Please cite this article in press as: Jannach, D., et al., Avoiding, finding and fixing spreadsheet errors – A survey of automated approaches for spreadsheet QA. J. Syst. Software (2014), http://dx.doi.org/10.1016/j.jss.2014.03.058 ARTICLE IN PRESS G Model JSS-9311; No. of Pages 22 The Journal of Systems and Software xxx (2014) xxx–xxx Contents lists available at ScienceDirect The Journal of Systems and Software j ourna l ho mepage: www.elsevier.com/locate/jss Avoiding, finding and fixing spreadsheet errors – A survey of automated approaches for spreadsheet QA Dietmar Jannach a,∗ , Thomas Schmitz a , Birgit Hofer b , Franz Wotawa b a TU Dortmund, Germany b TU Graz, Austria a r t i c l e i n f o Article history: Received 20 November 2013 Received in revised form 25 February 2014 Accepted 18 March 2014 Available online xxx Keywords: Spreadsheet Quality assurance Tool support a b s t r a c t Spreadsheet programs can be found everywhere in organizations and they are used for a variety of pur- poses, including financial calculations, planning, data aggregation and decision making tasks. A number of research surveys have however shown that such programs are particularly prone to errors. Some rea- sons for the error-proneness of spreadsheets are that spreadsheets are developed by end users and that standard software quality assurance processes are mostly not applied. Correspondingly, during the last two decades, researchers have proposed a number of techniques and automated tools aimed at sup- porting the end user in the development of error-free spreadsheets. In this paper, we provide a review of the research literature and develop a classification of automated spreadsheet quality assurance (QA) approaches, which range from spreadsheet visualization, static analysis and quality reports, over test- ing and support to model-based spreadsheet development. Based on this review, we outline possible opportunities for future work in the area of automated spreadsheet QA. © 2014 Elsevier Inc. All rights reserved. 1. Introduction Spreadsheet applications, based, e.g., on the widespread Microsoft Excel software tool, can nowadays be found almost everywhere and at all levels of organizations (Panko and Port, 2012). These interactive computer applications are often developed by non-programmers – that is, domain or subject matter experts – for a number of different purposes including financial calculations, planning and forecasting, or various other data aggregation and decision making tasks. Spreadsheet systems became popular during the 1980s and represent the most successful example of the End-User Program- ming paradigm. Their main advantage can be seen in the fact that they allow domain experts to build their own supporting software tools, which directly encode their domain expertise. Such tools are usually faster available than other business applications, which have to be developed or obtained via corporate IT departments and are subject to a company’s standard quality assurance (QA) processes. Very soon, however, it became obvious that spreadsheets – like any other type of software – are prone to errors, see, e.g., the early ∗ Corresponding author at: TU Dortmund, 44221 Dortmund, Germany. Tel.: +49 231 755 7272; fax: +49 231 755 7269. E-mail address: dietmar.jannach@tu-dortmund.de (D. Jannach). paper by Creeth (1985) or the report by Ditlea (1987), which were published in 1985 and 1987, respectively. More recent surveys on error rates report that in many studies on spreadsheet errors at least one fault was found in every single spreadsheet that was ana- lyzed (Panko, 1998). Since in reality even high-impact business decisions are made, which are at least partially based on faulty spreadsheets, such errors can represent a considerable risk to an organization 1 . Overall, empowering end users to build their own tools has some advantages, e.g., with respect to flexibility, but also intro- duces additional risks, which is why Panko and Port call them both “dark matter (and energy) of corporate IT” (Panko and Port, 2012). In order to minimize these risks, researchers in different disci- plines have proposed a number of approaches to avoid, detect or fix errors in spreadsheet applications. In principle, several approaches are possible to achieve this goal, beginning with better education and training of the users, over organizational and process-related measures such as mandatory reviews and audits, to better tool sup- port for the users during the spreadsheet development process. In this paper, we focus on this last type of approaches, in which the spreadsheet developer is provided with additional software tools 1 See http://www.eusprig.org/horror-stories.htm for a list of real-world stories or the recent article by Herndon et al. (2013) who found critical spreadsheet formula errors in the often-cited economic analysis of Reinhart and Rogoff (2010). http://dx.doi.org/10.1016/j.jss.2014.03.058 0164-1212/© 2014 Elsevier Inc. All rights reserved.