-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 from a first user study indicate that users find the approach useful.
Faults in spreadsheets are common and can cause severe
damage [
Existing spreadsheet anomaly detection tools still suffer from a major drawback: Since their scans have to be triggered manually, it cannot be guaranteed that users execute them regularly—if at all. The more actions users take and the more time passes between scans, the more difficult it gets for users to identify the actions responsible for the reported findings. Also, the spreadsheet could contain findings already created by previous users, and distinguishing between new and old findings puts an additional mental load onto users. At first glance, checking spreadsheets for anomalies automatically in the background seems to be the trivial solution to address the described issues. In fact, recent versions of Microsoft Excel already issue live inspection techniques by providing warning icons for built-in error types and report them as warning diamonds next to the affected cells (Figure 1). However, adopting a similar approach for more complex anomalies involves several challenges. In the rest of this position paper, we describe these challenges, our attempts to overcome them, and first experiences with the resulting solution.
We identified the following challenges for a live inspection approach of spreadsheets: Ch1: Avoid disruption: Users primarily want to work with their spreadsheet and not study findings. Thus, users must be notified in a low-disrubtive manner.
Ch2: Support workflow: Users should be free to choose when to deal with findings and supported in their workflow addressing the findings.
Ch3: Motivation: Users should be motivated to deal with reported findings.
Ch4: Recent first: Findings caused by recent user actions should be reported in a more prominent fashion than older findings.
Ch5: Provide overview: Users need an overview of all currently open findings and support to group them.
In previous work [
The live inspection mechanism borrows ideas from the task
metaphor that many e-mail clients employ to empower users to
process and categorize incoming e-mails [
To adopt the metaphor, we divided the formerly flat list (Ch5) of findings (anomalies) into four categories represented by the tabs shown in Figure 2. They are named, from left to right: ‘Open’, ‘Later’ (postponed), ‘Ignored’ and ‘Archive’ (solved). Figure 3 illustrates the states between which findings can travel.
1http://spreadsheet-inspection-framework.github.io
A newly detected finding is added to the list of findings as ’Open’. Open findings have a state of being ‘read’ or ‘unread’. The eye-catching red bubble next to the ‘Open’ section shows the number of unread findings (Ch1 Ch3, Ch5). Recent findings are placed on top (Ch4). The tabs ‘Later’ and ‘Ignore’ do not employ this notification mechanism because findings become part of these categories only when users explicitly move them there (Ch2)—by definition, all findings of these categories are read.
Findings that were raised but are meanwhile solved move to the ‘Archive’ without triggering a notification. The archive is a neutral category. In a preliminary version we experimented with a reward mechanism that highlighted solved findings and counted them using a green notification bubble (Ch3), but since many findings can be ‘solved’ by simply deleting the causative cells, this counter could introduce false incentives.
On the technical side, we implemented a diff mechanism which allows us to distinguish new from existing findings. Additionally, we set a trigger that automatically issues a scan whenever a user’s action triggers a recalculation of the spreadsheet. An extended preferences dialog allows users to choose which inspection rules shall be included in the automatic scan on a per-spreadsheet basis.
We did a first user study with one pilot followed by five participants (male engineering students aged between 18 and 24). The participants had to solve two tasks in a given spreadsheet that required extending and changing the business logic of its formulas. The spreadsheet was designed in such a way that findings were likely to be caused.
Three of the participants used the in-spreadsheet marker icons and directly tried to solve open issues as they appeared, but skipped findings they did not find trivial in the first place. They kept the inspection pane closed and did not open it until they finished their primary tasks. Then, they used the inspection pane to solve the remaining findings. The forth participant did not pay attention at all to the findings until he finished the primary task. In contrast, the fifth participant kept the inspection pane open all the time and paid more attention to the findings than to the actual task.
The first three subjects achieved a rapid learning effect: Once they understood that constants in formulas lead to findings, they changed their behavior and did not put constants into the next formulas they created. Overall, the participants rated the live inspection mechanism to be low distracting and generally acceptable.
Future work should provide an extended reward mechanism to motivate the users to solve open findings. When processing large spreadsheets or checking for more complex anomalies, users should be able to continue working while the scans are still running. Also, checks need to be executed incrementally and in order of priority, comparable to selecting and prioritizing regression tests in software engineering.