I guess I'll separate this into my preferences and things I think are actually necessary. I will preface this by saying I'm thinking along the lines of MMO villains, particularly in roleplay, as you can subvert certain things when you're writing a story for yourself only. For actual requirements:
A villain whose motivation is badly explained, not present or unbelievable (perhaps mixtures of these poor traits) makes for a bit of a lacklustre opponent. Even those whose "motivation" is clinical insanity needs to be played convincingly and not just as an idiot. Severe murderous tendencies are a real condition, they will have in-depth behavioural patterns as well, with the exception of those who are so sideways they're completely random.
- Having a good backstory that backs up their current behaviour and reads well is a good way to ensure this point is met. Random ego trips and arbitrary additions can be evaded here as well.
A villain must be a credible threat, otherwise they may as well be a mook for a real villain. This does not have to be pure power, and can just as easily be the flexing of mental muscles instead of real ones. Villains that fail to meet this criteria end up seeming more like goofy comedy relief as they'll be consistently stomped. Would say this sort of individual makes for a good "pre-villain" however, i.e. someone so soundly defeated they train hard enough or make drastic enough changes to become a foe.
A villain who cannot be defeated is hilariously badly designed. Borefest for the heroes, and a borefest for the villain as well (unless there's some serious ego, I guess). This isn't to say they always have to lose but the villain who retains his credibility as a threat even in defeat is, in some ways, far more interesting than one who is victorious consistently. The essence of this is that there must be a weakness, a chink in the armour or an exploitable mentality.
- Imagine yourself as the protagonist and fight yourself. If you cannot do anything, then imagine what it's like to be the player behind it. Wailing on something with no perceptible weakness is so unbelievably boring. If, over the course of a storyline, the weakness is discovered rather than immediately found, that's okay. As long as it's reasonably clear that they're not invincible.
- Admitting that random ideas you never thought of would probably do the job is almost mandatory; for example, if something is resistant to ALL magic and pretty physically tough (sounds dull to me, incidentally, but it's an example), if they do the smart thing and go for the weaker spots - nose, eyes, underside etc. it is very rewarding for protagonists to have a smart move pay off for them.
As for things I, personally, like in my villains:
A villain whose motivations are plausible to me, especially given detailed backstory, inspires a certain element of sympathy and in many ways a determination that is more grim instead of 'troof and jusstiss'. Someone who even the hero might mourn has so much more impact on me than a d*head, or even just kill without regret but at least understand why they acted the way they did. Examples: Ardun Kothe (SWTOR), Arthas Menethil (Warcraft)
P.S. Villains who can make the heroes question whether they're on the right side are the best ever. Shades of grey in the morality spectrum are far deeper than black and white.
I think you can have plausible villains who are just straight up good in a fight to be threatening but I've always preferred masterminds to that ilk. Physically inferior villains whose threat is all implicit in their demeanour or in the webs, plots and lies they weave. Honest cowards who flee instead of fight a battle they cannot win. Examples: Arcturus Mengsk (Starcraft), Dr. Breen (Halflife 2)
I have a preference for villains that are trying to do the right thing in a way that everyone else disagrees with, so they end up in conflict, or end up helping them as well as fighting them dependent on the situation. This ties into 'relatable' quite heavily, as the 'friend in another life' syndrome is very compelling. Examples: Illidan Stormrage (Warcraft), Illusive Man (Mass Effect 2)
A villain who is on equal footing with his enemies rather than a lofty one, and the matter is decided on wits or even rotten luck can be just as intense as an epic fight. This is often a thing of fighting villainous factions rather than individuals, as it's rarely 1v1 in this context. This also applies to protagonists - heroes who are too lofty are boring as hell as well. Rebels vs The Empire (classic Star Wars) is a pretty good example of this.
If a villain must be immensely powerful, let it be their downfall, as it is a far more pleasing and compelling end for the heroes if they die as a result of their own hubris and overconfidence. Examples: Archimonde, The Lich King (Warcraft).
Guess that'll do for now. Could talk about this stuff for hours just because writing's my premiere hobby.
† Miormi - Bronze Dragon - Time Keeper
Xaeridormi - Bronze Drake - Time Keeper Aspirant
Nelisera - Emerald Wyrm - Estranged
No longer active.