Content uploaded by Raymond R. Panko
Author content
All content in this area was uploaded by Raymond R. Panko on Aug 13, 2014
Content may be subject to copyright.
A preview of the PDF is not available
What We Know About Spreadsheet Errors
Journal of Organizational and End User Computing
Abstract and Figures
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.
Figures - uploaded by Raymond R. Panko
Author content
All figure content in this area was uploaded by Raymond R. Panko
Content may be subject to copyright.
... To help exemplify our ideas, our prototype (described in Section 2) focuses on critical thinking of the kind invoked when working with spreadsheets, and in particular in the activity of data-driven shortlisting (described in Section 1.2). Spreadsheets make a particularly interesting case study because the scope of risk and error in spreadsheets has a long history of popular and scholarly scrutiny [Panko, 1998, Powell et al., 2008, which makes them unique amongst application types that have extremely wide use across domains, professions, and organisations. The impacts of spreadsheet-related errors are huge and continuous, as carefully documented by EuSprIG. 2 Errors have numerous sources Aurigemma, 2010, Rajalingham et al., 2008]: incorrect formulas, incorrect data, issues with data structuring [Chalhoub and Sarkar, 2022], errors in comprehension [Srinivasa Ragavan et al., 2021], unit-related errors , and mistakes in copy-paste reuse [Joharizadeh et al., 2020], are but a few among many. ...
Generative AI, with its tendency to "hallucinate" incorrect results, may pose a risk to knowledge work by introducing errors. On the other hand, it may also provide unprecedented opportunities for users, particularly non-experts, to learn and apply advanced software features and greatly increase the scope and complexity of tasks they can successfully achieve. As an example of a complex knowledge workflow that is subject to risks and opportunities from generative AI, we consider the spreadsheet. AI hallucinations are an important challenge, but they are not the greatest risk posed by generative AI to spreadsheet workflows. Rather, as more work can be safely delegated to AI, the risk is that human critical thinking -- the ability to holistically and rigorously evaluate a problem and its solutions -- is degraded in the process. The solution is to design the interfaces of generative AI systems deliberately to foster and encourage critical thinking in knowledge work, building primarily on a long history of research on critical thinking tools for education. We discuss a prototype system for the activity of critical shortlisting in spreadsheets. The system uses generative AI to suggest shortlisting criteria and applies these criteria to sort rows in a spreadsheet. It also generates "provocations": short text snippets that critique the AI-generated criteria, highlighting risks, shortcomings, and alternatives. Our prototype opens up a rich and completely unexplored design space of critical thinking tools for modern AI-assisted knowledge work. We outline a research agenda for AI as a critic or provocateur, including questions about where and when provocations should appear, their form and content, and potential design trade-offs.
... Although previous research has suggested that errors are prevalent in spreadsheets (Panko and Halverson, 1996;Panko 2005), we have only limited information on the types of errors that occur, why they occur, and how to avoid them. We recently summarized the research literature on spreadsheet errors (Powell et al., 2006a) and came to the following conclusions: ...
All users of spreadsheets struggle with the problem of errors. Errors are thought to be prevalent in spreadsheets, and in some instances they have cost organizations millions of dollars. In a previous study of 50 operational spreadsheets we found errors in 0.8% to 1.8% of all formula cells, depending on how errors are defined. In the current study we estimate the quantitative impacts of errors in 25 operational spreadsheets from five different organizations. We find that many errors have no quantitative impact on the spreadsheet. Those that have an impact often affect unimportant portions of the spreadsheet. The remaining errors do sometimes have substantial impacts on key aspects of the spreadsheet. This paper provides the first fully-documented evidence on the quantitative impact of errors in operational spreadsheets.
... In the nearly forty years since the release of VisiCalc in 1979, spreadsheets have become the single most popular end-user programming environment, with 750 million users of Microsoft Excel alone [Foley 2010]. Spreadsheets are ubiquitous in government, scientific, and financial settings [Panko 1998]. ...
Spreadsheets are one of the most widely used programming environments, and are widely deployed in domains like finance where errors can have catastrophic consequences. We present a static analysis specifically designed to find spreadsheet formula errors. Our analysis directly leverages the rectangular character of spreadsheets. It uses an information-theoretic approach to identify formulas that are especially surprising disruptions to nearby rectangular regions. We present ExceLint, an implementation of our static analysis for Microsoft Excel. We demonstrate that ExceLint is fast and effective: across a corpus of 70 spreadsheets, ExceLint takes a median of 5 seconds per spreadsheet, and it significantly outperforms the state of the art analysis.
... Despite these issues and some conceptional shortcomings of spreadsheets, no other technology has managed to supplant spreadsheets on a broader scale. For these reasons -and also due to the number of existing spreadsheets -numerous scientists have investigated the creation, detection and prevention of faults in spreadsheets [24,47]. Yet, we see three areas that require more attention: overconfidence, collaboration and tool support. ...
Spreadsheets are powerful tools which play a business-critical role in many organizations. However, many bad decisions taken due to faulty spreadsheets show that these tools need serious quality assurance. Furthermore, while collaboration on spreadsheets for maintenance tasks is common, there has been almost no support for ensuring that the spreadsheets remain correct during this process. We have developed an approach named Spreadsheet Guardian which separates the specification of spreadsheet test rules from their execution. By automatically executing user-defined test rules, our approach is able to detect semantic faults. It also protects all collaborating spreadsheet users from introducing faults during maintenance, even if only few end-users specify test rules. To evaluate Spreadsheet Guardian, we implemented a representative testing technique as an add-in for Microsoft Excel. We evaluated the testing technique in two empirical evaluations with 29 end-users and 42 computer science students. The results indicate that the technique is easy to learn and to apply. Furthermore, after finishing maintenance, participants with spreadsheets "protected" by the technique are more realistic about the correctness of their spreadsheets than participants who employ only "classic", non-interactive test rules based on static analysis techniques. Hence, we believe Spreadsheet Guardian can be of use for business-critical spreadsheets.
... End-user programming, such as formulas in spreadsheets, has been extremely error-prone. For example, Panko [42] found there is a high probability of error aecting the bottom lines of any substantial spreadsheet. Despite the prevalence of consequential errors, it has been hard to get end users to write tests. ...
What if end users could own the software development lifecycle from conception to deployment using only requirements expressed in language, images, video or audio? We explore this idea, building on the capabilities that generative Artificial Intelligence brings to software generation and maintenance techniques. How could designing software in this way better serve end users? What are the implications of this process for the future of end-user software engineering and the software development lifecycle? We discuss the research needed to bridge the gap between where we are today and these imagined systems of the future.
This review paper primarily focuses on the document "Data Organization in Spreadsheets" by [1], which offers a practical guide to organizing spreadsheet data effectively. Emphasizing foundational principles such as consistency, simplicity, and reproducibility, the document provides actionable advice to minimize errors, improve collaboration, and ensure data integrity. Through this analysis, the review highlights the document's strengths, including its alignment with the "tidy data" paradigm and emphasis on error prevention and reproducibility. Additionally, the review discusses limitations, such as the lack of advanced spreadsheet techniques and domain-specific examples, and suggests enhancements like incorporating case studies and introducing programming tools like R and Python for scalable workflows. This review situates the document within broader data management practices, connecting it to key works in the field, such as those by [2], [4] and [5],further enriching its context and applicability
This research explores how group- and organizational-level factors affect errors in administering drugs to hospitalized patients. Findings from patient care groups in two hospitals show systematic differences not just in the frequency of errors, but also in the likelihood that errors will be detected and learned from by group members. Implications for learning in and by work teams in general are discussed.
A Small Matter of Programming asks why it has been so difficult for end users to command programming power and explores the problems of end user-driven application development that must be solved to afford end users greater computational power. Drawing on empirical research on existing end user systems, A Small Matter of Programming analyzes cognitive, social, and technical issues of end user programming. In particular, it examines the importance of task-specific programming languages, visual application frameworks, and collaborative work practices for end user computing, with the goal of helping designers and programmers understand and better satisfy the needs of end users who want the capability to create, customize, and extend their applications software. The ideas in the book are based on the author's research on two successful end user programming systems - spreadsheets and CAD systems - as well as other empirical research. Nardi concentrates on broad issues in end user programming, especially end users' strengths and problems, introducing tools and techniques as they are related to higher-level user issues. Bonnie A. Nardi is a Member of the Technical Staff at Hewlett Packard Laboratories.
The abstract for this document is available on CSA Illumina.To view the Abstract, click the Abstract button above the document title.
The evaluation of software technologies suffers because of the lack of quantitative assessment of their effect on software development and modification. A seven-step data collection and analysis methodology couples software technology evaluation with software measurement. Four in-depth applications of the methodology are presented. The four studies represent each of the general categories of analyses on the software product and development process: 1) blocked subject-project studies, 2) replicated project studies, 3) multi-project variation studies, and 4) single project studies. The four applications are in the areas of, respectively, 1) software testing strategies, 2) Cleanroom software development, 3) characteristic software metric sets, and 4) software error analysis.