Spreadsheet Auditing Software

It is now widely accepted that errors in spreadsheets are both common and potentially dangerous. Further research has taken place to investigate how frequently these errors occur, what impact they have, how the risk of spreadsheet errors can be reduced by following spreadsheet design guidelines and methodologies, and how effective auditing of a spreadsheet is in the detection of these errors. However, little research exists to establish the usefulness of software tools in the auditing of spreadsheets. This paper documents and tests office software tools designed to assist in the audit of spreadsheets. The test was designed to identify the success of software tools in detecting different types of errors, to identify how the software tools assist the auditor and to determine the usefulness of the tools.Comment: 14 Pages, 5 Figures. All the comparisons are now out of date however all the auditing software studied is still available for further historical and comparative analysis. Minor Edits and referencing of the original paper by GJC in Jan 2010. In memory of Mike O' Har

A Spreadsheet Auditing Tool Evaluated in an Industrial Context

Amongst the large number of write-and-throw-away spreadsheets developed for one-time use there is a rather neglected proportion of spreadsheets that are huge, periodically used, and submitted to regular update-cycles like any conventionally evolving valuable legacy application software. However, due to the very nature of spreadsheets, their evolution is particularly tricky and therefore error-prone. In our strive to develop tools and methodologies to improve spreadsheet quality, we analysed consolidation spreadsheets of an internationally operating company for the errors they contain. The paper presents the results of the field audit, involving 78 spreadsheets with 60,446 non-empty cells. As a by-product, the study performed was also to validate our analysis tools in an industrial context. The evaluated auditing tool offers the auditor a new view on the formula structure of the spreadsheet by grouping similar formulas into equivalence classes. Our auditing approach defines three similarity criteria between formulae, namely copy, logical and structural equivalence. To improve the visualization of large spreadsheets, equivalences and data dependencies are displayed in separated windows that are interlinked with the spreadsheet. The auditing approach helps to find irregularities in the geometrical pattern of similar formulas.Comment: 12 Pages, 2 Figures, 4 Table

Investigating Effects of Common Spreadsheet Design Practices on Correctness and Maintainability

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 observance of three commonly suggested Best Practices is correlated with desired positive effects regarding correctness and maintainability: "Do not put constants in formulae", "keep formula complexity low" and "refer to the left and above". The experiment was carried out in two phases which covered the creation of new and the modification of existing spreadsheets. It was evaluated using a novel construction kit for spreadsheet auditing tools called Spreadsheet Inspection Framework. The experiment produced a small sample of directly comparable spreadsheets which all try to solve the same task. Our analysis of the obtained spreadsheets indicates that the correctness of "bottom-line" results is not affected by the observance of the three Best Practices. However, initially correct spreadsheets with high observance of these Best Practices tend to be the ones whose later modifications yield the most correct results.Comment: 16 Pages, 5 Colour Figures; Proc. European Spreadsheet Risks Int. Grp. (EuSpRIG) 2012, ISBN: 978-0-9569258-6-

Practical Challenges with Spreadsheet Auditing Tools

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 found that most of these tools do provide useful aids in finding problems in spreadsheets, but we have also spotted several areas where tools had significant issues. Some of these issues could be remedied if spreadsheet auditing tool vendors would pick up some ideas of static analysis tools for traditional software development and adopt some of their solution approaches.Comment: 13 Pages. 3 Detailed Colour Figures, Proc. European Spreadsheet Risks Int. Grp. (EuSpRIG) 2013, ISBN: 978-1-9054045-1-

Teaching Spreadsheets: Curriculum Design Principles

EuSpRIG concerns direct researchers to revisit spreadsheet education, taking into account error auditing tools, checklists, and good practices. This paper aims at elaborating principles to design a spreadsheet curriculum. It mainly focuses on two important issues. Firstly, it is necessary to establish the spreadsheet invariants to be taught, especially those concerning errors and good practices. Secondly, it is important to take into account the learners' ICT experience, and to encourage them to attitudes that foster self-learning. We suggest key principles for spreadsheet teaching, and we illustrate them with teaching guidelines.Comment: 12 Pages, 10 Figure

