Excel: Audit Formulas To Ensure They’re Correct

Excel has a variety of powerful tools in it’s toolbox to help you see what is going on in a spreadsheet with formulas.

Formula Auditing can show you which cells are using what information in them and where the information within the active cell comes from. Formula Auditing can even help you trace and fix errors!

This guide is full of information on Formula Auditing and great Excel tips to help streamline your work.

Obviously ensuring that your formulas a working correctly and only using the appropriate data is vital to the integrity of your calculations across the board from simple sums through to advanced business intelligence tools.

We offer Excel training for all levels of experience!

 

Formula Auditing Group Overview

Shows the formula auditing section in the ribbon

This is the Formula Auditing Group, it belongs in the Formulas tab.

Here you will find all sorts of great tools to help ensure your formulas are working as intended.

Getting familiar with the formula auditing group and Formulas tab will help you greatly.

Trace Precedents and Trace Dependents

– These are great tools for making the relationships in your workbook clearer. Trace Precedents and Trace dependents are some of the most important tools for formula auditing.

When you have a large excel spreadsheet all sorts of cells are affecting one another, and it can be difficult to figure out exactly what information is going in to your active cell.

Trace precedents and trace dependents will help you by drawing arrows between cells and making the relationships obvious.

Error Checking

The Error Checking tool is going to help you find exactly what is causing an error in Excel and correct it.

Sometimes your cell will say something like “#DIV/0” when you type in a formula – this is where Error Checking comes in.

Using the Error Checking will create special red arrows that figure out where this issue is coming from.

Remove Arrows

– Lots of the tools in the Formula Auditing will draw arrows all over your worksheet, and while this is great for error tracking, it doesn’t help a lot when you already understand the issue!

The Remove Arrows tool will simply get rid of all these arrows for you.

Show Formulas

The Show Formulas button will change all your cells to show the formulas inside, to help you find errors in the formula rather than the data.

A lot of errors exposed with Show Formulas can be caused by incorrect cell referencing. This can be helped with our comprehensive guide on all types of cell referencing.

Evaluate Formula

– Error checking will help you find where an issue is coming from, but Evaluate Formula will explain the issue and even correct it for you!

Trace Precedents

Trace Precedents shows where the active 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 F8.

After selecting the cell, click Trace Precedents in the formula auditing group.

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 I click Trace Precedents in the formula auditing group.

Shows the tracing precedents arrows for a given cell

Don’t forget to use Remove Arrows after to get rid of these arrows!

 

Trace Dependents

Trace dependents show which other cells will be affected when you change a given cell.

To see this, click on the Formulas tab, double click on the cell you want to know about and then click on the Trace Dependents in the formula auditing group.

In the picture below you can see the the ‘12%’ in cell G2 is used in the calculations in cells B16 – I16.

Trace Dependents is drawing all these arrows to demonstrate every cell the percentage has affected.

Tracing dependants arrows, showing the percentage affecting all of next year costs

 

Trace Dependents is a great tool for reverse engineering a spreadsheet.

Don’t forget to use Remove Arrows after to get rid of these arrows!

 

Show Formulas & Cell Arguments

You can press the Show Formulas button in the Formula Auditing Group in order to change the whole spreadsheet to simply show formulas inside them rather than the actual values.

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

Excel showing how each cell is involved in a given cells formula by highighting them

 

Double click on the cell with the formula and its 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.

This will also expand any relevant drop down menus. View our full guide to drop down menus in excel here.

 

Common Error Messages

This is a list of the most common errors messages, why they occur and how you can fix them.

#DIV/0!

– This error appears when a formula using division refers to the divisor cell which contains a zero or is blank.

For example, =A2/A7 will result in this error if A7 is blank or contains zero.

#NAME?

– This error appears when a formula refers to a range name that doesn’t exist in the worksheet.

You may have used the wrong range name in the formula, or you may have forgotten to put quotation marks around some text used in the formula, causing Excel to think that the text refers to a range name.

#NULL!

– This error appears when you don’t use the correct separator for formula arguments.

For example, if you were adding three cells together and type =B1+B2 B3, when you hit enter, Excel will display this error because the third plus sign is missing and has a space instead

#NUM!

– This error appears when Excel finds a problem with a number in the formula, such as the wrong type of argument in an Excel function or when the formula produces a number too large or too small to be represented in the worksheet.

#REF!

– This error appears when a formula refers to a range name that doesn’t exist in the worksheet.

You may have used the wrong range name in the formula, or you may have forgotten to put quotation marks around some text used in the formula, causing Excel to think that the text refers to a range name.

#VALUE!

– This error appears when you type the wrong type of argument or operator in a function or when you call for a mathematical operation that refers to cells that contain text entries.

For example, if a formula reads =C1+C2, but C2 contains the word “Sales” instead of a number, Excel will return this error.

#########

– This error appears when a column is not wide enough or a negative date or time is used.

To correct the problem, you can widen the column or, if it is a date/time issue, make sure that the date and time are accurate.

You can also use Error Checking to fix these!

 

Error Checking

Excel will show you at the click of a button where your spreadsheet errors are using Error Checking.

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’ in the formula auditing group.

Highlights the error checking button

 

This is what will open up. It will go to the 1st error on the sheet in this dialog box.

Shows the error checking dialog 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 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 in the formula auditing group:

Highlights where to click to trace errors

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’ in the formula auditing group, the red arrow shows that the error in that cell is caused by an error in cell L7.

Demonstrates how tracing errors points with arrows where errors are coming from in formula cells

 

Once the error has been fixed, remember to use the Remove Arrows tools to bring the workbook back to being more readable!

 

Evaluate Formula

You will find the Evaluate Formula button under the formulas tab in the formula auditing group.

It may seem a bit confusing at first, but once you understand how it works, it’s a fantastic tool.

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

Shows the many errors in the % column

 

We first click on cell F6 and click on evaluate formula in the formula auditing group. This is what appears:

Evaluate formula dialog box step 1, shows the formula

 

Evaluate formula shows the two cells that are used in the formula in F6. See how E6 is underlined under the pink dot?

By clicking evaluate, it will show the value of that cell:

It then evaluates the nmber equivalent of the cell id

 

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.

Shows the other cell ID number equivalent

 

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

And tells you what error specifically it causes

 

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 F6 has in it, which is E6/E19.

Click again, and it will show you the formula of the first cell within the cell E6, which is D6*C6

Result after pressing restart

 

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 F6.

Result after pressing step in

 

When you click Step Out (move back up a level), you will then have the option to step into C6 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.

Dialog box displays the final error so you can fix it

 

This may seem very long from reading this, but in practice using the Evaluate Formula tool 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.

Start by clicking “Watch Window” in the Formula Auditing group.

Shows where to find the watch window

 

In this picture, the total is in cell C47 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.

Shows the watch window pop up

 

Click ‘Add’ and the little window will close, and the info will appear in the main ‘Watch Window’.

Shows how the watch window places the values at the top of the page now

 

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.

 

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:

Shows the circuar reference warning

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

 

Shows where the circular reference is

As you can see, cell E18 looks empty, but when you click on it, you can see the formula in the formula bar.

It is not displaying a total because of the circular reference.

Click in the formula bar and change E18 to E16, and everything will work correctly again.

Fixes the circular reference and it changes

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.