How to debug Excel spreadsheets

CPAs are often tasked with vetting or working with numbers in a spreadsheet. And while accountants are well-trained to identify and correct accounting errors, spreadsheets bring the danger of many other types of errors. Field audit results compiled by University of Hawaii professor and spreadsheet expert Ray Panko showed errors in 88% of 113 spreadsheets audited between 1995 and 2007. That’s a scary statistic, especially when one considers that decisions involving thousands or even millions of dollars are often based on spreadsheets. Decisions based on bad information can be hazardous to an organization’s bottom line.

The ultimate goal is to have a spreadsheet that produces accurate results, but as worksheets grow in size and complexity, ensuring accuracy becomes more difficult. Making even one small change in a spreadsheet may cause a ripple effect with unexpected consequences. What can a CPA do? This article walks you through different methods and built-in tools that can be used to audit and debug Excel spreadsheets. To follow along, download the “Spreadsheet to Debug.xlsx” file. The instructions below refer to Excel 2013 but also work, with some tweaks, for Excel 2010.

GET A GENERAL UNDERSTANDING OF THE SPREADSHEET AND THE FUNCTIONS INVOLVED

When initially opening a spreadsheet, first obtain a quick overall understanding of the spreadsheet, its parts, and the interaction between formulas and values. To see this in action, open the example spreadsheet. When you open the spreadsheet, a circular references warning pops up. Ignore this warning for now. The spreadsheet contains student scores in various categories, which are then weighted to get an overall weighted score for the class. Examine how it is put together. Open the + buttons at the top of the page. See which cells contain formulas by clicking on the FORMULAS tab, and then in the Formula Auditing group, selecting Show Formulas (or the shortcut Ctrl+~) (see “Overview of Where Formulas Are in the Spreadsheet”).

While the formulas are showing, zoom out so you can see the whole spreadsheet at once by using the bar on the lower-right corner (or Ctrl+mouse wheel). Because certain areas of this spreadsheet have been named, the name appears over those sections (see “Overall View of the Spreadsheet Layout”).

Note where the formulas are and that there are inconsistencies in the formulas’ sizes. In fact, as you zoom in on some of those inconsistencies, you see that they contain hard-coded values rather than formulas. For example, StudentMM is always going to get 82.22% in column BB regardless of how he or she has done on individual assignments (see “Formulas That Have Been Overwritten”).

At some point, that cell formula was overwritten. In this case, if you copy the formula in the cell below it into cell BB42, you will get the correct score. Knowing where the formulas are and what they are supposed to do is the first step.

CORRECT THE SYNTAX ERRORS

Syntax errors are the easiest to find because the spreadsheet expresses them in bold. Note the #DIV/0! error in cell BC55 (you have to turn off Show Formulas to see this, then turn it back on). The formula itself looks OK. Try to figure out where the error is coming from and fix the problem. After clicking on the cell BB55, the FORMULAS tab=>Formula Auditing=>Trace Precedents helps by showing all inputs to the formula (see “Use Trace Precedents Multiple Times to Trace Cell Inputs”). The error has come from the cell with the red arrow. If you click Trace Precedents again, you can see which cells are being used in those formulas (i.e., the error has propagated from BB4).

Click Trace Precedents again, and you see the error came from AL4. If you continue clicking Trace Precedents, the red line remains at AL4, thus that is where the error originated. Click in the Formula Auditing group on the FORMULAS tab, and click Remove Arrows to remove the arrows. Select cell AL4 and click Trace Dependents, also in the Formula Auditing group, several times to see which cells have been affected by this error. This is also a good way to see which cells will be affected before you delete or change any cell’s value. Examining cell AL4, you see it has a bad formula, but when you copy the formula below into cell AL4, it gives a good value (0.79). However, notice that cell BB4 still shows an error. What you did should have fixed all those cells relying on AL4. Try to determine what is going on. Any ideas?

INCOMPLETE CALCULATION ERRORS

If you select BB4 and edit it by clicking F2, and then click F9 (calculate the formula), you get the correct value (77.2119). So if you press the Enter key, you have the correct value, but what is the problem? That value is hard-coded and will never change regardless of new scores (see “Correct but Hard-Coded Value”).

So you try copying the formula from BB5 into BB4. What? The formula looks correct, but the value showing is the same as in cell BB5. And the other #DIV/0! errors have not disappeared. Try to figure out why.

If you are a bit frustrated, don’t worry, because most people are bewildered. What is happening? The spreadsheet has simply stopped calculating before it got to that cell. But why? If you look at the status bar on the bottom left of the window (see “Circular References Notification”), you will see there are circular references (in this case, there is a circular reference in AB6). A circular reference means a cell formula directly or indirectly references itself and so keeps trying over and over to recalculate the value for that cell. Finally, it just gives up!

To fix the error, go to cell AB6 and examine the formula. Unlike the formulas in the cells above and below it, this formula references itself. To fix it, change the formula to be consistent with the formulas in the cells above and below. Circular references on most spreadsheets are errors.

EXCEL’S ERROR-CHECKING TOOLS

Although cell BB4 and its dependent cells are now calculating correctly, from the left bottom of the window, you see there are still circular references. This time once again in the Formula Auditing group on the FORMULAS tab, select Error Checking, Circular References, $AD$60 (see “Finding the Circular References”). The cell AD60 is selected. Correct the formula by changing it to =AVERAGE(AD4:AD53). You now no longer have any circular reference errors.

