10.5 Auditing Formulas and Fixing Errors

Reasons to Audit Formulas

As a business grows, its activities grow in complexity, requiring higher-level and concurrent data analyses. As a result, managers build large Excel workbooks to handle these business operations, designing spreadsheets with a wide selection of formulas, functions, PivotTables, arrays, data ranges, What-If Data Tables, and other Excel data exploration tools. Each of these tools requires user input, which makes them inevitably subject to user error. The process of using Excel tools to monitor and fix errors in worksheets is called auditing formulas.

Remember that almost all formulas or functions have relationships to other areas of the workbook. Cell references or named ranges may even refer to other cell references, creating a complex web of references. If the key data points do not have the right formula or function, the resulting value will not be correct. You may also see an error message in Excel if the user settings are set to alert the user when a formula or function has a broken link or incorrect syntax.

Identifying Errors in Worksheets and Tables

There are many ways to check for errors in your workbook. These include tracing precedent functions, tracing dependent functions, showing the formula, and automated checking of errors. When tracing precedent or dependent functions, you are finding the relationships between cells.

When you are tracing precedent functions, you are finding the source of an error in a formula by locating the cells that provide the data to perform the calculation in the active cell. The arrows will point to the cells that are used to construct the formula used in the active cell so you may review them before making a correction. When you are tracing dependent functions, you are finding any cells affected by the active cell. You would want to do this before deleting a cell from a worksheet. These specifically relate to checking cell references and the information found within them. Figure 10.42 shows the output when tracing precedents for a cell.

Figure 10.42 The arrows point to cells B2 and E2 that are used to determine the highest value in the formula. (Used with permission from Microsoft)

Showing the formula is simply looking at a certain formula or function and manually checking its syntax. There is a command on the Formulas tab called Show Formulas that will automatically show which cells contain formulas. The actual formula in the cell is displayed, replacing the value that was calculated from that formula. It changes the content of the worksheet to show exactly what formulas are in the cells. This is a good first step to finding errors in formulas. To hide the formulas and return to the original spreadsheet with the computed values, simply select the Show Formulas command again. There is also an Error Checking command, which can help to identify specific errors in formulas.

Before any error checking can occur, first make sure that your version of Excel is tracking the errors when they happen. Click on the File tab and select Options. In the dialog box, choose the Formulas tab, and check the “Enable background error checking” option. You should also check all of the “Error checking rules” boxes, and then click OK, as shown in Figure 10.43.

Figure 10.43 Error checking starts with having Excel notify you if any error happens. (Used with permission from Microsoft)

Remember that there are two main characteristics that all formulas have:

1. All formulas must start with the equals sign (=).
2. Formulas have constants, cell references, or both, and operators, functions, or both.

Constants are numbers that are typed inside a formula; they can be integers or rational numbers. Operators are math notation signs for performing different calculations, such as +, –, *, /, and ^. These operators must respect the order of operations (PEMDAS). If the formula is complex—if it has multiple functions, for example—you must ensure that the function’s syntax rules are obeyed. For example, the PV function must include values for RATE, NPER, PMT, and FV, in that order, or the function will not work.

There are several types of potential errors. You may have syntax errors (not having the right argument in the function or having too few or too many arguments); capacity errors (in Excel, you cannot have more than 64 functions in a cell); or sheet or cell reference errors. It is worth noting that, when linking a formula in one worksheet to a cell in another worksheet, Excel automatically adds the sheet’s name and “!” after the sheet’s name; you must double-check that you didn’t delete this mark. For example, WorldCorp’s data for two clients is seen in Figure 10.44a. You summarize the overall total on a separate sheet, which references the first sheet. Notice in the formula the “!” after the worksheet named “data” and before the cell reference you include in the calculation (Figure 10.44b).

Figure 10.44 (a) You can use the data on one worksheet in a formula on another worksheet. (b) Excel will include the name of the worksheet followed by “!” in the formula. (Used with permission from Microsoft)

