Formula Auditing in Excel

Excel has a variety of powerful tools in it’s toolbox to help you see what is going on in a spreadsheet with formlas. Which cells are using the information in them and where the information within the cell comes from, they can even help you trace errors.

Tracing Precedents

This shows where the cell is getting the information from that it is using in the formula

To see this click on the Formulas tab, then click into the cell you want to know about, I did two in the picture below – the first one was cell F7, then click ‘Trace Precedents’ .

This will add the blue boxes and the arrow showing which cells are being used in the formula in that cell. The formula itself can be seen in the function bar above.

For the second one I clicked into cell F13 and then clicked ‘Trace Precedents’.

Auditing 2

 

Tracing Dependants

If you changed a cell, what other cells would it effect? What cells are using this info?

To see this, click on the Formulas tab, click on the cell you want to know about and then click on the ‘Trace Dependants’

In the picture below you can see the the ‘12%’ in cell G2 is used in the calculations in cells B3 – M3

Auditing 1

  Showing Formulas & Cell Arguements

Excel will automatically colour code the arguements in a formula to help you identify its relationship with other cells

showing formulas

Double click on the cell with the formula and it’s contents will show in the formula bar, different colours for different cells. If a cell is referenced twice it will keep the same colour in both places. You are able to drag and drop the colours to a different cell to change the formula.

 Common Error Messages

Error messages

Error Checking

Excel will show you at the click of a button where your spreadsheet errors are. The example we have is a very small simple worksheet and it’s easy to see the errors, but imagine how useful this will be on large complicated spreadsheets.

With the spreadsheet open that you want to check, click on the ‘Formulas’ tab and then ‘Error Checking’

Excel Toolbar

 

This is what will open up, it will go to the 1st error on the sheet.

 

error checking box

 

Click on the ‘Edit in formula bar’ and correct it. Whether you make any changes or not you will be given the option to ‘Resume’ in the error box and it will continue looking for errors.

Tracing Errors

This will help identify which cells are being used for the formula and which ones are causing the errors. When you use this, red arrows show the the linked cells where there is an error and blue cells show linked cells where there is no error. Here is where you find the button:

trace error button

In the diagram below cell L7 has the error. By clicking on cell L7 and clicking ‘trace error’ It shows in blue the cells that are involved but do not have errors in them.

By clicking on cell L8 and clicking ‘trace error’ the red arrow shows that the error in that cell is caused by an error in cell L7

Tracing errors v2

 Evaluating Formulas

You will find this button under the formulas tab in the formula auditing section.

It seems a bit confusing at first, but once you understand how it works, it’s a great tool.

This is the spreadsheet I will be using for this example:

Eval formulas 1st screenshot

We first click on cell F7 and click on evaluate formula, this is what appears:

Eval formulas 1

It shows the two cells that are used in the formula in F7. See how E7 is underlined under the pink dot? By clicking evaluate it will show the value of that cell:

Eval formulas 2_5

It automatically then moves over to the next cell in the formula, E19

Click evaluate  again and it will show the value of E19, in this case zero.

Eval formulas 3

Click evaluate again and is will show the error message from the cell and you have the option to restart.

Eval formulas 4

After you click on restart ‘Step In’ will be highlighted. Think of stepping in and out as moving down and back up levels. Click on that and it will show you the formula that F7 has in it, which is E7/E19 click again and it will show you the formula of the first cell within the cell E7 which is D7*C7

Eval formulas 5

Click step in (drill down to the next level) again and it will show you the value of that cell, the only option then is to ‘Step out’

Remember that the starting cell for all of this was F7

Eval formulas 6_5

When you click Step Out (move back up a level) you will then have the option to step in to C7 or to ‘step out’ another level. I have ‘Stepped out’ back to where cell E19 is underlined and I have then ‘Stepped In’. You can see from the picture below that cell E19 is empty which is causing the #DIV/0! error as you cannot divide by 0. You can now correct the error and move on to the next one.

Eval formulas 7

This may seem very long from reading this, but in practise is quick.

 

Learn To Use the Watch Window

On a larger worksheet you can use the watch window to see cells that are not in the main view so you don’t need to keep scrolling to see them or moving to different parts of the worksheet.

In this picture the total is in cell C280 which cannot be seen at the top of the worksheet. By clicking it, then pressing the ‘Watch Window’ button, which is greyed out in this image as I have gone past that, it will open another window. Click ‘Add Watch’ and the cell that you had selected in the worksheet will be in the info bar. Click ‘Add’ the top little window will close and the info will appear in the main ‘Watch Window’.

Watch window 1

If you want to you can dock this to the top of your workbook by double clicking the title bar of the watch window.

You will now be able to see the effect of any changes that are made to figures in column C

Watch window 2

 

Dealing With Circular References

I have just discovered that if you open a worksheet with a circular reference in it, a warning message will pop up. This message explains exactly what a circular reference is:

circular ref 1

Excel will display in the bottom left-hand corner that the sheet has this error and where it is:

circular ref 2

As you can see cell E18 looks empty but when you click on it you can see the formula in the formular bar. It is not dislaying a total because of the circular reference. Click in the formula bar and change E18 to E16 and everything will work correctly again.

circular ref 3

If you want to learn more about this, attend our Excel Essentials training

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone

Related courses