spot_img

Formula Auditing In Excel

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 formulas in cells instead of results of formulas. In this example it will looks like that:

Formula auditing show formulas

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.

Template

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell