Detecting and refactoring code smells in spreadsheet formulas

FFJ Hermans, M Pinzger, A van Deursen

Research output: Contribution to journalArticleScientificpeer-review

44 Citations (Scopus)
4 Downloads (Pure)


Spreadsheets are used extensively in business processes around the world and just like software, spreadsheets are changed throughout their lifetime causing understandability and 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; a visualization technique to highlight these formulas in spreadsheets and a method to automatically suggest refactorings to resolve smells. We implemented the metrics, visualization and refactoring suggestions techniques in a prototype tool and evaluated our approach in three studies. 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. Finally, we generate refactoring suggestions for those ten spreadsheets and study the implications. The results of these evaluations indicate that formula smells are common, that they can reveal real errors and weaknesses in spreadsheet formulas and that in simple cases they can be refactored.
Original languageEnglish
Pages (from-to)549-575
Number of pages27
JournalEmpirical Software Engineering
Issue number2
Early online date2014
Publication statusPublished - Apr 2015

Bibliographical note

Published online 7-2-2014


Dive into the research topics of 'Detecting and refactoring code smells in spreadsheet formulas'. Together they form a unique fingerprint.

Cite this