Home // Video Tutorials // 0 – 3 (Beginner) // 3 // Formula Auditing in Excel

Formula Auditing in Excel

How to use Formula Auditing in Excel to understand where the cells came from and which other cells are using them.

 

Formula Auditing in Excel

Formula auditing in excel is a fundamental part of spreadsheets, and any one who works on spreadsheets regularly should make use of this tool.
What is does is, it helps you visualize what cells are being used, and for what purposes.
In my opinion you should seriously consider making this a permanent part of your Toolbars and using it as regularly as possible.

In order to load the Toolbar you do the following, and depending on your version of Excel it might differ slightly;

You can go to view
Toolbars
And in this list you might have one called Formula Auditing. If you don’t have the Formula Auditing is that list
Go to customize and you’ll probably have to
Click on the Toolbars tab
And a little lower down you’ll see Formula Auditing
and you can switch it on
you can close that, and you now have the Toolbar
If you don’t find it in the View folder, you might look in the Tools,
where their might be a Formula Auditing button
Or else go to the Help feature
and actually type in Auditing or Formula Auditing to see how to load it on your version
This is a fundamental part of any spreadsheet development, and as I mentioned earlier, I highly recommend you anchor it in your Toolbars.

Now to go through what the buttons mean. Here I have an example of a simple spreadsheet;

this button over here if you hover over it, says Trace Precedents. What is does is it points out what sheets or what cells are using, are being used in that formula. So for example if I
click on that cell, you’ll see it’s going to E11
If you click on the Trace Precedents,
a blue line comes up and points it out to you
If you click on the next one which now contains a bit of a formula
You can do the same thing and it’ll point out the cells being used.
Obviously this can get a bit messy and you might want to remove these arrows, you can either say

Remove Precedent Arrows
Or you can use this Remove All Arrows key
What is nice about this feature as well is that if for whatever reason, the cell you are looking at looks at another spreadsheet or a worksheet within the workbook; it can actually take you there. So you’ll get this dotted line.

And If you double click on the dotted line
It’ll actually ask you if you want to go there
You’ll say click on the one you want to go to
And you will be taken to where it is on the spreadsheet
The other useful tool is the Trace Dependants tool. What that allows you to do is click on a cell and specify and ask, what other cells are relying on this particular cell. So for example,

if we click on the Tax rate
and click Trace Dependants, it points out all the cells that are using that cell
This is extremely useful particularly where you are about to delete a cell or information in a cell, just to check that no other formulas are actually relying on it. In a similar way

you can remove Dependant Arrows; or
you can delete all the arrows on your sheet,
Depending on your version of Excel, you may also have an Error Checking function,

if you click this function,
Excel has a couple of error checks built in, and you’ll work through your spreadsheet and it will point out certain areas which it thinks are risky and you’ll just need to look at it
So if we click on this, it’ll tell us that the cell I20 has got an inconsistent formula,
it will explain why, and you can have a bit of a look through here
As not all versions of Excel have that, I am not going to focus too much on this feature

What the Auditing Toolbar allows you to do is quickly spot patterns in your spreadsheet. So for example;

if we click on the selling price per unit,
we say Trace Precedent, we can see that, that is where it’s looking,
we go to the next one,
we click, we can see where it’s looking,
we go on, and you can actually see a pattern forming, and what eventually happens, is you quickly spot where the pattern suddenly doesn’t make sense.
So, and that’s what I call trawling through the spreadsheet.

In a similar way you can actually use Trace Dependents,

you can say what is using that ,
that’s fine,
move across,
what is using that,
and so you can go on.
And you can look at the patterns and identify if there are any problems.
The Auditing Toolbar is very, very important in spreadsheets and adds a lot of value and makes it a lot easier to find errors.