My take is a pretty simple one:
I don't think Sephiroth himself is a great villain, but the atmosphere he (And his actions) creates are what make a great villain. Remember in Nibelheim how he was reading through all of the books of the Shinra mansion as if possessed? That was in the flashback, but it wasn't the "insanity" itself that sells him as a villain (imo) it is the -atmosphere-. Remember the backing music of his insanity?
Now later in the game, you return to Nibelheim (5 years later) and in the same mansion it appears unchanged to what you see in the flashback, HOWEVER, the atmosphere dictated by the music and the apparent abandonment of the mansion just build on it and fill the place with Sephiroth's presence. The first time I returned there? I genuinely felt tension, expecting to meet Sephiroth and have to battle him at any moment.
Later in the game, it isn't Sephiroth's presence, but the presence of his act (Meteor) and the weight it bears on all of the people bearing witness to it and how various attempts continue to fail to prevent the act. Also, remember the post-meteor summoning music? That sets the theme behind the world again and makes it an oppressive presence (And you can't even SEE meteor outside of cutscenes/story dumps). If you've played LoZ: Majora's Mask, it's a similar concept there, there is evidently a villain behind it, but it is the act itself that has the presence and dictates the atmosphere.
As far as villains go, Sephiroth isn't bad, but nor is HE great. It is the acts and the -presence- he has that makes him memorable.