If you're a science fiction person, then give the Mass Effect series a try. Mass Effect is probably one of the better storyline-based games that I've personally played. It is also a personal favorite. Go out and grab the first game, and give it a try.
Serious Sam series; the first two are older (2001, 2002), have no compatibility problems, can be set to current standard resolutions, and have HD remakes; the 2nd (2005) has a very different artstyle and direction. The 3rd (from 2011) one uses Steamworks. Arcade style FPS with tagline "No cover; all man", but there's one type of reoccurring enemy that kind of contradicts that statement since they have hit scan weapons. IIRC they all have local multiplayer; #3 supports splitscreen. One thing about Serious Sam games is large open maps, hordes of enemies shooting projectiles, and cheesy one-liners. There's print quality artwork in the Deluxe edition if you want to print your own box without the PCDVD banner and ESRB/PEGI/whatever icons.
The Void (2008); resource management, and it's better to avoid combat, but the surroundings and music are absolutely beautiful. I don't know if the Steam version uses Steamworks. Some have complained about the game's difficulty and having to reload saves, but community-made easy/medium patches exist. There's also an official torrent linked on it's website for print quality artwork.
Median XL; if you've played Diablo II: Lord of Destruction, this is a total conversion mod that rebalances drop rates and stat points, and pretty much adds enough to make a completely different game. Leveling up is very quick, and there's a ton of endgame content, and extra challenges. Run/Walk speed for both player and monster is much faster; the AI is also much more aggressive, comes with many abilities. Horadric Cube crafting is made much more intricate and useful. It's compatible with the high resolution patch for 1080+ for version 1.12; there's a couple settings in Glide-wrapper that can further add to the graphics. There's also a mod of this mod called Ultimative. -- Deus Ex (2000); I'm currently playing through it for the first time, so far I'm enjoying it. RPG/FPS game that can be played through without killing anyone. The AI appears to be very very dumb on medium combat difficulty. I haven't played touched Human Revolution yet.
Skyrim (duh) Fallout New Vegas (I would add Fallout 3, but it requires a Windows Live Silver or Gold login. While playable offline, you can't play any DLCs without going online.) Sims 3 (as mentioned, should just need a login once at even then, I don't think it cares if you never do.) Borderlands (technically) Need for Speed (some get mad, but are still playable. Skip anything by Criterion if you want a full offline experience though.) Burnout Paradise (same as the Need for Speed series, except this Criterion game is not as bad as the others.) And...got to go.
Do you like RTSs? I couldn't recommend Age of Empires 2 (and 3 for that matter) and Warcraft 3 enough. I don't know how important skirmishes with other players might be, but I find that playing RTSs offline by myself is more fun anyway.