The use of spreadsheets in industry is widespread. Their outcomes are often used to substantiate critical business decisions. Unfortunately, spreadsheets are also error-prone. Consequently, companies risk making decisions based on inaccurate information, which eventually could lead to loss of money and reputation.
This dissertation investigates to what extent a visual language could support professional spreadsheet users in interacting with complex formulas. We divided our research into two phases. In the first phase, we try to understand better how spreadsheets are used in three ways... ... Read more

The use of spreadsheets in industry is widespread. It is known that spreadsheets have an average life span of five years, and during this life span, they are used on average by thirteen different persons. Consequently, spreadsheets need maintenance, and knowledge about the spreadsheet needs to be transferred from one user to another. To minimize the risk of introducing new errors, a thorough understanding of the spreadsheet's formulas is needed during maintenance and knowledge transfer tasks.

Research on the use of block-based languages has shown that they positively affect the comprehension of program code. We hypothesize that using a block-based representation of a spreadsheet formula will positively affect formula comprehension.

Hence, we extended XLBlocks, a block-based formula editor for spreadsheets, with the functionality to generate a block-based representation of an existing formula. We conduct a think-aloud study with twenty-one experienced spreadsheet users from industry and ask them to perform a set of spreadsheet comprehension tasks using XLBlocks. During an interview, we ask them, using the Cognitive Dimensions of Notations framework, to reflect on the use of XLBlocks.

We found that participants preferred to use the block-based representation of formulas when analyzing or explaining formulas or to implement non-trivial changes. Named function parameters and the absence of parentheses and commas make functions easier to understand. Furthermore, the visualization enables the user to separate smaller parts in the formula, which improves comprehension. Finally, the possibility to navigate from formula to formula makes it clear how formulas work together and improve the understanding of the spreadsheet as a whole. ... Read more

Spreadsheets are frequently used in industry to support critical business decisions. Unfortunately, they also suffer from error-proneness, which sometimes results in costly consequences. Experiments in the field of program education have shown that programmers tend to make fewer errors and can better focus on the logic of a program if they use a block-based language instead of a textual one. We hypothesize that a block-based formula editor could support spreadsheet users in a similar way. Therefore, we develop XLBlocks and conduct a think-aloud study with 13 experienced spreadsheet users from industry. Participants are asked to create and edit several formulas, using our block-based language. We then ask them to evaluate this editor using the Cognitive Dimensions of Notations framework. We found that for all dimensions the block-based formula editor received a better evaluation than the default text-based formula editor. ... Read more

The use of spreadsheets in industry is widespread and the information that they provide is often used for decisions. Research has shown that spreadsheets are error-prone, leading to the risk that decisions are made on incorrect information. Software Evolution is a well-researched topic and the results have proven to support developers in creating better software. Could this also be applied to spreadsheets? Unfortunately, the research on spreadsheet evolution is still limited. Therefore, the aim of this paper is to obtain a better understanding of how spreadsheets evolve over time and if the results of such a study provide similar benefits for spreadsheets as it does for source code. In this study, we cooperated with Alliander, a large energy network company in the Netherlands. We conducted two case studies on two different set of spreadsheets that both were already maintained for a period of three years. To have a better understanding of the spreadsheets itself and the context in which they evolved, we also interviewed the creators of the spreadsheets. We focus on the changes that are made over time in the formulas. Changes in these formulas change the behavior of the spreadsheet and could possibly introduce errors. To effectively analyze these changes we developed an algorithm that is able to detect and visualize these changes. Results indicate that studying the evolution of a spreadsheet helps to identify areas in the spreadsheet that are error-prone, likely to change or that could benefit from refactoring. Furthermore, by analyzing the frequency in which formulas are changed from version to version, it is possible to predict which formulas need to be changed when a new version of the spreadsheet is created. ... Read more

The use of spreadsheets in industry is widespread. Companies base decisions on information coming from spreadsheets. Unfortunately, spreadsheets are error-prone and this increases the risk that companies base their decisions on inaccurate information, which can lead to incorrect decisions and loss of money. In general, spreadsheet research is aimed to reduce the error-proneness of spreadsheets. Most research is concentrated on the use of formulas. However, there are other constructions in spreadsheets, like charts, pivot tables, and array formulas, that are also used to present decision support information to the user. There is almost no research about how these constructions are used. To improve spreadsheet quality it is important to understand how spreadsheets are used and to obtain a complete understanding, the use of charts, pivot tables, and array formulas should be included in research. In this paper, we analyze two popular spreadsheet corpora: Enron and EUSES on the use of the aforementioned constructions. ... Read more

Spreadsheets are widely used in industry. Spreadsheets also suffer from typical software engineering issues. Previous research shows that they contain code smells, lack documentation and tests, and have a long live span during which they are transferred multiple times among users. These transfers highlight the importance of spreadsheet comprehension. Therefore, in this paper, we analyze the effect of the organization of formulas on spreadsheet comprehension. To that end, we conduct a controlled experiment with 107 spreadsheet users, divided into two groups. One group receives a model where the formulas are organized such that all related components are grouped closely together, while the other group receives a model where the components are spread far and wide across the spreadsheet. All subjects perform the same set of comprehension tasks on their spreadsheet. The results indicate that the way formulas are located relative to each other in a spreadsheet, influences the performance of the subjects in their ability to comprehend and adapt the spreadsheet. Especially for the comprehension tasks, the subjects perform better on the model where the formulas were grouped closely together. For the adaptation tasks, we found that the length of the calculation chain influences the performance of the subjects more than the location of the formulas itself. ... Read more

Spreadsheets are a successful example of an end-user programming language, and the spreadsheet paradigm shares several characteristics like composition, selection, and repetition with programming languages. There are compelling reasons that spreadsheets are code. For most programming languages, developers are supported by powerful IDEs. However, spreadsheets are missing such an IDE. In our current work we are researching how spreadsheet users could be supported by an IDE for spreadsheets and what kind of functionality should be included? As a preliminary result of this research, we introduce in this showpiece: Polaris, an excel Add-in that provides users with context-aware navigation in spreadsheets. ... Read more

Spreadsheets can be considered to be the world's most successful end-user programming language. In fact, one could say spreadsheets are programs. This paper starts with a comparison of spreadsheets to software: spreadsheets are similar in terms of applications domains, expressive power and maintainability problems. We then reflect upon what makes spreadsheets successful: liveness, directness and an easy deployment environment seem contribute largely to their success. Being a programming language, several techniques from software engineering can be applied to spreadsheets. We present an overview of such research directions, including spreadsheet testing, reverse engineering, smell detection, clone detection and refactoring. Finally, open challenges and future plans for the domain of spreadsheet software engineering are presented. ... Read more

It is known that spreadsheets are error-prone. It is very diffcult for users to get an overview of the design of the spreadsheet, and this is causing errors. Furthermore users are not always aware of the best way to structure a spread- sheet and just start modeling. To address this we will build a visual language to develop spreadsheet models. This en- ables users to visualize the design of their spreadsheets. A spreadsheet generator will create the spreadsheet based on the specifications made with our visual language. During this process, best practices for structuring spreadsheets are automatically incorporated. There will be a bidirectional link between the model and the associated spreadsheet. ... Read more