Excel 2013 Spreadsheet Inquire

Excel 2013 (version 15) includes an add-in "Inquire" for auditing spreadsheets. We describe the evolution of such tools in the third-party marketplace and assess the usefulness of Microsoft's own add-in in this context. We compare in detail the features of Inquire with similar products and make suggestions for how it could be enhanced. We offer a free helper add-in that in our opinion corrects one major shortcoming of Inquire.Comment: 22 Pages with 23 large and highly detailed colour figures, Proc. European Spreadsheet Risks Int. Grp. (EuSpRIG) 2013, ISBN: 978-1-9054045-1-

The Future of Spreadsheets in the Big Data Era

The humble spreadsheet is the most widely used data storage, manipulation and modelling tool. Its ubiquity over the past 30 years has seen its successful application in every area of life. Surprisingly the spreadsheet has remained fundamentally unchanged over the past three decades. As spreadsheet technology enters its 4th decade a number of drivers of change are beginning to impact upon the spreadsheet. The rise of Big Data, increased end-user computing and mobile computing will undoubtedly increasingly shape the evolution and use of spreadsheet technology. To explore the future of spreadsheet technology a workshop was convened with the aim of "bringing together academia and industry to examine the future direction of spreadsheet technology and the consequences for users". This paper records the views of the participants on the reasons for the success of the spreadsheet, the trends driving change and the likely directions of change for the spreadsheet. We then set out key directions for further research in the evolution and use of spreadsheets. Finally we look at the implications of these trends for the end users who after all are the reason for the remarkable success of the spreadsheet.Comment: 13 Pages, 1 Tabl

Audit and Change Analysis of Spreadsheets

Because spreadsheets have a large and growing importance in real-world work, their contents need to be controlled and validated. Generally spreadsheets have been difficult to verify, since data and executable information are stored together. Spreadsheet applications with multiple authors are especially difficult to verify, since controls over access are difficult to enforce. Facing similar problems, traditional software engineering has developed numerous tools and methodologies to control, verify and audit large applications with multiple developers. We present some tools we have developed to enable 1) the audit of selected, filtered, or all changes in a spreadsheet, that is, when a cell was changed, its original and new contents and who made the change, and 2) control of access to the spreadsheet file(s) so that auditing is trustworthy. Our tools apply to OpenOffice.org calc spreadsheets, which can generally be exchanged with Microsoft Excel.Comment: 10 pages, 3 figure

The Detection of Human Spreadsheet Errors by Humans versus Inspection (Auditing) Software

Previous spreadsheet inspection experiments have had human subjects look for seeded errors in spreadsheets. In this study, subjects attempted to find errors in human-developed spreadsheets to avoid the potential artifacts created by error seeding. Human subject success rates were compared to the successful rates for error-flagging by spreadsheet static analysis tools (SSATs) applied to the same spreadsheets. The human error detection results were comparable to those of studies using error seeding. However, Excel Error Check and Spreadsheet Professional were almost useless for correctly flagging natural (human) errors in this study.Comment: 14 Pages, 4 Figure

A Toolkit for Scalable Spreadsheet Visualization

This paper presents a toolkit for spreadsheet visualization based on logical areas, semantic classes and data modules. Logical areas, semantic classes and data modules are abstract representations of spreadsheet programs that are meant to reduce the auditing and comprehension effort, especially for large and regular spreadsheets. The toolkit is integrated as a plug-in in the Gnumeric spreadsheet system for Linux. It can process large, industry scale spreadsheet programs in reasonable time and is tightly integrated with its host spreadsheet system. Users can generate hierarchical and graph-based representations of their spreadsheets. This allows them to spot conceptual similarities in different regions of the spreadsheet, that would otherwise not fit on a screen. As it is assumed that the learning effort for effective use of such a tool should be kept low, we aim for intuitive handling of most of the tool's functions.Comment: 12 Page