Tips on debugging formula

Have you got any tips to debug a formula that is displaying an error?

By Neale Blackwood

Excel has an Auditing toolbar that allows you to trace cell links. This toolbar has a Trace Error button. Excel XP and 2003 have built-in Auditing cell notes that examine and report on your spreadsheet formulae for typical errors. When typing formulae, always use lowercase. That way it is easy to see if you have made a spelling mistake, as Excel will automatically convert words it recognizes into uppercase once the formula is entered.

If you use range names capitalise at least one letter of the name. When typed in lowercase, the name will be changed to the capitalised spelling.

There are rare instances when Excel will not calculate fully. If this is the case, pressing Ctrl+Alt+F9 together will force a full calculation.
If you have a complicated formula with many parts or functions, you can calculate part of the formula by selecting that part in the Formula Bar (above the column letters and below the toolbars) and pressing F9. His will replace that part of the formula with its results or an error message in the Formula Bar. You can use this technique multiple times on a large formula.
Warning: You need to press the Esc key to reset the formula and remove the values. If you forget to press the Esc key you can use the Undo feature to correct the formula. This method is useful for a large formula where you are trying to track down what is causing an error.

Extras

In the Formula Auditing toolbar there is also an Evaluate Formula option which allow you to step through the formula and find errors. You can also use a Watch Window (also on the Auditing toolbar) which displays the values of cells.
In Excel 2007 there is a Formula Auditing section in the Formulas Ribbon tab.