Excel online training course

excel lesson 17, Auditing

Formula Auditing Toolbar

Excel gives us a usefull to to audit formulas.
This is the "Formula Auditing Toolbar" or in Excel 2000 and 97 the "Auditing Toolbar".

If you are using Excel 2000 or 97 then you can see that you cannot do the same as with the other toolbars.
With Excel 2000 and 97 you also have only one way to open the "Auditing Toolbar".
Choose "Tools", "Auditing" and "Show Auditing Toolbar" in the menubar.

When you work with 2003 or XP then we can open the toolbar by clicking on "View", "Toolbars" and "Formula Auditing" in the menubar or by clicking on "Tools", "Formula Auditing" and "Show Formula Auditing Toolbar" in the menubar.

The buttons of this toolbar explained:

error checking and improve (XP en 2003)   error checking
trace precendants   new comment
remove precendant arrows   circle invalid data
trace dependants   clear validation circles
remove dependant arrows   show Watch Window
remove all arrows   evaluate formula

Finding precendants and dependants

We can select each cell and formulacell which is being used in a formula and find the precendants and dependants of it.
When we do this Excel will show a blue line, from the dependant to the precendants or from the precendants to the dependants, depending on the button you have clicked in the toolbar.
Its usefull to know from which cells the formulacell gets its information.

To know the precendants you first choose the formulacell, and then click on the "Trace precendants" button .

In some cases the precendants are also the formulacells, by clicking on the "Trace Precendants" button multiple times we get to see the actual precendants.

This can also be reversed.
Select a cell and click on the "Trace dependants" button .
Once again blue arrows will become visible which show the dependants:

To remove all arrow we click on the "Remove all arrows" button .
Or remove them one by one by clicking on the "Remove precedent arrows" or "Remove dependant arrows" button.

We can also audit the linked information in another worksheet or workbook.
Select a linked cell and click on the "Trace precendants" button.

A black dotted line will appear with a worksheet icon.
When we click on the arrow a "Go To" window will open where we can find a list will all our linked cells.

Choose one and click on "Ok".
The linked cell in another worksheet will be immediatly selected.

Trace errors

Precendants in formulas with errors can also be traced.
Click on the cell which has the formula with the error.
Click on the "Trace error" button.
Excel shows us an arrow which shows us the cause of the error.

Recover the error in the formulabar.

Tracing and Recovering errors (XP and 2003 only)

We can trace errors and recover our formulas by clicking on the "Error Checking" button .
A window will open with in it the first error in our worksheet:

Clicking on "Help on this error" button opens the windows help window concerning that error.
Close the help window.
We click on the "Resume" button to continue.
Then we click on the "Show Calculation Steps" button.
In the new window we click on the "Evaluate" button to evaluate the underlying references.
If we have nested functions in our formula, we can walk through the calculations which are executed in our formula step by step untill we find the error.

If neccesairy you can click on the "Restart" button to start with all the calculations from the beginning.
When the underlying reference which is evaluated is a reference to another formula, you can click on the "Step In" button to view the function in that window and evaluate its value.
Click on the "Step Out" button to return back to the original formula.
When you are done evaluating the formula you click on "Close" to return back to the "Error Checking" window.

We can also click on the "Ignore Error" button to ignore the error.
But once we've done that, we can't go back and perform "Error Checking".
You can compare this with a spelling check, once you have ignored the spelling mistake, it will allways ignore it.
We can undo this by clicking on the "Options" button and then the "Reset Ignored Errors" button.

We can click the "Edit in Formula Bar" button to show the formula in the formulabar, where we can also solve the problem.
Once we think we solved the problem, click on the green arrow on the leftside of the formulabar.
Click the "Resume" button to search for the next error.
If all errors have been corrected we click on the "Ok" button in the popup window.

Use Watch Window

Checking values of cells can give you quite a headache if your working with large worksheets.
Excel added a function to help make these things alot easier.
The "Watch Window" can show the value of any cell(s) in a seperate window.

Click on the "Watch Window" button excell_small_104.jpg (1.497 bytes).
Click "Add Watch".
Select the cells from which you want to show the values and click "Add".

You can trace the value of the selected cell while you are working on any part of the spreadsheet.
You can also doubleclick on a cell in the "Watch Window" to go to that cell.
To remove information from the "Watch Window" we choose the cell(s) and click on "Delete Watch".

Validate cell

Excel allows you to trace if the correct type of information is entered in a cell.
We can limit the information to numbers, a date or values of a list for example.
I will give a example here where the information we want to insert has to limit itself to a number between 1 and 12.
Click on "Data" and "Validation" in the menubar.
A new window will open.

In the first tab we enter what we allow.
In this example it will be a whole number between 1 and 12.
Next we click on the "Input Message" tab
We enter a title and a input message.
When you check the "Show input message when cell is selected" field, you will get to see the following in our worksheet when the cell is selected:

Next click on the tab "Error Alerts".

We choose a image that we want to display from the "Style" field and enter a title and message.

When we enter wrong information into the cell which does not comply with our conditions we will get to see a popup window like the one below.

When you click yes, then Excel will accept your information, if you click on "No" you can enter new information into the cell.

When you choose "List" in the first tab of the "Data validation" window you could have selected a range from a number of cells in your worksheet.

Or you can make a list by entering all options seperates by a comma.

The result :

To make any changes, we select the cell and click "Data", "Validation" in the menubar once more.