How to find errors in Excel formulas
Q. I am trying to create a formula in our accounting system, but I keep getting errors. What can I do to get the formula to work?
A. All Excel users know how powerful formulas are, but sometimes it can be challenging to get them to work correctly. Anyone using Excel formulas needs to understand the resources available for auditing those formulas. Let’s look at how to use Excel’s auditing tools to audit formulas and ensure the accuracy of data.
To follow along, download this Excel workbook. A video demonstration is also available at the bottom of this article.
Note that the walkthrough and video were made using Microsoft Excel 365 for Pcs. Other versions of Excel may work differently.
Excel’s auditing tools are found, fittingly enough, on the Formulas tab in the Formula Auditing group, as shown in the screenshot below:
Here’s how to use the tools. Please note that while the example formula is very simplistic, these techniques also work well on complex formulas.
TRACE PRECEDENTS
Trace Precedents allows you to understand formula dependencies by highlighting the cells that are referenced in or have an impact on an Excel formula. To use this tool, select a cell with a formula in it and click the Trace Precedents button. In the following screenshot, Trace Precedents shows that cells H3 and J3 are referenced in cell K3, which contains the simple formula =H3+J3 calculating the sum of the values in cells H3 and J3.
This tool makes it easy to understand the dependencies between cells.
REMOVE ARROWS
As you can see in the screenshot for Trace Precedents, Excel places arrows on the spreadsheet to illustrate. Remove Arrows is a tool that allows you to remove the arrows that are created by the Trace Precedents and Trace Dependents tools. To use this tool, select the cell or cells that have arrows and click the Remove Arrows button. You will need to do that before continuing the walkthrough in the next section, as you can’t run Trace Dependents on a cell being referenced by Trace Precedents arrows.
TRACE DEPENDENTS
Trace Dependents allows you to understand formula dependencies by highlighting the cells that are affected by the selected cell. To use this tool, select the cell for which you want to trace the dependents and click the Trace Dependents button. Returning to the same formula as was used for Trace Precedents, click in cell H3 to see what cell(s) are being affected by this cell.
As shown in the screenshot above, the Trace Dependents tool points out the cell(s) affected by the value in cell H3.
SHOW FORMULAS
Show Formulas is a tool that allows you to toggle between viewing the formulas and the results of the formulas in your spreadsheet. To use this tool, click Show Formulas or use the shortcut Ctrl + ` (grave accent, under the tilde). The screenshot shows how the formulas in column K are displayed after Show Formulas is turned on.
ERROR CHECKING
Error Checking is a tool that allows you to quickly identify and diagnose errors in your formulas. To use this tool, select the cell or cells that contain errors and click the Error Checking button. For example, in the screenshot below, the formula in cell K3 that calculates the sum of the values in cells H3 and I3 is showing an error. We can use the Error Checking tool to diagnose the errors in this formula.
As you can see in the screenshot above, the Error Checking tool identifies the error as being a wrong data type referenced by the formula in cell K3. If you then click on Show Calculation Steps, you can see the values for each cell referenced by the formula. In this case, the letters “td” were entered into cell H3, making it impossible to add the values in H3 and J3. Again, this is a simple example, but the technique is valuable in tracking down errors in large, complex formulas and spreadsheets.
EVALUATE FORMULA
Evaluate Formula is a tool that breaks complex formulas into their individual components, making them more understandable. To use this tool, select the cell that contains the formula you want to evaluate and click the Evaluate Formula button. The following screenshot shows that there is a formula in cell K3 that calculates the sum of the values in cells H3 and J3. We can use the Evaluate Formula tool to understand how this formula is calculated. As you can see, the Evaluate Formula tool breaks down the formula in cell K3 into its individual components and allows you to evaluate each component.
Although this formula is very simple for illustrative purposes, the tool is extremely useful for complex formulas.
WATCH WINDOW
Watch Window is a tool that allows you to monitor the value of selected cells while you work on other parts of your spreadsheet. To use this tool, select the cell or cells you want to monitor and click the Watch Window button. For example, citing the simple formula in cell K3 that calculates the sum of the values in cells H3 and J3, we can use the Watch Window tool to monitor the value of cell K3 while we work on other parts of our spreadsheet.
Take advantage of the auditing tools available in Microsoft Excel. By using these tools, you can quickly identify and fix errors, monitor the value of selected cells, and ensure the integrity of your data.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
