Hawking was famous, and important, mostly as a popularizer. I'm not a physicist so I'm not competent to make a confident evaluation of his contribution. However it seems to me that as a physicist he was one of the leaders who helped advance cosmological theory during his career, but many of his ideas remain speculative and he was not really transformative. As I understand it he never won a Nobel prize because none of this key ideas have been subject to theoretical confirmation.
Nevertheless being the incarnation, for the general public, of the cosmic mysteries, was an important cultural role. Hawking's deep journey into cosmological science left him a fully convinced atheist. It took him a bit of a journey to get there but in 2014 he said:
Before we understand science, it is natural to believe that God created the universe. But now science offers a more convincing explanation. What I meant by 'we would know the mind of God' is, we would know everything that God would know, if there were a God, which there isn't. I'm an atheist.The fact is, however, that relatively few people are atheists; and for most of those who are not, the scientific understanding of the cosmos is simply unacceptable. As Steven Weinberg famously wrote, "the more the universe seems comprehensible the more it seems pointless." As he goes on to say in the linked interview:
I think it's been the truth in the past that it was widely hoped that by studying nature we will find the sign of a grand plan, in which human beings play a particularly distinguished starring role. And that has not happened. I think that more and more the picture of nature, the outside world, has been one of an impersonal world governed by mathematical laws that are not particularly concerned with human beings, in which human beings appear as a chance phenomenon, not the goal toward which the universe is directed. And for some this has no effect on their religion. Their religion never looked for any kind of point in nature. For others this is appalling, the idea that all of the stars and galaxies and atoms are going about their business, and it's just by accident that here on this solar system the peculiar chemical properties of DNA acting over billions of years have produced these people who have been able to talk and look around and enjoy life. For some people that picture is antithetical to the view of nature and the world that their religion had given them.
It is not only antithetical to religion, it is also, well, counterintuitive and bizarre. Once Edwin Hubble used a powerful telescope to discover the universe, and discover that it was expanding, people tried conceptually running the tape backward and they got the so-called Big Bang. (There was no bang, of course. I want to call it the Initial Singularity, the IS, but the name is too entrenched now.) By mixing Einstein's theory of gravitation with quantum field theory, and making more and more sophisticated observations of the cosmos using ever more powerful and sophisticated instruments, cosmologists were able to deduce the history of the universe that Hawking discusses (as of its stage of development in 1988) in A Brief History of Time. Then, the accelerating expansion, dark matter, and dark energy had not been discovered, so in a sense we know less than we thought we did.
But all of this means nothing to the average person. It just seems crazy. The universe is 13.8 billion years old (approximately)? It started as an infinitely hot, infinitely dense point and suddenly started expanding and cooling? The earth is 4 1/2 billion years old, and it condensed along with the sun from the remnants of a previous generation of exploded stars? There are 100 billion stars in our galaxy (actually probably a lot more than that) and 100 billion galaxies (ditto). Prove it to me!
Well, you know, unless you want to take a degree in cosmology you're just going to have to trust us. It seems that many people just don't.