By the standards of many more modern horror movies, I think it's easy to see The Exorcist as being overrated. It relies more on psychological stimulation and suspense rather than the standard horror movie does, particulary today. However, you have to take into consideration the subject matter that the film deals with and the day and age in which the movie came out.
The Exorcist isn't the first horror film to deal with aspects of supernaturalism along the lines of demons and whatnot but no film had approached it in this way before. In this movie, you have a young and innocent prepubescent girl possessed by a demon that mutilates her body at various points in the movie, speaks with a menacing tone, says things that were quite shocking to hear from a girl of Linda Blair's age in the early 70s, saying things like "Fuck me, Jesus", stabbing herself in the privates with a crucafix, etc. Some of these things in and of themselves were just plain shocking to audiences.
However, you also have to take into consideration the religious beliefs of those that watched the movie. In the Bible, there are mentions of demonic possessions and many people take the Bible as literal truth. Even if they don't, many still beliefe that demonic possession is possible and does happen. In the film, the demon identifies itself as "The Devil", even though later films, and the book itself I think, name the demon as Pazzuzu. The Devil or Satan or whatever is a being that literally hundreds upon hundreds of millions of people believe exists. The villain of The Exorcist wasn't some disfigured psychopath or a mentally ******ed cannibal or some unsoppable juggernauth wearing a hockey mask while wielding a machete. The villain was a primal force of nature that many believe exists, including those that have watched The Exorcist over the years. In the minds of some of them, I'm willing to bet they said "I know that this is only a movie, but maybe something like this can really happen. Maybe it can even happen to me."
The Exorcist wasn't a movie that had a lot of blood and gore in it. Blood and gore might not necessarily be scary, depending on who you are. You might be disgusted, but being disgusted and frightened don't go hand in hand. The Exorcist is one of these movies that takes a part of who people really are, or at least some aspects of what they believe in, and uses it to generate legitimate fear.
Is it a masterpiece? I'd have to say yes overall. This was a movie that truly frightened a lot of people that have seen it. It didn't need lots of violence, it didn't need blood and gore, it didn't need some tangible villain. It used some of the deepest held beliefs that many people have, took them and turned it onto a story that people are still talking about nearly 40 years later. It got inside people's heads and stayed with them.