Ozgrid, Experts in Microsoft Excel Spreadsheets

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Formula Auditing & Troubleshooting.

 

Excel Training Level 2 Lesson 10

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 2 TRAINING INDEX

FREE EXCEL HELP

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 I 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 Tools>Forumla Auditing to see the Formula Auditing sub-menu, or right click in the toolbar area of your screen and select Auditing to bring up the Auditing Toolbar.  We will look at the options on the Toolbar here.

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.

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.

Remove Precedent Arrows

Removes tracer arrows from one level of dependents on the active worksheet.

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 Dependent Arrows

Removes tracer arrows from one level of precedents on the active worksheet. To remove the next level of arrows, click the Remove Dependent Arrows button again.

Remove All Arrows

Removes all tracer arrows from the worksheet.

Trace Error

If the active cell contains an error value such as #VALUE or #DIV/0, draws tracer arrows to the active cell from the cells that cause the error value.

New Comment

Inserts a comment at the insertion point.

Circle Invalid Data

Identifies all cells that contain values that are outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell, click the circled cell, and then click Validation on the Data menu.

Clear Validation Circles

Hides circles around cells that contain values outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell before you remove circles, click the circled cell, and then click Validation on the Data menu.

Please note that if your are not familiar with Validation let me know and we will cover it in the next lesson. It is a very useful function.

Show Watch Window

Will allow you to add a formula that frequently generates an error to a window to keep an eye on it for any future error messages.

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 the Auditing Toolbar 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 Auditing toolbar can help us!

  Click in cell H30, the click the Trace Precedents button on the Auditing toolbar. 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 Precedent Arrows.

  Click in cell A1 and click the Trace Dependents button and you will have a red coloured 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 Dependent Arrows.

  Now with H30 selected click on the Trace Error button. Excel will draw a red arrow and immediately take you to the error cell.

  Click Remove All Arrows

In case you don't realise, when using the Auditing toolbar 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).

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 2 TRAINING INDEX