Formula auditing

In this article you will learn everything about formula auditing. It is especially about Trace Precedents and Trace Dependences which can help you to understand what is going on in the worksheet.

In the Formulas tab you can see a section called Formula Auditing.

Formula auditing ribbon

Trace Precedents - which cells using this formula?

Commands contained therein provide an analysis of the formulas from your workbook. For example, in a situation when you are in a cell that contains a specific formula, and you want to check the cell to which it refers. Then you can use the Trace Precedents command: This will display the arrows from all the cells that are used in the formula to cells analyzed.

Excel tells us, from which the data depends on our formula by selecting the color cell source. You can see an example below. Below cell C2 is directly dependent on B2 and C12.

Formula auditing Trace Precedents

Using the same function again, Excel puts another arrow until the all the cells with formulas that have an influence on our cell, will be referred to their predecessors.

Tracking dependencies - which formulas use this cell?

The dependency tracking shows that cells depend indicated, the opposite of tracking predecessors. To do it use the Trace Dependents button.

The following example shows how the cells change, if you change the value in cell B2.

Formula auditing Trace Dependents

Remove Arrows

You can remove arrows clicking Remove Arrows button.

Show formulas

This button will show fomulas in cells instead of results of formulas. In ths example it will looks like that:

Formula auditing show formulas