My post probably won't fit so well here as I have many more freshwater dives than I do saltwater...and I haven't been to any exotic locations as many have posted.
My favorite dive location is Presque Isle, MI in Lake Huron. I love diving on the wrecks there. It is my sensory overload...I love the historical element, the viz is great and as most of the wrecks are deep, they are well preserved.
My next high sensory dive is Ginnie Springs going in Devil's Eye or Ear, thru the lips and into the main area of the system. Working against a fairly high flow, when you hit your turn point, I felt like I was flying thru the Grand Canyon with a jet pack. (And I have backpacked in the GC for a couple of week long trips.)
Next on my list is Hole-in-the-Wall Cave in Marianna, FL. The ceilings are so fossilized it's like someone 'wallpapered' them. And between the fossils and geothite 'chandeliers' it's hard to get very far into the system!
I have dove out of Morehead City on the U352 which was a reflective dive for me. I also enjoyed all the fish life there, swimming in compartments with 3 sand tigers (Aeolus), seeing large rays and having to ascend a bit to get out of the cloud of jacks making it hard to know where your reference was(Shurz). Various wrecks and a couple of artificial purpose reefs.
In the Keys, one of my favorite dives was was the Eagle and the 2 GIANT Jew fish . We also did a couple dives on the Spiegel Grove and the fun part of that was diving with my daughter. And, there was a neat bull shark off the stern.
We dive locally at Table Rock Lake and this is where we learned to dive and have several favorite spots. We use to be able to dive deep at the dam and swim up to the dam wall ;-) They have restricted this now due to some new system construction around the visitor's center. We have also had a lot of fun in Norfork Lake. These are considered rock bottom lakes and the visibility can be very good. I have a few dives in Beaver and Bull Shoals where I did my only spear fishing. Also, Greer's Ferry where I was meeting up to learn some of the divers I'd be going to Morehead City with and one very neat dive was on an old wood structure pump house from when the river wasn't dammed up.
About three trips to Cozumel. The first one was great, the second was pretty fun, by the third time I realized there were other places to dive.
Mackinaw City was great fun and there is a ton of topside stuff to do in that area. There are some great wrecks there, but I prefer Presque Isle for the wreck quality, but Mack has several that aren't too deep.
I have dove out of Milwaukee and Chicago'ish areas. Prefer the Milwaukee area, but Chicago is closer to me.
And, lots of caves, in MO-Roubidoux where I have scootered to the 2nd drop off, Cannon Ball which has a unique no viz entrance from Lake Wappapello, but then opens in to clear spring water as you enter the system and Mine Lamotte which has a maze of old lead mine tunnels radiating from the quarry area and the water is cold, the very high flow of Bennett Springs cavern and Blue Spring as a support diver with the OCDA team. FL - Ginnie, Peacock, Orange Grove, Little River, Madison Blue, Vortex, Jackson Blue, H n t W, Twin, and probably a couple I am forgetting. One favorite was the Grand Traverse entering Orange Grove and exiting Peacock going thru a couple of sink holes.
I have about 60 saltwater dives and 470 freshwater with 200 cave dives, about 100 of them in MO and 100 in FL.
I'd love to dive in the Truk area on the warships/planes and the New Jersey area. And, I need to get some west coast diving in. Eagles Nest is probably a cave I have on my list and I'd like to go back to Australia and do some salt water and cave diving. Oh...and Lake Superior
Anyway, I know this is different perspective than the 'normal' post in this thread, but thought I'd share.
Other than the runs to the 2nd Drop Off in Roubidoux, my least favorite diving has been scooter diving. I know most people love it. I bought a scooter, took the training with Edd Sorenson and made about 40 dives when I decided I wasn't enjoying it and sold the scooter and started Side Mounting (and I know they absolutely aren't mutually exclusive and I did a couple of SM scooter dives.) I may rent a scooter for a specific purpose and I am glad I have the training and gave it a try, but I don't think I'll buy one again.
Happy diving...