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
These findings are shown graphically by arrow lines that makes the visualization 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.
You need to check whether the display options for the workbooks you are using are correctly set.
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,
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 valid references. Otherwise, you will get an error message.
You will get a message.
Click Remove Arrows in the Formula Auditing group.
All the arrows in the worksheet will disappear.
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,
An arrow appears from B2 to C2, showing C2 is dependent on B2.
To trace the dependents of the cell C4 −
Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.
Click 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.
You have understood the concept of Precedents and Dependents. Now, consider a worksheet with several formulae.
Click in a cell under Pass Category in Student Grades table.
Click Trace Dependents. All the cells under Pass Category in Exam Results table will be mapped as the dependents.
The worksheet below contains the summary of sales by the salespersons in the regions East, North, South, and West.
Click the FORMULAS tab on the Ribbon.
Click 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.
Click in a cell under TotalSales.
Click 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-click on the arrow. A Go TO dialog box appears, showing the precedents.
As you observe, there are four precedents, on four different worksheets.
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
In the Evaluate Formula dialog box, the formula is displayed in the box under Evaluation. By clicking the Evaluate button several times, the formula gets evaluated step-wise. The expression with an underline will always be executed next.
Here, C2 is underlined in the formula. So, it is evaluated in the next step. Click Evaluate.
Cell C2 has value 0.2. Hence, C2 will be evaluated as 0.2. 1+0.2 is underlined showing it as the next step. Click Evaluate.
1+0.2 will be evaluated as 1.2. SQRT(1.2) is underlined showing it as next step. Click Evaluate.
SQRT(1.2) will be evaluated as 1.09544511501033. C10 is underlined showing it as next step. Click Evaluate.
C10 will be evaluated as 4976.8518518515.
1.09544511501033*4976.8518518515 is underlined showing it as next step. Click 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.
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!.
Click in the cell C5.
Click the FORMULAS tab on the Ribbon.
Click the arrow next to Error Checking in the Formula Auditing group. In the drop-down list, you will find that Circular References is deactivated, indicating that your worksheet has no circular references.
Select Trace Error from the drop-down list.
The cells needed to compute the active cell are indicated by blue arrows.
The Error Checking dialog box appears.
Observe the following −
If you click Help on this error, Excel help on the error will be displayed.
If you click Show Calculation Steps, Evaluate Formula dialog box appears.
If you click Ignore Error, the Error Checking dialog box closes and if you click Error Checking command again, it ignores this error.
If you click 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.