Mods don't change the game nearly as much as some people would like to think, and usually it feels like cheating when you use them. Then again, this is coming from a guy who plays Fallout 3 on Xbox. :celebration:
They can change the game completely if you want them to. I played on console for 1,000 hours before switching to PC a year ago and my game looks nothing like my 360 games. My wasteland is filled with larger mobs of enemies, Geckos, Iguanas, ghouls who only come out at night, three-story Super Mutant Gargantuans, power armored Deathclaws, trogs, Punga Scorpions, fireflies, random travelers, shops, more people in the settlements, vertibird bombing runs, shacks, towns and vaults to discover, clubs to fight in, notes and books to find, quests, a hundred boarded-up buildings now open for exploration, foods to cook, chems to indulge in and use as weapons and nobody cares if I kick a can in their presence any longer.
Mods can change the game 100% if that is what you wish and everyone I know uses mods which make their game harder because vanilla was too easy.