Formula Auditing in Excel – Trace Precedents and Dependents

April 29th, 2011 by Tips Master | Posted under Excel.

Formula Auditing in Excel 2007There are a couple of auditing tools available in Excel. These are of great help when we are working troubleshooting excel formulas or when required to do the auditing of spreadsheets.  We will see in this tutorial some of the tools available.

 

 

 

Formula Auditing in Excel


These tool in Excel allows us to Trace Precedents in a formula, Trace Dependents , Show formulas, Error checking and Evaluating formula in Excel.

Formula auditing Group in Excel 2007

I have already covered the Evaluating formula in a previous post, hope you have read it already. Today in this post we will see the Trace Dependents and Trace Precedents tools.

Trace Precedents in the same worksheet
See the picture below

 Sample data for Trace Precedents in Excel
I have entered some random values in some cells for the sake of explaining this.  We have values in B8 and E4. H4 contains the formula E4+B8 which gives the result in the cell as 300(200+100). We need to know which are the cells precedent to this.

Go to Formulas Ribbon. You will find the Trace Precedents in the Formula Auditing group. See the screen shot below

Formula Auditing in Excel 2007
The basic requirement for using the formula auditing tool is that, it can be used only where a cell contains a formula referring to other cells.

We will see how we can use this tool.

Select the cell H4 and select Trace Precedents. See the Picture Below

Trace Precedents in Excel 2007

You can see the blue arrows referring to the cells E4 and B8 which are the Precedents of the cell H4.

Trace Precedents Arrow in Excel 2007

We can move to the precedent cells by simply double clicking on the Blue arrows.

Trace Precedents in a different worksheet/ Workbook


Now i have changed the formula to =E4+Sheet2!D4 whereby i am including the cell D4 from sheet2 in the calculation.

 

5. Example 2

When I press the Trace Precedents again, we can see a different line (dotted line) which is referring to the different sheet. If you want to know where is it actually referring to , you can double click on the arrow which will Pop up the Go to box and will show the reference there. If the formula refer to multiple sheets then you an see multiple references in the Go to Window. It also shows the worksheet reference which will help to know whether it is in the same workbook or in a different workbook. Selecting the references and clicking the Go to button will take you to the another worksheet or workbook.

 Go to window refereing to another Sheet in Excel
Trace Dependents option.


In the above example if we want to know which are the cells dependent to the cell B8 i.e. whether any other cells are referring to the particular cell, we can use the Trace Dependents option.

 

 Trace Dependents in Excel 2007

Select the cell B8 and press Trace Dependents . We can see a blue arrow linking to cell H4. this shows that the formula in cell H4 contains a link to cell B8

Trace Dependents Arrow in Excel 2007

Referencing to Multiple cells


I have entered formula in mutliple cells which refers to cell B8. We can see the tool refers to each and every cell which links to the selected cell.  See the below picture. Clicking on each arrow will take you to the respective cells.

Referencing to Multiple cells in Excel

Remove the Arrows

You can use the button Remove Arrows to remove all the arrows used in Formula auditing.

Remove Arrows in Formula Auditing

So we have seen how useful is the Formula Auditing tool in excel when working with Formulas. Will be back with more tips.

Give your comments/ Queries.

Comments

6 Responses to “Formula Auditing in Excel – Trace Precedents and Dependents”
  1. James says:

    Thanks for the formula tracing info – that was very helpful.

  2. Ian says:

    Just wanted to say thanks, trying to get my head around Excel 2011 again, after not using it for 7 years!

  3. very easy to understand notes .thanks.

Do you have any comments on Formula Auditing in Excel – Trace Precedents and Dependents ?