Is the difference in visuals between software and hardware acceleration really noticeable? Does the game flat out look bad in software mode?

Which game are you talking about? Makes a difference. Keeper 1 is DOS only. The 3D supporting Windows version does look better but this GoG release won't play on OSs after XP. The D3D version of it is quite attractive and worth seeing in action.

Dungeon Keeper 2: if you have a new system from the past few years then you will probably have on board graphics (especially if you have a laptop), and switching to software mode will use this instead of your GFX card. It probably won't look noticeably different (DK2 was an early 3D game and I personally always thought it looked quite ugly anyway) but you won't be able to enhance the graphics by altering settings in the control panel.