Level 2 - Lesson 18 - Formula Auditing
Category: [General,Excel] Demo Available
Formula Auditing
There is another method that we can use to troubleshoot and this is with the Auditing feature of Excel. This feature is mainly used to try and pin point any errors that your formulas are producing. To be honest with you we have only ever used this out of curiosity and do not find it all that helpful. I put this down to the fact that when I see an error in a formula cell I have a pretty good idea what the problem is by the error type being generated. We will run through it though as you may need it at some stage.
Go to Formula Auditing options on the Formulas tab and you will see the various options.
Trace Precedents
Draws tracer arrows from the cells that supply values directly to the formula in the active cell (precedents). To trace the cells that supply values indirectly to the formula in the active cell, click the Trace Precedents button again.
Trace Dependents
Draws a tracer arrow to the active cell from formulas that depend on the value in the active cell. To add additional levels of indirect dependents, click the Trace Dependents button again.
Remove Arrows
Removes all tracer arrows from the worksheet.
Show Formulas
Shows the formulas on your worksheet. This options toggles on and off. (You can see the same by using Ctrl ˜)
Error Checking
Checks your worksheet for errors and if it finds them, brings up a dialog box showing you the type of error you have got, and giving options to fix it.
Evaluate Formula
Allows you to breakdown and edit a formula containing an error, effective with nested formulas. This option is an alternative to the Insert Function Dialog, which can be used equally as efficiently to break down formulas.
Let's create an error in a formula to see how this option can help us. We will use the formula =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no").
First of all, Cut and Paste the formula into cell H30
Now in cell A1 enter this formula =na(). This will create the #N/A error for us. If you now look at cell H30 it too will be reading #N/A.
Let's see how the Evaluate Formula option can help us!
Click in cell H30, the click the Trace Precedents button under Formula Auditing options on the Formulas tab. You should now see two arrows leading into cell H30, one red and one blue. These are coming from cells A1 and A2
To move directly to these cells, double click on either the red or blue arrow head. To move back to the formula cell, double click the round dot at the start of the arrow.
Once back in the formula cell, click Remove Arrows.
Click in cell A1 and click the Trace Dependents button and you will have a red colored arrow leading to the formula cell. Again you can navigate straight to the cell by double clicking on the dot.
Make sure you are back in cell A1 and click Remove Arrows.
Now with H30 selected click the drop down to the right of Error Checking then select the Trace Error button. Excel will draw a red arrow and immediately take you to the error cell.
Click Remove Arrows
In case you aren't sure, when using Auditing functions, a red arrow means an error. That is why when we initially clicked in cell H30 and then clicked the Trace Precedents button Excel used a red arrow coming from cell A1 (red means error) and a blue one from cell A2.
Ok, armed with this information you will eventually be able to confidently write those very long complicated Excel formulas you have no doubt seen. If somebody asks you how it works (or why it doesn't work) you won't need to make out you are suddenly very busy and run away! By the time you have worked your way through it they will either think your are a related to Albert Einstein or meet you with a blank stare, either way they will know that, you at least know what your talking about (and they will be right).
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples