Want to know my top 3 calculation tips when your calculations go wrong? When creating calculations in Excel there are some built-in features that are helpful to know about and understand to guide you along the way.
In some cases Excel will automatically correct your calculation. For example, if you have a missing bracket at the end of the calculation it will add it for you when you press ENTER.
Almost Automatic correction
This prompts you to check that the change being made is correct before it applies it. For example, if you are looking for text in a formula and you put in the starting quote but forget the ending one. Be aware the correction suggested will not always be what you need but it will in most cases. It is worth checking what change it is suggesting though.
Suggestion of where the calculation is going wrong
The initial help screen for this is gobbledegook.
If OK is pressed the formula will highlight the part of the calculation where it thinks the error is occurring. For example, if you have missed a comma in the middle of a function it will often recognise that and highlight information on both sides of where the comma should be. If the whole calculation is highlighted the error is too ambiguous and you’re on your own! Again, the suggestion (if there is one) is not 100% accurate but is certainly a good place to start.
If these 3 options are not helpful then I would slowly read through the calculation from the start. If it’s the end of the day look at it with fresh eyes in the morning. It’s amazing how often I have noticed errors that I couldn’t see the night before! Another option is talking it through with a colleague. Often explaining it to someone else provide the clarity to discover the issue. Only as a last resort would I consider rewriting the formula from scratch.
I hope these top 3 calculation tips make and Excel calculation whizz out of you!