i-nth logo

Authors

Felienne Hermans & Danny Dig

Abstract

Spreadsheets are widely used in industry. It is estimated that end-user programmers outnumber regular programmers by a factor of 5. However, spreadsheets are error-prone: several reports exist of companies which have lost money because of spreadsheet errors. We assert that a contributing factor to these problems is the difficulty of consistent editing of spreadsheet formulas.

In this paper, we observe the occurrence of copy-equivalent regions in spreadsheets, non-connected regions with similar formulas within one spreadsheet. These regions occur frequently in practice. Therefore, we design a strategy to consistently transform them, by presenting a grammar with which formula transformations can be described. We implemented these transformations in our tool BumbleBee, which is an Excel add-in that consistently applies transformations to spreadsheet formulas.

To evaluate the usefulness of our approach, we perform an evaluation that shows that 1) our transformation tool is necessary, because a vast majority of spreadsheets with formulas (over 70%) contain similar formulas in non-connected regions, 2) the BumbleBee grammar is expressive, as it can be used to express all refactorings in previous work on spreadsheet formula refactoring, as well as all migrations to update formulas to Excel 2010, and 3) that spreadsheet users perform changes to spreadsheet formulas more efficient using BumbleBee.

Sample

BumbleBee example
BumbleBee example

Refactoring example, applying the 'replace awkward formula' transformation to change formulae like =B3+C3+D3 into formulae like =SUM(B3:D3).

Publication

2013, Technical report

Full article

BumbleBee: A transformation environment for spreadsheet formulas

Also see

BumbleBee, a tool for spreadsheet formula transformations