What We Know About Spreadsheet Errors

Abstract

Although spreadsheet programs are used for small "scratchpad" applications, they are also used to develop many large applications. In recent years, we have learned a good deal about the errors that people make when they develop spreadsheets. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists. These error rates, although troubling, are in line with those in programming and other human cognitive domains. In programming, we have learned to follow strict development disciplines to eliminate most errors. Surveys of spreadsheet developers indicate that spreadsheet creation, in contrast, is informal, and few organizations have comprehensive policies for spreadsheet development. Although prescriptive articles have focused on such disciplines as modularization and having assumptions sections, these may be far less important than other innovations, especially cell-by-cell code inspection after the development phase.

Development of a database for chemical mechanism assignments for volatile organic emissions

“…The use of Excel spreadsheets and macros is probably not the optimum approach to maintain complex databases such as this (e.g., see Panko, 1998), but completely changing the implementation would require more support than is currently available. Ideally, maintaining such a database should be part of the larger efforts to improve emissions inventories or to develop and maintain improved modules for models, but at present this is not considered a part of either effort.…”

Detecting code smells in spreadsheet formulas

“…Secondly, there are papers that address common errors in spreadsheets, like [19], [20], together with their causes.…”

Data organization in spreadsheets

“…Many researchers have examined error rates in spreadsheets, and Panko (2008) reported that in 13 audits of real-world spreadsheets, an average of 88% contained errors. Popular spreadsheet programs also make certain types of errors easy to commit and difficult to rectify.…”