Note that many of the cells in column AC have a small triangle in the upper-left corner of the cell, and when the cell is selected, a caution sign may appear (see “Triangle Caution Signs Indicating Possible Errors”). When you hover the mouse over the caution sign, the following message appears: “The formula in this cell refers to a range that has additional numbers adjacent to it.” This may or may not be an error. In this case, it didn’t include the cells in column D, so you will correct the formula in cell AC4 to =SUM(D4:Z4) – SUM(AA4:AB4) (you may correct it by clicking on the caution sign and then clicking on Update Formula to Include Cells) and then copy that formula down to the rest of the students.

Next, going back to the Formula Auditing group on the FORMULAS tab, select Error Checking, Error Checking to search for additional errors. An Error Checking window will appear similar to the image on the next page. Your error will be different depending on where you started. The Error Checking window will select a possible error cell and suggest what may need to be done to correct the problem. The suggested solution may not always be correct, so carefully check each error and proposed solution. The image below suggests that cell AR17 is formatted as text or preceded by an apostrophe. Thus, even though this may look like a number, formulas using it will probably not be correct. You can correct the value by clicking on Convert to Number, and Excel will move to the next identified possible error.

Go through each error option. You may decide to ignore a possible error. If there was a small triangle in the upper-left corner of the cell and you click to ignore the error, the triangle will disappear, and future error checking will not include that cell. What if someone has instructed Excel to ignore the errors before sending the spreadsheet to you? To reset the error checking, go to File, Options, Formulas, and click the Reset Ignored Errors button. Also, make sure the Enable background error checking box is checked. Also look at the error-checking rules below.

Go through Error Checking until you have cleared all errors. (Our Excel found errors in 10 cells: AU5, AR7, AZ7, AK16, AR17, BD17, BE17, AK45, BB45, and BC47.) There, doesn’t that feel better? But wait, there are still other errors that Excel didn’t find.

OTHER ERROR-CHECKING TIPS

In columns D through Z, where the Quizzes & Problems scores were recorded, there are missing values. Yes, they are hard to see. But when the formula in column A drops the lowest score, blanks will be ignored, and the next lowest score will be used in error. Those cells should have a zero placed in them. Conditional formatting could help here, but a fast nonpermanent solution is to click on Home, Editing, Find & Select, Go To Special, Blanks, OK (see “Showing How to Access Go To Special Form”). Note that two cells in the range D4:Z53 are highlighted. Put a zero in both of those cells. So are you done with that? If you have a very sharp eye, you may have noted that cell U53 has a period in it instead of the zero.

When you are done with the automatic error checker, look at your formulas again. You may still see a value amid a column of formulas that needs to be corrected.

CONCLUSION

Now that there are no obvious errors, should you trust this spreadsheet? Not yet, because the spreadsheet likely contains the hardest errors to find—logical errors. They include information put in the wrong cell or formulas that work but may not be correct. If your reputation is based on a correct spreadsheet, you should next go through the formulas to make sure they are correct. No single tool can complete this task, so the process can be time-consuming and arduous. Remember that you have the Trace Precedents and Trace Dependents tools to visually help you do this. Those tools can save you some time and headaches, though it’s important to keep in mind that no software tool can help you catch all logic errors. For that, you have to apply your brain as well.

In the end, you can’t escape the simple fact that Excel spreadsheets, especially large and complicated ones, often contain errors. These errors can be extremely difficult to spot, but the techniques outlined in this article illustrate a number of tools and methods for systematically weeding out errors from a spreadsheet. If you follow these steps, you can be confident in your spreadsheets’ viability.

About the authors

Rayman Meservy (meservy@byu.edu) is associate professor of information systems, and Marshall B. Romney (mbr@byu.edu) is the John and Nancy Hardy Professor of Accounting and Information Services, both in the Marriott School of Management at Brigham Young University in Provo, Utah.

To comment on this article or to suggest an idea for another article, contact Jeff Drew, senior editor, at jdrew@aicpa.org or 919-402-4056.

AICPA RESOURCES

JofA articles

• “Beyond Spreadsheets,” April/May 2015, page 36
• “Excel: The Power of Mapping,” April 2014, page 30

CPE self-study

Advanced Excel: Practical Applications for Accounting Professionals (#745756, text)

CPE audio webcast

“Excel for Accounting Professionals: Error Trapping and Improving Lookups,” Dec. 16, 12:30 p.m.—2:30 p.m. ET (#VCL2EXAP5012)

Conference

Digital CPA Conference, Dec. 7—9, Las Vegas

For more information or to make a purchase or register, go to cpa2biz.com or call the Institute at 888-777-7077.

Information Management and Technology Assurance (IMTA) Section and CITP credential

The Information Management and Technology Assurance (IMTA) division serves members of the IMTA Membership Section, CPAs who hold the Certified Information Technology Professional (CITP) credential, other AICPA members, and accounting professionals who want to maximize information technology to provide information management and/or technology assurance services to meet their clients’ or organization’s operational, compliance, and assurance needs. To learn about the IMTA division, visit aicpa.org/IMTA. Information about the CITP credential is available at aicpa.org/CITP.