Additionally, several errors have specific code assignations that should automatically appear in your cell if you have an error. These code errors are preceded by “#” to indicate that they are errors. Some of the common code errors are described in Table 10.5.

Identifying the Cells Involved in a Formula

Excel makes it easy to find cells that meet certain criteria. For example, Excel can highlight all cells in a worksheet that contain formulas. To do this, select a cell (Figure 10.45) and go to the Find and Select icon on the Home tab, then choose Go To Special (Figure 10.46a). Next, select Formulas, then click OK (Figure 10.46b). All the cells that contain formulas will be selected (highlighted) in that worksheet.

Excel also has a built-in feature, called the Watch Window, that allows users to see where each constant, reference, and operators are correct. Select the Watch Window icon (on the Formulas tab in the ribbon). The Watch Window will be blank, so choose Add Watch, and select the cell or cells you want to analyze, as shown in Figure 10.47. Putting a cell or a range in the Watch Window allows you to monitor the cells you choose to ensure they remain error-free as you continue to work in a worksheet. You can add multiple cells to the Watch Window by following the same process. The Watch Window tells you the value and the function, the sheet, the workbook, the cell, and the defined name to help you identify any errors.

Figure 10.45 The Watch Window can tell you the relationships of the cell to other cells. You can use Find and Select to highlight cells that contain items such as Notes or Formulas. (Used with permission from Microsoft)

Figure 10.46 (a) Use Go To Special to define what elements you want Excel to search for in the cells on the worksheet. (b) Watch Window is an alternative to see the formulas in cells on a worksheet. (Used with permission from Microsoft)

Figure 10.47 Click Add Watch to list the cells and the formulas. (Used with permission from Microsoft)

Another way to dissect a formula is by selecting the cell and choosing Evaluate Formula (on the Formulas tab). This command solves the formula piece by piece to ensure that the formula is built correctly. First, the formula displays as shown in Figure 10.48. Then, select Evaluate, which will solve the first argument of the function, then click on Evaluate again and the second argument gets resolved. Continue to click Evaluate to solve the formula one argument at a time, until you reach the last argument, where the final step is the calculation result. This lets you confirm that the cell is calculating correctly.

Figure 10.48 shows the Evaluate window when it first opens, and Figure 10.49 shows the last step before the final answer is shown in the window. If clicking the Evaluate button does not provide clarity on the formula, you can use Step In, which shows a more detailed view of all of the components of the formula. You are essentially going into the formula to see all the cell references that contribute to the data in that cell. If you use Step In, you will need to click Step Out to go back to the macro view of the formula and to proceed to the next argument, as shown in Figure 10.50. You can use this method to break down complex formulas that might include cell references in other worksheets.

Figure 10.48 The Evaluate Formula tool can easily show where the error is by solving for each argument. Choose Evaluate to go step-by-step through the formula. (Used with permission from Microsoft)

Figure 10.49 To see the result, choose Evaluate. (Used with permission from Microsoft)

Figure 10.50 Step In takes you into the formula for a more detailed view, and Step Out goes back to the original full formula instead of the individual components. (Used with permission from Microsoft)

Another way to see the references of a formula is by simply selecting the cell in question, and then pressing Ctrl+[ (Control and open bracket) to see dependent references, or Ctrl+] (Control and close bracket) to see precedents. This simple method selects all the cell references of the formula in question, as shown in Figure 10.51a. You can see that cells C2, C4, and E3 are selected and shaded in. These commands serve a similar function as the Trace Dependents and Trace Precedents tools; they show the dependent cells or precedents, but they do not draw a line like the Trace Dependents and Trace Precedents tools do.

