Excel 2010: Using Formula Auditing Tools
Have you ever inherited an Excel workbook that is so complex it seems just a maze of text and numbers? If so, the tools in the Formula Auditing group of the Formulas tab on the Excel Ribbon might be the getting-acquainted “social” you need to make that workbook your new best friend.
One first approach might be to use the Show Formulas tool. It will tell which cells contain values that were entered and which are the result of some calculation. Below, on the left, is a sheet in the default view; and, on the right, is the same sheet after clicking the Show Formulas button:
Formulas displayed in cells
When a cell result displays the dreaded and not very helpful #VALUE! Entry, or if the formula result just isn’t logical; you could use the Error Checking Tool. In the example below the formula is set to subtract a text entry from a numeric entry which Excel doesn’t want to do. With the cell selected, click the Error Checking button to open the Error Checking window.
Error Checking window explanation
If the simple statement about the error isn’t helpful enough, you can use other tools in this window to dig deeper into the problem:
The Error Checking window
Let’s use the Show Calculation Steps… tool to analyze each element of the formula. Clicking that button opens the Evaluate Formula window.
Notice that E1, the first element of the formula is underlined. Clicking the Evaluate button will change that reference to its value.
Evaluating the parts of a formula
Trace Precedents and Trace Dependents can also give visual clues about dependencies between cells. If you select any cell that contains a formula, having identified those with the Show Formulas tool, you can click the Trace Precedents button to more graphically see the values contributing to the result. Similarly you can select a non-formula cell and click the Trace Dependents button to see all the cells that particular reference impacts. The graphics below show these tools in action:
Once you have seen the connections, you can erase the arrows using the Remove Arrow button also in the Formula Auditing group of tools. If you have both Precedent and Dependent arrows, it lets you remove them by type.
