Excel is an incredibly powerful tool for working with long lists of numbers and even complex datasets. The bigger and more complex the data gets, however, the more likely it is that errors will creep into the formulas that you are using. Sometimes, diagnosing those errors can be tricky. The cell which throws the error message could be several steps removed from where the error is. Indeed, in some cases there may not be an error in a specific cell. The spreadsheet could simply be giving you incorrect results with no sign that there is a problem at all.
How to Detect, and Prevent, Common Excel Errors
As always, preventing errors with sound development practices is the best idea. When you are building your worksheet, save regularly. Keep a few old versions so that you can revert if you run into problems.
Pay attention to what Excel is telling you, too. One thing that a lot of people don’t realize is that error messages for serious syntax errors are not the only kind of warning or notification that Excel gives. While it’s true that error messages should not be ignored, Excel also offers some “polite warnings” about other potential problems and you should pay attention to those as well.
Look for the Green Corner
Excel will flag up syntactically correct, but potentially problematic, formulas with a green triangle in the top left corner of the cell. For example, it may warn you that you have an “inconsistent formula” if the formula in one cell is looking at a different range to the formula in other cells.
To find out what Excel doesn’t like, use the “Trace Error” option from right-clicking on the cell. This will tell you what it thinks is wrong. It could well be that you intended to look only at the specific cell range that you included in the formula. Excel doesn’t know that, though, and simply tries to guess what you were doing based on the rest of the layout of the sheet.
Trace for Diagnostics
The Trace Error option is very powerful, and can help to diagnose all manner of problems. If your sheet has #VALUE! or #N/A! errors in it, you can hide them using IFERROR, but you should only do this if you expect certain results to throw an error and you know for sure that this will not have an impact on the rest of the sheet. The vast majority of the time, an error is a problem, and you should fix it immediately.
Use Trace to see where the error originated. You can step through all the formulas in the sheet, so if one cell acts based on the value of another cell, which contains a formula, and that cell’s input is other cells with a formula as well, then you can step through that for as many layers of formula as it takes until you find the cell with the problem. That issue could be anything from text in a numbered cell, to an incorrect date format. It’s easier to find when you can follow the path of data to the final output.