Transcripts for the above video clip:

AUDITING TOOLBAR PROJECT

The auditing toolbar is a fundamental part of Excel, 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.