If a cell has no dependent cells—cells that are depending on this data from other worksheets, or workbooks, or from another table in the same worksheet—then you cannot perform the Ctrl+[ command, as shown in Figure 10.51b. In this example, there were no dependent cells.

Figure 10.51 The Ctrl+[ command is a simple way to see dependent cells. (a) Excel highlights the dependent cells. (b) If there are no dependent cells, Excel will let you know that there are none. (Used with permission from Microsoft)

The most comprehensive way to see your cell relationships is by installing the Inquire add-in. First, go to the Add-ins menu (File > Options > Add-ins) (Figure 10.52). Look for Manage drop-down at the bottom of the window and select COM Add-ins, from the drop-down menu. Then, select Go. Then, check Inquire and click OK, as shown in Figure 10.53a. Excel adds a new tab to the ribbon. From that ribbon tab, select Workbook Analysis (Figure 10.53b). The workbook will be displayed with all its formulas and connections between cells, and all formulas will be categorized by the type of function used (Figure 10.54). This new ribbon tab also offers the features Workbook Relationship, Worksheet Relationship, and Cell Relationship, all of which give a graph of the relationship between the sheet and the precedents and dependents in the worksheet.

Figure 10.52 Access Excel Options from the File tab to find Add-Ins. (Used with permission from Microsoft)

Figure 10.53 (a) Inquire is a formula-auditing tool that does what the Watch Window does but also gives the found errors and organizes the formulas by categories. (b) Inquire will be added as a tab giving you several options to choose from. (Used with permission from Microsoft)

Figure 10.54 You will be able to see all formulas in the spreadsheet with their location identified by the cell reference. (Used with permission from Microsoft)

Choosing and Implementing Formula Auditing Options

Let’s look at an example to practice identifying errors and choosing and implementing the correct ways to fix those errors. Using the WorldCorp loan installment example, suppose that the loan the company settled on has a 5.5 percent interest rate, resulting in a payment of $99,500.83 per year in interest and principal. Therefore, the total cost of the increase in capacity for the manufacturing plant will be $995,008.27. Cell F4 now has precedents and dependents, as shown in the arrows in Figure 10.55.

Figure 10.55 The cell pulls data from many other cells. By using Trace Precedents and Dependents, we can visually see the cells that are connected by formulas. (Used with permission from Microsoft)

The data in Figure 10.55 looks good, but suppose you get a #NUM! error in cell F4 (Figure 10.56). Recall that this type of error means that the formula contains contradictory or unsound numbers that stop the formula from calculating the correct result. First, try the Error Checking tool. Change the location of the N variable to a blank cell, C1. This gives the error shown in the figure. The formula cannot be calculated based on a blank cell. Choose the Error Checking tool from the Formulas tab in the Formula Auditing command group.

When the window appears, choose Show Calculation Steps (Figure 10.56). This opens the Evaluate Formula command, which shows the formula already solved (Figure 10.57a). When examining the formula, you can see the values of 0.065 for the rate and 750,000 for the principal. Because the PMT function requires you to input N, or the number of payment periods, you would also expect to see 10 in the formula, but there is a 0 instead of a 10 in the N variable location in the formula. Thus, you can see where the problem in the formula is, so you can fix it. If you then close the Evaluate Formula window, you are taken back to the Error Checking tool from where you can choose Edit in Formula Bar. When selected, the cells used in the formula are highlighted. At this point, you should see that the formula uses cell C1, not C2 as was intended. Revise the formula to replace C1 with C2 (Figure 10.57b), and run the Error Checking tool again. Now the worksheet doesn’t have any errors (Figure 10.58c).

Figure 10.56 The formula cannot calculate an answer because of an error on the worksheet. The Error Checking tool is similar to the Evaluate Formula tool, except that it checks the whole worksheet at once. (Used with permission from Microsoft)

Figure 10.57 (a) Show Calculation steps will show you where the error is located. (b) You can adjust the formula to address the error on the spreadsheet. (Used with permission from Microsoft)

Figure 10.58 Running Error Check again indicates that the spreadsheet is now error-free. (Used with permission from Microsoft)