When you have multiple files to deploy along with your exe, dll's to register, file associations to set up, then an installer is a neat way to deliver all of that in a reliable manner. If you don't do this with an installer the user could easily screw things up.
In addition to that, the installer is sometimes used as a means to ensure the computer is truly ready for the application. For example, the installers I've written check to ensure the proper version of .NET is installed, and will download & install it if necessary.
However, there are many times when these characteristics are simply not worth it and deploying a standalone application in a single exe is perfectly acceptable. Simple applications that don't need to store a lot of settings on your computer and don't have a lot of prerequisites are perfect examples. The first thing that comes to mind are all the utilities from Sysinternals.