Formula Auditing Tool
Formula Auditing Tool
Download this file to follow along with the video and to do the next exercise.
The Formula Auditing Tool is one of the most important tools you will learn about. It helps you visualise where a formula is looking and can be used when checking whether a formula has been correctly built in Excel.
We use the Formula Auditing Tools to:
- Check which cells are being referred to in a calculation
- Identify differences in formula in rows or columns
- Check whether cells are important to the spreadsheet when we want to delete them (or rows or columns)
Watch the video clip and/or read the text to understand how to use the Formula Auditing Tools
If YouTube doesn't work, click here
Where to find the Formula Auditing Tool
You can find the Formula Auditing tools in the formula tab
- Trace Precedent
- Trace Dependent, and
- Remove arrows
Trace Precedent (which cells are being used in a calculation)
The Trace Precedent button shows you which cells are used in a calculation i.e. which cells PRECEDE the cell you are in.
If you click on a cell you are investigating and push the Trace Precedent button you will see some blue lines appear as shown below. Note that the blue lines point to the cells referred to in the formula bar i.e =F11*(1+$E$4).
Visually it is much easier to see what is being used this way.
Another benefit is that if you double click on one of the blue lines, you are taken to the cell (watch the video clip to see how this works).
We often use this feature to trawl through a row or column of formula to identify cells that have different formula in them. The idea is that a well built spreadsheet has consistency in formula and the pattern shown by the blue lines should look similar. In the example below you can quickly see that there is something different with cell I11 as all the other cells refer to the 5% and this one refers to the 7%.
Another benefit of the Trace Precedent button is that it can show AND take you to cells that are in different sheets or workbooks. Note that if you click on cell I4 and look at the formula you will see it looks at another sheet. As shown below if you click on the Trace Precedent button you get a dashed line with a spreadsheet at the end which indicates that this refers to a cell outside of this sheet.
You can double click on this dashed line. In this case a new box will appear with a listing of all the cells that are outside this sheet (in the example below there is only one cell). You can click on the item you want to follow and click OK. You will be taken straight to the cell.
To get back to where you were, just click the F5 button on your keyboard and push OK (don’t click anything else).
In order to be able to see what is happening we recommend that you use the REMOVE ARROWS button to clear your screen occasionally.
Trace Dependent (which other cells are using the cell you are in)
As useful as the Trace Precedent is, you can do it manually if you really have to (look at the formula and look at each cell). The Trace Dependent button performs a function that you cannot perform manually.
Trace Dependent will tell you which other cells are making use of the cell you are in. In the example below note that cell E1 contains the tax rate which is a number of 30%. By clicking the Trace dependant button we can see all the cells that make use of the 30%.
This is particularly useful when you are thinking of deleting cells.
So if you saw the situation below and were tempted to delete the 7% in cell I4 (looks untidy), you must first check if it is being used by clicking the Trace Dependent button. You will then see that it is an important cell.
Similarly to trace precedent,
- if cells in other sheets are using this cell, you will see a dashed line.
- all these lines can be double clicked on to take you to the relevant cells
- you should use the REMOVE ARROWS button to remove blue lines when it gets too busy.