Your spreadsheet justifies a $10 million project, but you have a queasy feeling about the calculations. How can you check them?
Your spreadsheet justifies a $10 million project, but you have a queasy feeling about the calculations. How can you check them?
First make sure you follow good spreadsheet style. See our Style tips. Why does good spreadsheet style relate to auditing? Because a spreadsheet that is more readable is easier to check! But after you have tried to make it readable, you still need auditing tools.
Excel's Auditing Toolbar is a convenient way to see which cells are dependents or precedents of a given cell.
To see the Audit Toolbar, select Tools, Auditing, Show Auditing Toolbar. Unfortunately, the Auditing Toolbar does not appear on the View Toolbar menu.
To use the Auditing Toolbar, click to add Audit Arcs.
- Click the first button, "Trace Precedents," to see precedents - which cells are used by this cell? Excel inserts blue Audit Arcs, pointing from the precedents to the current cell.
- Click the third button, "Trace Dependents," to see dependents - which cells use this cell? Excel inserts blue Audit Arcs, pointing from the current cell to its dependents.
If the tail (not the pointy part) of an Audit Arc comes from a blank cell, your spreadsheet almost certainly has big problems. If the cell is in a column of cells with similar formulas, they are probably all wrong!
Look for Audit Arcs that point upwards or to the left. A good spreadsheet will follow the Write Like English Rule. It should read from left to right and top to bottom. So if you find an Audit Arc that points the wrong way, try to rearrange the spreadsheet so the Audit Arc points down and/or to the left.
Look for spurious cells. A spurious cell is of the form "B5=A26". It has exactly one precedent. While not wrong in itself, it can be an indication of sloppy work. You can simplify the spreadsheet by replacing all references to B5 with references to A26. Then delete B5!
Look for transposition-by-formula. Cells in a row are converted to cells in a column with spurious formulas. Eeeeww! Each formula has to be written one-at-a-time by hand, which is very likely to introduce errors. If you absolutely have to transpose a set of cells, use Excel's Transpose() function.
Look for cells with no dependents. You find a cell, you click Button 3, "Trace Dependents," and nothing happens except perhaps you hear "ding." Your cell has no dependents. Is it the bottom line, or did the writer put it there just for your information? If it is the bottom line, keep it. If it is there just for your information, delete it!
Having trouble because references are on another sheet? You can fix that! Put it all on one sheet. This way you can see everything at a glance, and the Audit Arcs actually show you something.
Inputs can be so controversial. Garbage in, garbage out. But at least they hold still and they are easy to change. Inputs don't really need to be debugged, so they take up a lot less attention.
Formulas are more troublesome. Most writers format formulas the same way as constant inputs, so the reader must move the cursor around to find the formula. Where are the formulas in your spreadsheet? Find all your formulas fast with the Glowing Formula Rule!
Panko wrote that we should not audit our own spreadsheet. We need someone else to look at it, because we each tend to be over-confident and in love with our own work.