To aid the development of spreadsheet debugging tools, a knowledge of end-users natural behaviour within the Excel environment would be advantageous. This paper details the design and application of a novel data acquisition tool, which can be used for the unobtrusive recording of end-users mouse, keyboard and Excel specific actions during the debugging of Excel spreadsheets. A debugging experiment was conducted using this data acquisition tool, and based on analysis of end-users performance and behaviour data, the authors developed a "spreadsheet cell coverage feedback" debugging tool. Results from the debugging experiment are presented in terms of enduser debugging performance and behaviour, and the outcomes of an evaluation experiment with the debugging tool are detailed.
Recognizing that the use of spreadsheets within finance will likely not subside in the near future, this paper discusses a major barrier that is preventing more organizations from adopting enterprise spreadsheet management programs. But even without a corporate mandated effort to improve spreadsheet controls, finance functions can still take simple yet effective steps to start managing the risk of errors in key spreadsheets by strategically selecting controls that complement existing user practice
This paper deals with errors in using spreadsheets and analysis of automatic recording of user interaction with spreadsheets. After a review of literature devoted to spreadsheet errors, we advocate the importance of going from error detection to interaction behaviour analysis. We explain how we analyze screen captures and give the main results we have obtained using this specific methodology with secondary school students (N=24). Transcription provides general characteristics: time, sequence of performed tasks, unsuccessful attempts and user preferences. Analysis reveals preferred modes of act…
University of California, San Diego（加州大学圣地亚哥分校）；Salesforce, Inc（赛富时公司）；Apple, Inc（苹果公司）；University of Massachusetts, Amherst（马萨诸塞大学阿默斯特分校）；Meta, Inc（Meta公司）；Eukarya, Inc（真核生物公司）
End-user programmers create software to solve problems, yet the problem-solving knowledge generated in the process often remains tacit within the software artifact. One approach to exposing this knowledge is to enable the end-user to annotate the artifact as they create and use it. A 3-level model of annotation is presented and guidelines are proposed for the design of end-user programming environments supporting the explicit and literate annotation levels. These guidelines are then applied to the spreadsheet end-user programming paradigm.
We have prototyped a "spreadsheet component repository" Web site, from which users can copy "components" into their own Excel or Google spreadsheets. Components are collections of cells containing formulae: in real life, they would do useful calculations that many practitioners find hard to program, and would be rigorously tested and documented. Crucially, the user can tell the repository which cells in their spreadsheet to use for a componen's inputs and outputs. The repository will then reshape the component to fit. A single component can therefore be used in many different sizes and shapes …
Accuracy in spreadsheet modelling systems can be reduced due to difficulties with the inputs, the model itself, or the spreadsheet implementation of the model. When the "true" outputs from the system are unknowable, accuracy is evaluated subjectively. Less than perfect accuracy can be acceptable depending on the purpose of the model, problems with inputs, or resource constraints. Users build modelling systems iteratively, and choose to allocate limited resources to the inputs, the model, the spreadsheet implementation, and to employing the system for business analysis. When making these choice…
Existing approaches for detecting anomalies in spreadsheets can help to discover faults, but they are often applied too late in the spreadsheet lifecycle. By contrast, our approach detects anomalies immediately whenever users change their spreadsheets. This live inspection approach has been implemented as part of the Spreadsheet Inspection Framework, enabling the tool to visually report findings without disturbing the users' workflow. An advanced list representation allows users to keep track of the latest findings, prioritize open problems, and check progress on solving the issues. Results fr…
The quality of the data in spreadsheets is less discussed than the structural integrity of the formulas. Yet it is an area of great interest to the owners and users of the spreadsheet. This paper provides an overview of Information Quality (IQ) and Data Quality (DQ) with specific reference to how data is sourced, structured, and presented in spreadsheets.
This paper describes an exploratory empirical study of the effect of named ranges on spreadsheet debugging performance. Named ranges are advocated in both academia and industry, yet no experimental evidence has been cited to back up these recommendations. This paper describes an exploratory experiment involving 21 participants that assesses the performance of novices debugging a spreadsheet containing named ranges. The results are compared with the performance of a different set of novices debugging the same spreadsheet without named ranges. The findings suggest that novice users debug on aver…
A new MS Excel application has been developed which seeks to reduce the risks associated with the development, operation and auditing of Excel spreadsheets. FormulaDataSleuth provides a means of checking spreadsheet formulas and data as they are developed or used, enabling the users to identify actual or potential errors quickly and thereby halt their propagation. In this paper, we will describe, with examples, how the application works and how it can be applied to reduce the risks associated with Excel spreadsheets.
A spreadsheet usually starts as a simple and single-user software artifact, but, as frequent as in other software systems, quickly evolves into a complex system developed by many actors. Often, different users work on different aspects of the same spreadsheet: while a secretary may be only involved in adding plain data to the spreadsheet, an accountant may define new business rules, while an engineer may need to adapt the spreadsheet content so it can be used by other software systems. Unfortunately, spreadsheet systems do not offer modular mechanisms, and as a consequence, some of the previou…
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 a…
Spreadsheets are widely used in industry, because they are flexible and easy to use. Sometimes they are even used for business-critical applications. It is however difficult for spreadsheet users to correctly assess the quality of spreadsheets, especially with respect to their understandability. Understandability of spreadsheets is important, since spreadsheets often have a long lifespan, during which they are used by several users. In this paper, we establish a set of spreadsheet understandability metrics. We start by studying related work and interviewing 40 spreadsheet professionals to obta…
Spreadsheets are widely used in industry, because they are flexible and easy to use. Often, they are even used for business-critical applications. It is however difficult for spreadsheet users to correctly assess the maintainability of spreadsheets. Maintainability of spreadsheets is important, since spreadsheets often have a long lifespan, during which they are used by several users. In this paper, we present a checklist aimed at measuring the maintainability of a spreadsheet. This is achieved via asking several questions, and can indicate whether the spreadsheet is safe to use now and in the…
Modern ERP systems contain flexible report generators but the tendency exists for users to export data to spreadsheets for manipulation, reporting and decision making. A purported reason for this is that some users are more familiar with personal reporting tools (spreadsheets) as opposed to enterprise reporting tools. The author's doctoral research intends to measure the extent of spreadsheet usage in ERP environments and to determine which factors facilitate this.
Thanks to their flexibility and capability to perform different tasks and organize data in the best form and format, spreadsheets are widely used in different organizations and by different end users. Many business organizations rely on spreadsheets to fulfill their various tasks. On the other hand, the number of spreadsheets that contain errors are very high, thus researchers have developed different tools aimed at the prevention, detection, and correction of errors in spreadsheets. This research work is a comprehensive review that describes and classifies approaches on finding and fixing err…
This paper describes the rationale, curriculum and subject matter of a new MSc module being taught on an MSc Finance and Information Management course at the University of Wales Institute in Cardiff. Academic research on spreadsheet risks now has some penetration in academic literature and there is a growing body of knowledge on the subjects of spreadsheet error, human factors, spreadsheet engineering, "best practice", spreadsheet risk management and various techniques used to mitigate spreadsheet errors. This new MSc module in End User Computing Risk Management is an attempt to pull all of th…
Spreadsheets are widely used by knowledge workers, especially in the industrial sector. Their methodology enables a well understood, easy and fast possibility to enter data. As filling out a spreadsheet is more accessible to common knowledge workers than defining RDF statements, in this paper, we propose an easy-to-use, zero-configuration, web-based spreadsheet editor that simultaneously transfers spreadsheet entries into RDF statements. It enables various kinds of users to easily create semantic data whether they are RDF experts or novices. The typical scenario we address focuses on creating …
Spreadsheet users are often unaware of the risks imposed by poorly designed spreadsheets. One way to assess spreadsheet quality is to detect smells which attempt to identify parts of spreadsheets that are hard to comprehend or maintain and which are more likely to be the root source of bugs. Unfortunately, current spreadsheet smell detection techniques suffer from a number of drawbacks that lead to incorrect or redundant smell reports. For example, the same quality issue is often reported for every copy of a cell, which may overwhelm users. To deal with these issues, we propose to refine sprea…
Spreadsheets are the go-to tool for computerized calculation and modelling, but are hard to comprehend and adapt after reaching a certain complexity. In general, cognition of complex systems is facilitated by having a higher order mental model of the system in question to work with. We therefore present a concept for structure-aware understanding of and interaction with spreadsheets that extends previous work on structure inference in the domain. Following this concept, structural information is used to enrich visualizations, reactively enhance traditional user actions, and provide tools to pr…
We believe the error prone nature of traditional spreadsheets is due to their low level of abstraction. End user programmers are forced to construct their data models from low level cells which we define as "a data container or manipulator linked by user-intent to model their world and positioned to reflect its structure". Spreadsheet cells are limited in what they may contain (scalar values) and the links between them are inherently hidden. This paper proposes a method of raising the level of abstraction of spreadsheets by "redrawing the boundary" of the cell. To expose the hidden linkage str…
Spreadsheets are a ubiquitous software tool, used for a wide variety of tasks such as financial modelling, statistical analysis and inventory management. Extracting meaningful information from such data can be a difficult task, especially for novice users unfamiliar with the advanced data processing features of many spreadsheet applications. We believe that through the use of Natural Language Processing (NLP) techniques this task can be made considerably easier. This paper introduces NLP-SIR, a Natural language interface for spreadsheet information retrieval. The results of a recent evaluation…
Spreadsheets are the most popular end-user programming software, where formulae act like programs and also have smells. One well recognized common smell of spreadsheet formulae is nest-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. However, end users usually lack essential programming language knowledge and skills to tackle or even realize the problem. The previous research work has made very initial attempts in this aspect, while no effective and automated approach is currently available. This paper firstly propos…
Although experts diverge on how best to improve spreadsheet quality, it is generally agreed that more time needs to be spent testing spreadsheets. Ideally, experienced and trained spreadsheet engineers would carry this out, but quite often this is neither practical nor possible. Many spreadsheets are a legacy, developed by staff that have since moved on, or indeed modified by many staff no longer employed by the organisation. When such spreadsheets fall into the hands of inexperienced, non-experts, any features that reduce error visibility may become a risk. Range names are one such feature, a…
Practitioners often argue that range names make spreadsheets easier to understand and use, akin to the role of good variable names in traditional programming languages, yet there is no supporting scientific evidence. The authors previously published experiments that disproved this theory in relation to debugging, and now turn their focus to development. This paper presents the results of two iterations of a new experiment, which measure the effect of range names on the correctness of, and the time it takes to develop, simple summation formulas. Our findings, supported by statistically signific…
Modern spreadsheet systems can be used to implement complex spreadsheet applications including data sheets, customized user forms and executable procedures written in a scripting language. These applications are often developed by practitioners that do not follow any software engineering practice and do not produce any design documentation. Thus, spreadsheet applications may be very difficult to be maintained or restructured. In this position paper we present in a nutshell two reverse engineering techniques and a tool that we are currently realizing for the abstraction of conceptual data model…
A number of automated techniques and tools were proposed in the research literature over the years which aim to support the spreadsheet developer in the process of testing and debugging a faulty spreadsheet. One underlying assumption of many of these approaches is that the spreadsheet developer is capable of providing test cases or is at least reliably able to determine whether a calculated value in a certain cell is correct given the current set of inputs. Since real-world spreadsheets can be complex, we argue that these assumptions might be too strong in some situations. We therefore propose…
Heavy use of spreadsheets by organisations bears many potential risks such as errors, ambiguity, data loss, duplication, and fraud. In this paper these risks are briefly outlined along with their available mitigation methods such as: documentation, centralisation, auditing and user training. However, because of the large quantities of spreadsheets used in organisations, applying these methods on all spreadsheets is impossible. This fact is considered as a deficiency in these methods, a gap which is addressed in this paper. In this paper a new software tool for managing spreadsheets and identif…
Just like other software, spreadsheets can contain significant faults. Static analysis is an accepted and well-established technique in software engineering known for its capability to discover faults. In recent years, a growing number of tool vendors started offering tools that allow casual end-users to run various static analyses on spreadsheets as well. We supervised a study where three undergraduate software engineering students examined a selection of 14 spreadsheet auditing tools, trying to give a concrete recommendation for an industry partner. Reflecting on the study's results, we foun…
Spreadsheets are software programs which are typically created by end-users and often used for business-critical tasks. Many studies indicate that errors in spreadsheets are very common. Thus, a number of vendors offer auditing tools which promise to detect errors by checking spreadsheets against so-called Best Practices such as "Don't put constants in fomulae". Unfortunately, it is largely unknown which Best Practices have which actual effects on which spreadsheet quality aspects in which settings. We have conducted a controlled experiment with 42 subjects to investigate the question whether …
A demonstration in MS Excel to show how users can connect their spreadsheet models to the external environment that the model represents. We employ indexes to generate a list of relevant evidence that is hyperlinked to the context in which the evidence is discussed. The hyperlinks between the index and the contextual discussion have their own specific presentational identity. We contend that these presentational differences aid the integrity and understanding of complex models. Where models are complex, separate individual results can lead to contradictory conclusions. The demonstration includ…
A spreadsheet is remarkably flexible in representing various forms of structured data, but the individual cells have no knowledge of the larger structures of which they may form a part. This can hamper comprehension and increase formula replication, increasing the risk of error on both scores. We explore a novel data model (called the "lish") that could form an alternative to the traditional grid in a spreadsheet-like environment. Its aim is to capture some of these higher structures while preserving the simplicity that makes a spreadsheet so attractive. It is based on cells organised into nes…
Spreadsheet formula prediction has been an important program synthesis problem with many real-world applications. Previous works typically utilize input-output examples as the specification for spreadsheet formula synthesis, where each input-output pair simulates a separate row in the spreadsheet. However, this formulation does not fully capture the rich context in real-world spreadsheets. First, spreadsheet data entries are organized as tables, thus rows and columns are not necessarily independent from each other. In addition, many spreadsheet tables include headers, which provide high-level …
In the insurance industry, spreadsheets have emerged as an invaluable tool to for product pricing, because it is relatively straightforward to create and maintain complex pricing models using Excel. In fact, Excel is often preferred to "hard-code" whenever there are frequent changes to the calculations and business logic which under-pin the pricing of an insurance product. However, problems arise as soon as spreadsheets are deployed to end-users: version control, security of intellectual property, and ensuring correct usage are obvious issues; frequently, integration with other systems is also…
Spreadsheets are widely used for table manipulation and presentation. Stylistic formatting of these tables is an important property for both presentation and analysis. As a result, popular spreadsheet software, such as Excel, supports automatically formatting tables based on rules. Unfortunately, writing such formatting rules can be challenging for users as it requires knowledge of the underlying rule language and data logic. We present CORNET, a system that tackles the novel problem of automatically learning such formatting rules from user examples in the form of formatted cells. CORNET takes…
SheetCopilot: Bringing Software Productivity to the Next Level through Large Language Models
School of Artificial Intelligence, University of Chinese Academy of Sciences (UCAS)（中国科学院大学人工智能学院）；State Key Laboratory of Multimodal Artificial Intelligence Systems (MAIS), Institute of Automation, Chinese Academy of Sciences（中国科学院自动化研究所多模态人工智能系统全国重点实验室）；Center for Artificial Intelligence and Robotics, HKISI, CAS（中国科学院香港创新研究院人工智能与机器人创新中心）；The Hong Kong Polytechnic University（香港理工大学）；Shanghai Artificial Intelligence Laboratory（上海人工智能实验室）
Sprego is a programming tool for novice and end-user programmers within graphical spreadsheet environments. The main idea of Sprego is to use as few general purpose functions as possible, and based on these functions we create multilevel formulas to solve real world programmable spreadsheet problems. Beyond providing the framework for the theoretic background and the tools which support Sprego, in order to demonstrate the power which lies within it, we present a converted authentic table and, based on this table, data retrieval tasks, their algorithms, and coding in full details.
