I find that the automatic mode works well on monophonic solo instruments---trumpet, trombone, bass (usually monophonic, though not necessarily). If things aren't exactly in tune, it saves you from having to go back and re-record the entire track. Of course, that's basically doing a constant adjustment, which you can do with other, cheaper tools fairly easily, but it certainly saves time and effort.
Even the best vocalists can be off on a note once in a while. Ditto on what xfinsterx said: set it almost all the way to 'clumsy' and as slow a start as possible, and it sounds fairly natural, even allowing some vibrato while still bringing the occasional goof back into shape. After doing that, though, I always go back and listen to it. If anything is far enough off to create noticeable artifacting, it means that it is correcting significantly, and that spot needs to be re-recorded (or it means that you have a smear that shouldn't be auto-tuned).
Auto-tune can make a bad singer sound like a robot or a good singer sound great, depending on how it is used. I've always thought it would be fun to sing something a quarter step sharp and run auto-tune just to get an intentional robot sound on something. Maybe it's just me.