Excel 2007 vs. 2003: Formula Auditing Tools Revealed

By David H. Ringstrom, CPA

It can be a challenge to get speed in an unfamiliar spreadsheet, or to check your own work in a complex spreadsheet. Someone else may have taken a different approach than you to create the formulas, or you may have lost track of which formulas refer to which cells. Many users don’t realize that Excel offers several auditing tools to make quick work of tracing formulas within a spreadsheet. I’ll first explain how to locate and use the features in Excel 2007, and then provide guidance for users of Excel 2003 and earlier.

Try it now: A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Microsoft offers a variety of tools designed to help you master the Excel 2007 interface:

• Office Online offers an online command reference that shows where your favorite Excel 2003 commands reside in Excel 2007. You can also download and install the command reference tool on your computer.
• Install the Get Started tab to put numerous training aids right at your fingertips.
• The Microsoft Office Labs website offers a free Search Commands tab that enables you to request Excel commands in your own words.

Formula Auditing Tools

Microsoft Excel has long offered the ability to audit formulas within your spreadsheet, but many users overlooked the commands tucked away under the Formula Auditing choice of the Tools menu. As shown in Figure 1, the commands have more prominent placement in Excel 2007 on the Formulas tab of the ribbon.

Figure 1: It’s easy to find the Formula Auditing tools in Excel 2007.

The best way to understand the formula auditing tools is to try them either on your own spreadsheet, or an Excel template. For instance, a ratio analysis template is available from Office Online – this site contains hundreds more free templates for Microsoft Office.

Expert tip: If you download the ratio analysis template, you can quickly erase the current inputs so that you can start with a clean slate. To do so, select cells F9 through J44, and then press the F5 key. Click the Special button, and then choose Constants, and clear the checkboxes for Text, Logicals, and Errors, as shown in Figure 2. Click OK, and then press the Delete key.

Figure 2: Go To Special enables you to select all input cells at once – you can then press Delete to clear all of the selected cells with one keystroke.

Let’s explore each of the auditing tools:

• Trace Precedents: As shown in Figure 3, this tool identifies any other cells that affect the current cell. Cell J31 contains the formula =J30/J27, so you can see a blue arrow with dots in cells J27 and J30. This feature only works in cells that contain a formula, as shown in Figure 4.

Figure 3: The Trace Precedents feature identifies cells referenced by the current formula.

Figure 4: Trace Precedents only works with formulas that refer to other cells.

• Trace Dependents: As you might expect, the Trace Dependents feature identifies any cells that refer to the current cell, as shown in Figure 54. This can be helpful when you’re not sure if deleting a particular cell will affect the integrity of your spreadsheet. In this case you can see that cells J34 and F36 refer to cell F34. In addition, formulas on other worksheets also refer to cell F34, as indicated by the spreadsheet icon.

Figure 5: Trace Dependents can help confirm whether it’s safe to delete a particular cell.

Identify Offsheet Locations: You might think that the spreadsheet icon in Figure 5 would provide a means to determine what other cells refer to cell F34. However, instead you must click on the arrow itself. Doing so will display a Go To window with a list of the cells on other worksheets that rely on the current cell. Choose an address, and then click OK to visit this cell. To return to the present cell, press F5, and then click OK.

• Remove Arrows: This command removes any formula auditing arrows placed by the Trace Precedents or Trace Dependents commands.
• Show Formulas: Spreadsheet errors are often caused by a user typing over a formula either inadvertently, or on purpose. As shown in Figure 6, this feature displays all of the formulas in a worksheet at once. Click Show Formulas again to return to the usual mode of displaying values. As you can see, it’s easy to identify that cell J24 was overwritten – all of the surrounding cells above and below begin with an equal sign.

Figure 6: Show Formulas enables you to tell at a glance if a key formula has been overwritten.

Keyboard shortcut: Press Ctrl-~ to toggle between formula and value mode in Excel.

• Error Checking: The Error Checking feature identifies errors in your worksheet, such as #REF!, #N/A!, #DIV/0! and so on. As shown in Figure 7, the Error Checking feature displays a dialog box that provides insight into the error. You can click Next or Previous to navigate through each error in the worksheet, or click Trace Error to display the blue and red arrows shown in Figure 7. Click Ignore Error to skip this cell in future error checks, or click Edit in Formula bar to modify the formula. Finally, the Options button enables you to control Excel’s default error checking capability. As shown in Figure 8, unclick Enable Background Error Checking to disable the little green arrows that Excel places in cells that it perceives as errors.

Helpful: Click Remove Arrows to eliminate the arrows that Trace Errors puts on your worksheet.

Figure 7: Error Checking identifies and helps resolve spreadsheet errors.

Figure 8: Clear the checkbox for Enable Background Error Checking to disable the green arrows that Excel places in cells that contain perceived errors.

• Evaluate Formula: This tool enables you to step through formula and see exactly how the result is derived. For instance, in Figure 9 cell H26 contains the formula =J26/J27. I clicked on that cell, and then choose Evaluate Formula. Each time I clicked Evaluate Formula in the dialog box, Excel replaced a cell reference with the value, so that =J26/J27 becomes 25000/65000. If I clicked Evaluate Formula one more time, the dialog box would display .38, which is the final result of the formula. The Step In and Step Out buttons allow you to evaluate nested portions of a formula if necessary.

Figure 9: Evaluate Formula enables you to convert cell references to values.

• Watch Window: The Watch Window, shown in Figure 10, enables you to monitor the value of cells anywhere in your workbook. To use this feature, click on Watch Window, and then click Add Watch. You can select one or more cells, which will then appear in the Watch Window. Notice that the Name column displays the range name assigned to a cell, which makes it much easier to identify the cells that you’re watching. Further, you can customize the Watch Window:
• Resize the columns as needed, to make them smaller, or even hidden
• Resize the Watch Window to consume more or less screen space
• Drag the Watch Window on to the ribbon to dock it at the top of your screen
• Click the Watch Window icon to temporarily hide the window, and then click it again whenever you want to display the watches that you set

The Watch Window enables you to monitor cells anywhere in your workbook, without having to continually return to those cells to monitor their values.

Figure 10: The Watch Window enables you to keep an eye on key cells within your workbook.

Excel 2003 and Earlier

All of the features are available in earlier versions of Excel. You can access the formula auditing tools in two ways:

• Choose Tools, Formula Auditing, and then make a choice from the submenu
• Right-click on the main menu in Excel, and then choose Formula Auditing from the toolbar list.

Circle Invalid Data: The Formula Auditing toolbar in Excel 2003 and earlier contains buttons for Circle Invalid Data and Clear Validation Circles. These features are used to identify inputs that do not conform with Data Validation rules, as opposed to general input errors. In Excel 2007 these commands are available under the Data Validation drop-down menu on the Data tab of the ribbon.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.