|Title||How to debug Excel spreadsheets|
|Authors||Rayman Meservy & Marshall Romney|
|Publication||Journal of Accountancy|
|Series||Volume 220, Number 5, November, page 46|
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.
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 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.