You have to make a spell by spell comparison every time you level up
And comparing spells is more art than science.
Multi-target damage
Let's assume you have 2 enemies, 10 HP each. You do 6 damage to both of them, they still can hit you on their rounds. If you do 10 damage to one of them, you will be hit only once. For optimization, 2 x 6 < 10. There is no easy way to measure efficiency for multi-target attacks, but on other boards the following calculation is accepted:
- 100% for the first target
- 50% for the second (1/2)
- 33% for the third (1/3)
- and so on
So a spell that does 12 damage for 4 target is roughly equivalent to a spell that does 12 + 6 + 4 + 3 = 25 to a single target, not 48. And enemies usually do not do you the favor of piling up like this, definitely not 11 of them like wax eagle suggests in his answer.
Damage of the slot
Fireball does 8d6 damage for a 3rd level slot, Burning Hand does 5d6. It also has worse reach and smaller area. Fireball wins hands down.
Cone of Cold does 8d8 (average 36), Fireball 10d6 (35) for a 5th level slot. Fireball only does slightly less damage, and still has a better shape. Unless I know I am up against fire resistent creatures, I would still take that one. However for a 9th level slot, Cone of Cold does 12d8 (54) while Fireball does 14d6 (49) damage.
If two spells have similar shape and damage in the same slot, pick the one with the lower base level. If you can take them out with a Fireball in the 3rd level slot, why waste a 5th level slot, you have fewer of those.
Spell progression is not parallel, if one spell was better in a 2nd level slot, it might not be better at a 5th. Take Burning Hands vs Thunderwave; 1st level slot does 10.5 vs 9 damage, 5th level slot does 24.5 vs 27.
For this reason comparing spells only in 9th level slots is useful if you actually have those, and no other slots. (meaning never)
Damage types
A Fireball is useless even in a 9th level slot against someone who is immune to fire.
- Poison is the least useful (verging on useless), most undead, devils and demons are immune, and many other creatures have resistance.
- Necrotic is much better, but still a clear second. Mostly undeads are immune or resistant.
- Fire and Cold are roughly equivalent, more creatures resist fire, but also more are vulnerable to it. Most incorporeal undead are immune to cold.
- Lightning, Radiant, Acid, Psychic, Thunder and Force are very rarely resisted.
- Bludgeoning, Piercing and Slashing damage from spells are only resisted by swarms.
Save or Attack
Both have their pros and cons, but if every other parameter is equal, an Attack roll is far superior in most cases. Many monsters have Magic Resistance and even Legendary Saves, but only the Tarrasque is protected against (ranged) attack rolls. Also it is much easier to gain Advantage on attacks than to force disadvantage on a save.
Attacks
You have Disadvantage on ranged attack rolls, if you are adjacent to an enemy.
Most spells with attack rolls target single creatures, or you have to split the damage, like Scorching Ray.
Saves
The type of save matters a lot, a Stone Giant has a better chance to succeed on a Con save even with disadvantage, than on an simple Cha save against DC 15. So prepare diverse spells, quite often the description provided by the DM tells you what they are most sensitive to:
- Strength saves against caster and skirmisher types
- Dexterity saves against big burly warriors and casters
- Constitution saves against caster and skirmisher types
- Intelligence saves against big burly warriors
- Wisdom saves against big burly warriors and skirmishers
- Charisma saves against the ugly ones
Save for half
As wax eagle points out a spell that allowes a save for half is only worth about 75% of a spell that does not.
Save for nothing
These spells can be considered 25% weaker than spells that do half damage even on a save.
Other effects
Some spells have secondary effects besides direct damage, like the push of Thunderwave. It is hard to put a number on them, but still you have to consider how useful it is for you.
Conclusion
Every time you level up and gain a higher slot, you have to calculate the damage of the spells that just became available for you and compare it to your current spells. If the difference is big, take the new one as one of your free spells.
You have to take into account the campaign setting as well. If the DM only throws kobolds at you, even on 10th level, Fireball is enough. Cone of Cold will not make them any more dead, but it will eat up your 5th level slot.
You have to make this mental arithmetic every time you prepare spells as well.