Excel: Audit Formulas To Ensure They’re Correct
Contents
Formula Auditing lets you evaluate and fix your errors, smoothly and seamlessly.
It shows you your formula’s dependencies and gives insights into what is causing errors.
The Formula Auditing Group
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.
1 – Trace Precedents And 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 affect one another, and it can be difficult to figure out exactly what information is going into your active cell.
Trace precedents and trace dependents will help you by drawing arrows between cells and making the relationships obvious.
2 – 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.
This tool is your best friend when learning how to audit your own worksheets.
3 – 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.
4 – 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 are caused by incorrect cell referencing.
5 – 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!
Tracing 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.
Don’t forget to use Remove Arrows after to get rid of these arrows!
Tracing 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 ‘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.
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 to a formula to help you identify its relationship with other cells.
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 lists.
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.
This is what will open up. It will go to the 1st error on the sheet in this 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:
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.
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.
This tool is a little more complex, only taught on our advanced Microsoft Excel courses.
This is the spreadsheet I will be using for this example:
We first click on cell F6 and click on evaluate formula in the formula auditing group. This is what appears:
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 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.
Click evaluate again, and it will show the error message from the cell, and you have the option to restart.
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
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.
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.
This may seem very long from reading this, but in practice, using the Evaluate Formula tool is quick.
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.
In this picture, the total is in cell C47, which cannot be seen at the top of the worksheet.
We want it to remain on screen without copying it over and ruining our page layout.
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’ and the little window will close, and the info will appear in the main ‘Watch Window’.
If you want to, you can dock this to the top of your workbook by double-clicking the title bar of the watch window.
To get a whole set of data or headings in view at all times, try using freeze panes.
You will now be able to see the effect of any changes that are made to figures in column C.
