Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

10.5 Auditing Formulas and Fixing Errors

Workplace Software and Skills10.5 Auditing Formulas and Fixing Errors

Learning Objectives

By the end of this section, you will be able to:

  • Explain the reasons formulas may need to be audited
  • Identify the various errors that may be present in worksheets and tables
  • Identify the cells involved in a formula
  • Implement the appropriate options for auditing various aspects of formulas

WorldCorp receives many client orders every day, and the corporation needs to have an accurate count of inventory to ensure that they have the capacity to deliver orders on time. This real-time inventory handling and customer relationship management can only happen by implementing computer systems that track these business activities. Microsoft Excel can be programmed to help in fulfilling these tasks with the right data. Yet, as you can imagine, this enormous data load can be daunting, and occasionally, small errors may be made in some formulas. Excel has various tools for the user to identify such errors, and thus correct them.

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.

Cell J4 is selected with =MAXIFS($E$2:E$10,B$2:B$10,”Antonio”) in the formula bar. The cell contains “$26,016.00.” Two arrows start from cells B2 (Antonio) and E2 ($10,582.00) and come together to point to cell J4.
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.

Formulas is selected in Excel Options window. Error checking rules options are visible and checked.
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).

(a) Spreadsheet displays two tabs along the bottom. (b) Summary tab is selected at the bottom of the spreadsheet. Cell B2 is selected and =data!F2+data!G2 displays in the Formula bar.
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.

# Code Explanation How to Fix It
#### This common error is a formatting error rather than a formula error. It occurs when the column is not wide enough for the numbers to be displayed. Make the column wider.
#NAME? This error is displayed if the function is not well-written, such as if there is a typo in the name of the function, or the designated function name is not complete or is written incorrectly. Find the correct wording of the function by clicking on the Insert Function tool.
#DIV/0! This error happens when you divide by zero. This can also be caused if a linked cell reference has no value. Enter a value in the cell, or add the correct constant in the denominator.
#NULL! This happens when the Excel notation syntax rules are not obeyed, such as if an operator is incorrect or missing. Follow the pop-up menu that appears when writing a function. It tells where commas should be, what value should be referenced, and so on. If the error was made in the past, you can consult the roster of functions on the Formulas tab in the ribbon and selecting the Insert Function icon.
#NUM! This happens when a cell reference contains contradictory or unsound numbers that stop the function from calculating the result. Check the referenced cells, and retype the numbers that are unsound.
#REF! This means that the cell references in a formula are not there anymore, or the cell reference was entered incorrectly in the formula. Find the correct cell reference and fix the formula.
#VALUE! This happens when a cell reference has a different data type, for example, when you use a function that requires two numerical values but you insert text for one of them. Use the same data types.
#N/A This happens when a function searches for a certain value and the function cannot find it. Change the criterion on the search operators (e.g., LOOKUP function).
#GETTING_DATA This is a temporary error. While waiting for Excel to calculate a complicated query, Excel will process the answer in a few seconds and display this error. Wait for Excel to calculate the answer.
Table 10.5 Common Code Errors These are many of the most common code errors, and how to fix them.

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.

Cell F3 selected ($97,128.43) and formula bar reads =PMT(E3,$C$2,-$C$4,0,0). Find & Select button options: Find, Replace, Go To, Go To Special, Formulas, Notes, Conditional Formatting, Constants, Data Validation, Select Objects, Selection Pane.
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)
(a) Formulas (Numbers, Text, Logicals, Errors) selected in Go To Special pane. (b) Select the cells that you would like to watch value of: =’name of worksheet’!$F$3 displays in Add Watch pane.
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)
The “Watch Window” is shown with a row of headers and a row of data below (in parentheses): Book (operati…), Sheet (cell re…) Name, Cell (F3), Value ($97,128.43), and Formula (=PMT(E3,$C$2,-$...).
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.

Evaluate Formula tool displays Reference (‘cell reference abs’!$F$3) =Evaluation: (PMT(E3,$C$2,-$C$4,0,0)). To show the result of the underlined expression, click Evaluate. The most recent result appears italicized. Reads below. Evaluate button selected.
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)
The “Evaluate Formula” tool is shown. PMT(0.05,10,-750000,0,0) is listed in the Evaluation window and the Evaluate button is selected.
Figure 10.49 To see the result, choose Evaluate. (Used with permission from Microsoft)
Evaluate Formula tool displays Reference (‘cell reference abs’!$F$3) =Evaluation: (PMT(E3,$C$2,-$C$4,0,0)), then cell reference abs’!$C$4 = $750,000.00. Across the bottom reads, the cell currently being evaluated contains a constant. Step Out button selected.
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.

