How to Debug Excel Spreadsheets: These Techniques Can Help Find or Fix Errors Hidden in Hundreds, or Even Thousands, of Rows, Columns, and Formulas
[ILLUSTRATION OMITTED] 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, go to the online version of this article at journalofaccountancy.com/debug-spreadsheet to download the "Spreadsheet to Debug.xlsx" file. The download fink is near the top of the page. The instructions below refer to Excel 2013 but also work, with some tweaks, for Excel 2010. [ILLUSTRATION OMITTED] 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"). [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] [ILLUSTRATION OMITTED] 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. …