There 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.

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

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

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

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

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.

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.

** **

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.

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**

**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.

**Remove the Arrows**

You can use the button **Remove Arrows** to remove all the arrows used 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.

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

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

very easy to understand notes .thanks.