(a) Cell E3 selected (5.00%). Column C selected. (b) Column F selected and formula bar displays =PMT(E3,$C$2,-$C$4,0,0). $97,128.43 inside cell F3. Microsoft Excel window displays No cells were found.
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.

Add-ins is selected in Excel Options window. Add-ins are highlighted in Add-ins pane with information listed beneath.
Figure 10.52 Access Excel Options from the File tab to find Add-Ins. (Used with permission from Microsoft)
(a) COM Add-Ins window displays selection pane for Add-ins available. Acrobat PDFMaker Office COM Addin and Inquire are selected. (b) Workbook Analysis is selected from the Report command group.
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)
A pane displays options for Items (lists Summary, Workbook, Formulas along with other options), Results (lists Item, Sheet Name, Cell Address, Formula), and Export (includes Item detail limit and Excel Export button).
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)

Mac Tip

In the Mac version, all add-ins are located in the Tools menu.

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.

Cell F4 selected and =PMT(E4,$C$2,-$C$4,0,0) in the formula bar. Cell F4 shows $99,500.83. Three arrows from cells C2 (10), C4 ($750,000.00), and cell C12 (995,008.27) all meet and point to cell F4.
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.

Mac Tip

In the Mac version, the Error Checking tool is located in the Formulas menu.

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).

Error Checking box opens to Show Calculation Steps button highlighted. In the spreadsheet, cell F4 displays #NUM! instead of a number as in other cells in that column.
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)
(a) Evaluate Formula box displays Reference: (Sheet2!$F$4) and Evaluation: (PMT(0.055.0,-750000.0.0). #NUM! displays in cell. (b) Formula bar displays =PMT(E4,$C$2,-$C$4,0,0). #NUM! displays in cell C12 with small green triangle at the top left.
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)
In the spreadsheet, cell F4 is selected and lists $99,500.83 inside. Microsoft Excel box displays: The error check is complete for the entire sheet.
Figure 10.58 Running Error Check again indicates that the spreadsheet is now error-free. (Used with permission from Microsoft)

Spotlight on Ethics

The Tick and Tie Method

All businesses should have multiple ways of checking and auditing their financial statements. Often this is referred to as “checks and balances.” One example for banking purposes would be to require two signatures on all checks that are written on the account. That then ensures that two different people have approved the check. The concept of auditing in accounting is checking and double-checking that the financial statements are accurate and follow the appropriate accounting standards. This not only protects the company from the consequences of errors, but it can also ensure that the company employees are not committing any wrongdoing.

One method to accomplish this is the “tick and tie” internal auditing method, which involves verifying the numbers on one financial statement with those on another. Managers can use this method internally to monitor transactions for irregularities (payments to unknown vendors, for example) or verify that company financial statements balance before the external auditors do a yearly review. It is better to monitor constantly and consistently than to wait for the external auditor to find the errors. Also, by having a regular internal audit of the finances of the company, you can create a culture that expects honesty and integrity. If you happen to find a deliberate error by an employee, you can address it quickly. By using the tools available in Excel, you can set up an internal audit system to use regularly, thus creating the culture in the organization and identifying misconduct quickly.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Apr 15, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.