Detecting code smells in spreadsheet formulas

Abstract

Abstract-Spreadsheets are used extensively in business processes around the world and just like software, spreadsheets are changed throughout their lifetime causing maintainability issues. This paper adapts known code smells to spreadsheet formulas. To that end we present a list of metrics by which we can detect smelly formulas and a visualization technique to highlight these formulas in spreadsheets. We implemented the metrics and visualization technique in a prototype tool to evaluate our approach in two ways. Firstly, we analyze the Euses spreadsheet corpus, to study the occurrence of the formula smells. Secondly, we analyze ten real life spreadsheets, and interview the spreadsheet owners about the identified smells. The results of these evaluations indicate that formula smells are common and that they can reveal real errors and weaknesses in spreadsheet formulas.

Is spreadsheet ambiguity harmful? detecting and repairing spreadsheet smells due to ambiguous computation

“…Hermans et al proposed visualizing spreadsheets by dataflow graphs [17], and detected inter-worksheet smells in these graphs [18]. They proposed detecting smells from data clones [20] and in spreadsheet formulas [19]. In these pieces of work, Hermans et al's [19] and Smellsheet Detective [10] focus on syntactic faults, while our work focus on missing formula and inconsistent formula smells, which concern semantic faults.…”

“…They proposed detecting smells from data clones [20] and in spreadsheet formulas [19]. In these pieces of work, Hermans et al's [19] and Smellsheet Detective [10] focus on syntactic faults, while our work focus on missing formula and inconsistent formula smells, which concern semantic faults. Our work also detects conformance errors caused by ambiguous computation smells.…”

BumbleBee: a refactoring environment for spreadsheet formulas

“…In previous work, we have seen that end-users understand smells in spreadsheets [1,13] and that they prefer refactored versions of formulas [2]. This lays ground for a more research on spreadsheet formula refactoring.…”

“…Now that we have defined the transformation language, we use it to describe refactorings from our previous work, showing that BumbleBee is able to express them [1,2]. Note that the language as we have currently defined it only supports intra-formula refactorings.…”

Automating the formalization of product comparison matrices

“…Considerable research effort has been devoted to the study of spreadsheets [19,35]. All studies have the same observation: errors in spreadsheet are common but non trivial [2,12,[20][21][22][23]. Automated techniques have been developed for locating errors; guidelines on how to create well-structured and maintainable spreadsheets have been established, etc.…”