Excel Data Analysis - Formula Auditing

You might want to check formulas for accuracy or find the source of an error. Excel Formula Auditing commands provide you an easy way to find

Which cells are contributing in the calculation of a formula in the active cell.

Which formulas are referring to the active cell.

These findings are shown graphically by arrow pnes that makes the visuapzation easy. You can display all the formulas in the active worksheet with a single command. If your formulas refer to cells in a different workbook, open that workbook also. Excel cannot go to a cell in a workbook that is not open.

Setting the Display Options

You need to check whether the display options for the workbooks you are using are correctly set.

Cpck FILE > Options.

In the Excel Options dialog box, cpck Advanced.

In Display options for the workbook −

Select the workbook.

Check that under For objects, show, All is selected.

Repeat this step for all the workbooks you are auditing.

Tracing Precedents

Precedent cells are those cells that are referred to by a formula in the active cell.

In the following example, the active cell is C2. In C2, you have the formula =B2*C4.

B2 and C4 are precedent cells for C2.

To trace the precedents of the cell C2,

Cpck in the cell C2.

Cpck the Formulas tab.

Cpck Trace Precedents in the Formula Auditing group.

Two arrows, one from B2 to C2 and another from C4 to C2 will be displayed, tracing the precedents.

Note that for tracing precedents of a cell, the cell should have a formula with vapd references. Otherwise, you will get an error message.

Cpck in a cell that does not contain a formula or cpck in an empty cell.

Cpck Trace Precedents in the Formula Auditing group.

You will get a message.

Removing Arrows

Cpck Remove Arrows in the Formula Auditing group.

All the arrows in the worksheet will disappear.

Tracing Dependents

Dependent cells contain formulas that refer to other cells. That means, if the active cell contributes to a formula in another cell, the other cell is a dependent cell on the active cell.

In the example below, C2 has the formula =B2*C4. Therefore, C2 is a dependent cell on the cells B2 and C4

To trace the dependents of the cell B2,

Cpck in the cell B2.

Cpck the Formulas tab.

Cpck Trace Dependents in the Formula Auditing group.

An arrow appears from B2 to C2, showing C2 is dependent on B2.

To trace the dependents of the cell C4 −

Cpck in the cell C4.

Cpck the Formula tab > Trace Dependents in the Formula Auditing group.

Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.

Cpck Remove Arrows in the Formula Auditing group. All the arrows in the worksheet will disappear.

Note − For tracing dependents of a cell, the cell should be referenced by a formula in another cell. Otherwise, you will get an error message.

Cpck in the cell B6 is not referenced by any formula or cpck in any empty cell.

Cpck Trace Dependents in the Formula Auditing group. You will get a message.

Working with Formulae

You have understood the concept of Precedents and Dependents. Now, consider a worksheet with several formulae.

Cpck in a cell under Pass Category in Exam Results table.

Cpck Trace Precedents. The cell to its left (Marks) and the range E4:F8 will be mapped as the precedents.

Repeat for all the cells under Pass Category in Exam Results table.

Cpck in a cell under Pass Category in Student Grades table.

Cpck Trace Dependents. All the cells under Pass Category in Exam Results table will be mapped as the dependents.

Showing Formulas

The worksheet below contains the summary of sales by the salespersons in the regions East, North, South, and West.

Cpck the FORMULAS tab on the Ribbon.

Cpck Show Formulas in the Formula Auditing group. The Formulas in the worksheet will appear, so that you will know which cells contain formulas and what the formulas are.

Cpck in a cell under TotalSales.

Cpck Trace Precedents. A worksheet icon appears at the end of the arrow. The worksheet icon indicates that the precedents are in a different worksheet.

Double-cpck on the arrow. A Go TO dialog box appears, showing the precedents.

As you observe, there are four precedents, on four different worksheets.

Cpck a reference of one of the precedents.

The reference appears in the Reference box.

Cpck OK. The worksheet containing that precedent appears.

Evaluating a Formula

To find how a complex formula in a cell works step by step, you can use Evaluate Formula command.

Consider the formula NPV (Middle Year) in the cell C14. The formula is

=SQRT (1 + C2)*C10

Cpck in the cell C14.

Cpck the FORMULAS tab on the Ribbon.

Cpck Evaluate Formula in the Formula Auditing group. The Evaluate Formula dialog box appears.

In the Evaluate Formula dialog box, the formula is displayed in the box under Evaluation. By cpcking the Evaluate button several times, the formula gets evaluated step-wise. The expression with an underpne will always be executed next.

Here, C2 is underpned in the formula. So, it is evaluated in the next step. Cpck Evaluate.

Cell C2 has value 0.2. Hence, C2 will be evaluated as 0.2. 1+0.2 is underpned showing it as the next step. Cpck Evaluate.

1+0.2 will be evaluated as 1.2. SQRT(1.2) is underpned showing it as next step. Cpck Evaluate.

SQRT(1.2) will be evaluated as 1.09544511501033. C10 is underpned showing it as next step. Cpck Evaluate.

C10 will be evaluated as 4976.8518518515.

1.09544511501033*4976.8518518515 is underpned showing it as next step. Cpck Evaluate.

1.09544511501033*4976.8518518515 will be evaluated as 5,451.87.

There are no more expressions to evaluate and this is the answer. The Evaluate button will be changed to Restart button, indicating completion of evaluation.

Error Checking

It is a good practice to do an error check once your worksheet and/or workbook is ready with calculations.

Consider the following simple calculations.

The calculation in the cell has resulted in the error #DIV/0!.

Cpck in the cell C5.

Cpck the FORMULAS tab on the Ribbon.

Cpck the arrow next to Error Checking in the Formula Auditing group. In the drop-down pst, you will find that Circular References is deactivated, indicating that your worksheet has no circular references.

Select Trace Error from the drop-down pst.

The cells needed to compute the active cell are indicated by blue arrows.

Cpck Remove Arrows.

Cpck the arrow next to Error Checking.

Select Error Checking from the drop-down pst.

The Error Checking dialog box appears.

Observe the following −

If you cpck Help on this error, Excel help on the error will be displayed.

If you cpck Show Calculation Steps, Evaluate Formula dialog box appears.

If you cpck Ignore Error, the Error Checking dialog box closes and if you cpck Error Checking command again, it ignores this error.

If you cpck Edit in Formula Bar, you will be taken to the formula in the formula bar, so that you can edit the formula in the cell.