Finding Formula Errors in Excel 2010

Synopsis: In this article we look at how to locate formula errors in an Excel 2010 workbook. We also show you how to setup Excel to warn you about errors in formulas.

Excel 2010 may detect an error in one of the formulas you are using in a workbook. When it does, it usually indicates the error by placing a green triangle in the top left corner of the cell containing the formula. In some cases Excel will also display a message box, warning you that there is an error in one or more of your formulas.

This is what the green triangle looks like.

If you select the cell with the green triangle you will see the associated smart tag and this contains a description of the error Excel has detected.

In this case the smart tag tells you that this is an inconsistent formula, which means that Excel has compared it with neighboringformulas and has noticed that it is different from them in some way.

If you click Help on this error you will see the online Help from Office.com.

Scroll through this and you will find the help that particularly relates to the current problem.

In this case, if you look at the other cells in the same row you will see that they all contain formulas that add the contents of a range of cells together. For example, in cell K16 the formula sums the values in the cells from K8 to K15.

Other cells – such as L16 and I 16 follow the same pattern. However, if you look at the formula in J16 – the cell with the green triangle – you can see the difference.

The sum here is only from J8 to J14. Excel detects that this particular formula is inconsistent and gives you the warning about inconsistency.

It is possible, of course, that this formula is correct and that this column does need a different formula. For that reason, Excel provides the option Ignore Error on the smart tag. Select that option and Excel will remove the warning.

If, however, there really is an error, you can select Edit in Formula Bar and correct the formula. Note also that there is an option Copy Formula from Left, in which case Excel will do just that, adjusting it to the correct column, of course.

There are several types of formula error that Excel can check for, and you can control which ones it uses for you from the Excel Options dialog box. Access the dialog from Backstage View then select Formulas. Alternatively, select Error Checking Options… on the smart tag.

The lower section lists the error checking rules that Excel can apply. You can see that Formulas inconsistent with other formulas in the region is checked. You can choose which of the rules to apply and which to ignore by checking and unchecking the boxes.

Note also in the section Error Checking that there is a drop down box where you can select or change the color to use to indicate errors. It defaults to green, but if you are using a workbook with a lot of green in it or you have trouble spotting small green triangles you can change the color using that drop down.

In the same section there is a button you can use to reset the list of errors you have told Excel to ignore.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!