What is Auditing?

When you are preparing a worksheet you can make errors, i.e., a Data Entry Error: typing the wrong number, misspelling a word or name, or forgetting to type both parentheses in a pair.

Excel will let you know when you miss a parenthesis, but there is no software tool that can check to be sure you enter all your numbers correctly. But, if you set any validation automatically you can find out the error.

Excel’s built-in auditing feature helps to simplify that task. Excel’s auditing feature allows you to display tracer arrows to find precedents or cells that provide data to a formula, dependents, or cells containing formulas that refer to other cells, and errors in any cell.

To view the auditing options in any Excel version i.e 2007,2010,2013, click on the Formula tab of the Ribbon-> Formula Auditing

Tracing cell relationships

To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts:

Cell precedents: Applicable only to cells that contain a formula, a formula cell’s precedents are all the cells that contribute to the formula’s result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn’t used directly in the formula but is used by a cell that you refer to in the formula.

Cell dependents: These formula cells depend on a particular cell. A cell’s dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent.

For example, consider this simple formula entered into cell A4: =SUM(A1:A3)

Cell A4 has three precedent cells (A1,A2 & A3), which are all direct precedents. Cells A1, A2 & A3 each have a dependent cell (cell A4), and they’re all direct dependents.

Identifying cell precedents for a formula cell often sheds light on why the formula isn’t working correctly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For example, if you’re about to delete a formula, you may want to check to see if it has any dependents.

Identifying precedents

You can identify cells used by a formula in the active cell in a number of ways:

Press F2: The cells that are used directly by the formula are outlined in color, and the color corresponds to the cell reference in the formula. This technique is limited to identifying cells on the same sheet as the formula.

Display the Go To Special dialog box (choose Home-> Editing-> Find & Select-> Go To Special). Select the Precedents option and then select either Direct Only (for direct precedents only) or All Levels (for direct and indirect precedents). Click OK, and Excel selects the precedent cells for the formula. This technique is limited to identifying cells on the same sheet as the formula.

Press Ctrl+[ to select all direct precedent cells n the active sheet

Press Ctrl + Shift+ { to select all precedent cells (direct and indirect) on the active sheet.

Choose formulas-> Formula Auditing-> Trace Precedents, and Excel will draw arrows to indicate the cell’s precedents. Click this button multiple times to see additional levels of precedents. Choose Formula-> Formula Auditing-> Remove Arrows to hide the arrows. The figure above shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C11.

Identifying dependents

You can identify formula cells that use a particular cell in a number of ways:

Display the Go To Special dialog box: Select the Dependents option and then select either Direct Only (for direct dependents only) or All Levels (for direct and indirect dependents). Click OK. Excel selects the cells that depend on the active cell. This technique is limited to identifying cells on the active sheet only.

Press Ctrl+] to select all direct dependent cells on the active sheet.

Press Ctrl + Shift+} to select all dependent cells (direct and indirect) on the active sheet.

Choose Formulas-> Formula Auditing-> Trace Dependents and Excel will draw arrows to indicate the cell’s dependents. Click this button multiple times to see additional levels of dependents. Choose Formulas-> Formula Auditing-> Remove Arrows to hide the arrows.

Tracing Error values

If a formula displays an error value. Excel can help you identify the cell that is causing that error value. The error in one cell is often the result of an error in a precedent cell. Activate a cell that contains an error value and choose Formulas-> Formula Auditing-> Error Checking-> Trace Error. Excel draws arrows to indicate the error source.

Fixing circular reference errors

If you accidentally create a circular reference formula. Excel displays a warning message, Circular Reference (with the cell address) in the status bar, and draws arrows on the worksheet to help you identify the problem. If you can’t figure out the source of the problem, use Formulas-> Formula Auditing-> Error Checking->Circular References. This command displays a list of all cells that are involved in the circular references. Start by selecting the first cell listed and then work your way down the list until you figure out the problem.

Using background error-checking feature

Some people may find it helpful to take advantage of Excel’s automatic error-checking feature. This feature is enabled or disabled by using the check box labeled  Enable Background Error checking, on the formulas tab in the Excel Options dialog box shown in Figure above. In addition, you can specify which types of errors to check for by using the checkboxes in the Error Checking Rules section.

When error checking is turned on, Excel continually evaluates your worksheet, including its formulas. If a potential error is identified. Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a Smart Tag appears. Clicking this Smart Tag provides you with options. The figure shows the options that appear when you click the Smart Tag in a cell that contains a #DIV/0 error. The options vary, depending on the type of error.

In many cases, you will choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored error can be reset so that they appear again. (Use the Reset Ignored Errors button in the Formula tab of the Excel Options dialog box).

You can choose Formulas->Formula Auditing-> Error checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. The figure shows the Error Checking dialog box. Note that this dialog box is modeless so that you can still access your worksheet when the Error Checking dialog box is displayed.

Using Excel Formula Evaluator

Excel’s Formula Evaluator lets you see the various parts of a nested formula evaluated in the order that the formula is calculated. To use the Formula Evaluator, select the cell that contains the formula and choose Formula->Formula Auditing-> Evaluate Formula to display the Evaluate Formula dialog box (see figure).

Click the Evaluate button to show the result of calculating the expressions within the formula. Each click of the button performs another calculation. This feature may seem a bit complicated at first, but if you spend some time working with it, you’ll understand how it works and see its value.

Excel provides another way to evaluate a part of a formula:

1) Select the cell that contains the formula.

2) Press F2 to get into cell edit mode.

3) Use your mouse to highlight the portion of the formula you want to evaluate. Or, press Shift and use the arrow Esc to cancel and return your formula to its previous state.

4) Press F9.

The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press ESC to cancel and return your formula to its previous state.