A lot of folks here seem overly focused on being the star of combat. Wizards are a utility class, with a side order of mook destruction. If that's not your thing, you won't enjoy playing a wizard, any more than a player who finds combat boring would enjoy playing a fighter, or a player who hates being in a support role would enjoy playing a cleric.
It's not about being the star of combat. Nobody ever said that. It's about being at least a little bit competent in combat more than one encounter in three.
First of all, don't make the mistake of thinking of cantrips as fallbacks for when you run out of "real" spells. At low- to mid-levels, cantrips are your "real" spells; leveled spells are your big guns.
When you pull out the big guns, they should actually do something above and beyond what other PCs are doing with melee attacks.
Magic Missile. Yes, I hit automatically. But the Monk with his Flurry of Blows averages more damage. His ability refreshes with a short rest, my ability refreshes with a long rest (and yes, I know that Flurry is not a ranged attack).
Yes, but that is a bit of an issue. Wizards do not get all of the spells like Clerics, and do not get decent At Will attacks like anyone else.
For my wizard PC, I took 1st level spells: Detect Magic, Disguise Self, Find Familiar, Fog Cloud, Identify, Mage Armor, Shield, and Witch Bolt (didn't read it carefully) and 2nd level spells: Suggestion and Web by third level.
With 6 spells at level 3, that's typically Mage Armor and Shield as autospells, one or both of the second level spells, and if I throw Detect Magic in the list, then there are only 1 or 2 spells per day remaining. Making one of them offensive (at this point, I have more offensive spell options, these are just the ones I selected) still squeezes the PC.
Do you try to protect with Fog Cloud? Take a second offensive spell? Forget about Identify, that has to wait. Disguise Self is great, but I cannot take it most days.
The point is, the PC cannot do much in the way of utility since he is forced to do at least a little offense and defense and not taking the highest level spells seems like a bit of a waste.
Other excellent leveled spells: Levitate, shield, invisibility, suggestion,, counterspell. I routinely make my DM cry by counterspelling his spell-slinging villains. If you're an abjurer (which gives you proficiency on your counterspell checks), you have no excuse to let an enemy caster ever get a big spell off.
Abjurers only get this ability starting at level 10. So, level 5 through 9, the PC is using up one of his few higher level slots to maybe, and maybe not, counter a 4th or higher level spell.
Btw, Shield is stupid. It's a spell more for Bards, Arcane Tricksters, and Eldritch Knights than for Wizards. Resource-wise, it's extremely expensive for a Wizard. And even with that in mind, most PC Wizards have to use up a spells known per day to take it because without it, being a wizard sucks even more. It's like Mage Armor. A more or less have to take spell designed to eat up spells per day and spell slot resources.
It's all great and well to talk about the wonderful things that wizards can do at higher levels, but the wizard actually has to survive to get to those levels (in most campaigns), and the utility you talk about can be few and far between because the wizard doesn't actually have the spell he needs today prepared.