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
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
