Christopher Chambers

Struggling to Excel: A Field Study of Challenges Faced by Spreadsheet Users

Spreadsheets have become one of the most widely-adopted software technologies. They have proven useful for performing numeric computations as well as for organizing, manipulating, exploring, and visualizing data. Yet only one aspect of spreadsheets, formulas, has received extensive attention in field studies to date. In this paper, we describe a three-part field study that widens this focus to uncover a broader range of challenges that people encounter when creating and using spreadsheets. This study has revealed several opportunities to improve spreadsheet editors, including developing different modes for spreadsheet creation, improving support for spreadsheet reuse, and helping users to find and use features.

Reasoning about spreadsheets with labels and dimensions

Labels in spreadsheets can be exploited for finding formula errors in two principally different ways. First, the spatial relationships between labels and other cells express simple constraints on the cells usage in formulas. Second, labels can be interpreted as units of measurements to provide semantic information about the data being combined in formulas, which results in different kinds of constraints. In this paper we demonstrate how both approaches can be combined into an integrated analysis, which is able to find significantly more errors in spreadsheets than each of the individual approaches. In particular, the integrated system is able to detect errors that cannot be found by either of the individual approaches alone, which shows that the integrated system provides an added value beyond the mere combination of its parts. We also compare the effectiveness of this combined approach with several other conceivable combinations of the involved components and identify a system that seems most effective to find spreadsheet formula errors based on label and unit-of-measurement information.

SheetDiff: A Tool for Identifying Changes in Spreadsheets

Most spreadsheets, like other software, change over time. A frequently occurring scenario is the repeated reuse and adaptation of spreadsheets from one project to another. If several versions of one spreadsheet for grading/ budgeting/etc. have accumulated, it is often not obvious which one to choose for the next project. In situations like these, an understanding of how two versions of a spreadsheet differ is crucial to make an informed choice. Other scenarios are the reconciliation of two spreadsheets created by different users, generalizing different spreadsheets into a common template, or simply understanding and documenting the evolution of a spreadsheet over time. In this paper we present a method for identifying the changes between two spreadsheets with the explicit goal of presenting them to users in a concise form. We have implemented a prototype system, called SheetDiff, and tested the approach on several different spreadsheet pairs. As our evaluations will show, this system works reliably in practice. Moreover, we have compared SheetDiff to similar systems that are commercially available. An important difference is that while all these other tools distribute the change representation over two spreadsheets, our system displays all changes in the context of one spreadsheet, which results in a more compact representation.

Dimension inference in spreadsheets

We present a reasoning system for inferring dimension information in spreadsheets. This system can be used to check the consistency of spreadsheet formulas and can be employed to detect errors in spreadsheets. We have prototypically implemented the system as an add-in to Excel. In an evaluation of this implementation we were able to detect dimension errors in almost 50% of the investigated spreadsheets, which shows (i) that the system works reliably in practice and (ii) that dimension information can be well exploited to uncover errors in spreadsheets.

Planted-model evaluation of algorithms for identifying differences between spreadsheets

Users often need to test, debug or reuse spreadsheets. We present a new algorithm that can identify differences between two spreadsheets, providing a basis for future tools to help users compare two versions of a spreadsheet (thereby seeing what is new and needs testing) or two different spreadsheets (thereby seeing which is more appropriate for reuse in a situation). This algorithm, RowColAlign, is a two-dimensional generalization of the classic dynamic programming algorithm for solving the one-dimensional longest common subsequence problem. In addition, we present a new planted model for generating test cases to evaluate this algorithm and others like it, including the greedy SheetDiff algorithm presented in prior work. In our evaluation, our new RowColAlign algorithm made no errors at all on test cases, including test cases comparable to relatively large spreadsheets. Moreover, further analysis revealed that it is unexpected for our new algorithm to make errors except when spreadsheets contain an unrealistically small number of distinct values. These results are extremely encouraging, revealing our algorithms potential as the basis for future spreadsheet tools.

Combining spatial and semantic label analysis

In this paper we demonstrate how these two approaches can be combined. We have formalized a combined reasoning system and have implemented a corresponding prototype system. We have evaluated the system on the EUSES spreadsheet corpus. The evaluation has demonstrated that adding a syntactic, spatial analysis to a dimension inference can significantly improve the rate of detected errors.

A Code-Centric Cluster-Based Approach for Searching Online Support Forums for Programmers

Online forums provide peer-to-peer technical support for many user populations, including programmers struggling to master a new language. Programmers can help one another by uploading code samples to such a forum. Unfortunately, finding relevant code samples can prove difficult using existing search engines for large, diverse forums. Therefore, we have prototyped a new kind of code search engine for online forums that draws upon unsupervised machine learning in two ways. First, it displays code samples in visual groupings based on the mutual similarity of code samples. Second, it uses the assignment of code samples to clusters to achieve a form of query expansion, thereby identifying additional search results as potentially useful. We evaluated the system by running it on the forum for the LabVIEW programming language. A textual analysis of posts showed that the unsupervised machine learning algorithm successfully tended to assign code samples to clusters based on topical similarity. An empirical user evaluation confirmed that the new search engine improved on the forums existing search engine by providing results for more queries, by generating more results per query, and by providing more relevant search results.

Helping end users find and fix performance issues in visual dataflow code

In this paper, the author discusses three techniques that will help end users find and fix performance problems in dataflow code. All of these techniques adapt the established concept of a “bad smell,” which is a heuristic for finding sections of code that function correctly but that have poor maintainability. This concept is adapted and applied in the context of visual dataflow languages for the novel purpose of helping them find and fix a broad range of performance problems. To test these techniques, we will create a prototype that applies them to LabVIEW. Using this prototype, we will conduct several user studies to evaluate how useful each technique is and how well they help end users to find and fix performance problems.