Audit Excel Formulas

Formula auditing in Excel allows you to display the relationship between formulas and cells. In just a few minutes time, you will learn how to trace precedents, trace dependents, show formulas instead of their results, check for common errors and how to evaluate a formula.

Formula Auditing Example

The following easy example helps you master Formula Auditing in Excel quickly and easily.

Assume you had a nice day out with your friends. Someone paid for everything during the day and has sent you a spreadsheet with the costs.

Remember, this is just an easy example. For more complicated spreadsheets the following features will be even more powerful!

Trace Precedents

You have to pay $96.00. To show arrows that indicate which cells are used in the 'Cost per person' formula, execute the following steps.

1. Select cell C13.

2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents.

Result:

As expected, the 'Total cost' and 'Group size' values are used to calculate the cost per person.

3. Click Trace Precedents again.

The second arrow tells us which cells are used in the 'Total cost' formula - that is, the different costs.

Remove Arrows

To remove the arrows, execute the following steps.

1. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

Trace Dependents

To show arrows that indicate which cells use the selected cell, execute the following steps.

1. Select cell C12.

2. On the Formulas tab, in the Formula Auditing group, click Trace Dependents.

Result:

The only cell that uses the value of cell C12 is cell C13.

Show Formulas

By default, Excel displays the results and not the formulas. To display the formulas instead of their results, execute the following steps.

1. On the Formulas tab, in the Formula Auditing group, click Show Formulas.

Result:

Note: instead of clicking Show Formulas, you can also press CTRL + (`). You can find this key above the tab key.

Error Checking

To check for common errors that occur in formulas, execute the following steps.

1. Enter the value 0 into cell C12.

2. On the Formulas tab, in the Formula Auditing group, click Error Checking.

Result:

Excel finds an error in cell C13. The formula used is divided by zero.

Evaluate Formula

To debug a formula by evaluating each part of the formula individually, execute the following steps.

1. Select cell C13 (the 'Cost per person' formula).

2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

3. Click Evaluate three times.

4. Click Evaluate one more time. Excel shows the formula result.

Did you like this free Excel tutorial? Show your appreciation, vote for us.

Thank you very much! We've got many more free Excel tutorials for you. Read on, learn, and enjoy!

Go to Top: Audit Excel Formulas    |    Go to Next Tutorial: Array Formulas