Formula Auditing In Excel
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.
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.
Remove Arrows
You can remove arrows clicking Remove Arrows button.
Show formulas
This button will show formulas in cells instead of results of formulas. In this example it will looks like that:
Formula Auditing greyed out
Sometimes it may happen that formula auditing button will be greyed out. There might be a few causes for that.
First check if your there is worksheet protection enabled.
File – Options – Advanced – Display options for this workbook – For objects show – change it from Nothing (hide objects) to All.
