Find problem cells in Excel

The Formula Auditing Toolbar is an excellent way of tracking down problems in Excel spreadsheets, especially when formulas refer to several cells, and it's easier to use than the name suggests.

We have here a spreadsheet with a couple of divide-by-zero errors. We could try and track them down by looking at the cell references, but what we really need is a more visual approach.

Click on the View menu, toolbars and then Formula Auditing. You can either leave it floating or move it into the Toolbar area.

The second icon from the left draws lines from the current cell to the cells that are being used by the formula. Click several times if there are several layers of formulas. The icon to the right removes the lines one step at a time.

The icon that looks like a pencil rubber will remove all the lines; it's essential when there are too many lines onscreen.

The forth icon from the left works in the other direction, showing the cells that are taking the information from the current cell. We now know that the 0 in cell B5 is causing problems so to find out where it is being used, click on it and then on the Trace Dependants button. Repeat to see the full list.