Your Excel Spreadsheet Smells
Do your spreadsheets smell? This week, a tweet from Felienne Hermans caught my eye.
- "Our @icse2012 paper on spreadsheet smells already has a citation before publication"
Spreadsheet smells? I've seen some stinky spreadsheets, but have never read a conference paper on spreadsheet smells. It sounded intriguing, so I followed the link to Felienne's paper – Detecting and Visualizing Inter-worksheet Smells in Spreadsheets.
The starting point for the paper is the code smell metaphor introduced in Martin Fowler's book, Refactoring: Improving the Design of Existing Code. I don't have that book, so I visited Wikipedia, to see what it knew about code smells.
Fortunately, Wikipedia had a helpful summary of common code smells, and I've listed a few of them below. Can you see how these code smells relate to Excel, whether you're building worksheets, or creating Excel VBA code?
- Duplicated code: identical or very similar code exists in more than one location.
- Long method: a method, function, or procedure that has grown too large.
- Contrived complexity: forced usage of overly complicated design patterns where simpler design would suffice.
- Excessive use of literals: these should be coded as named constants, to improve readability and to avoid programming errors.
Hmmm…replace "code" with formulas, and you've probably seen (or created) workbooks that had those code smells. I've been guilty of creating some of those smells, and have seen workbooks start small, and slowly grow out of control.
Among the most frequent spreadsheet smells that Felienne and her colleagues found were:
- Inappropriate Intimacy – a worksheet that is overly related to a second worksheet.
- Feature Envy – if there is a formula that is more interested in cells from another worksheet, it would be better to move the formula to that worksheet
- Shotgun Surgery – a formula F that is referred to by many different formulas in different worksheets…chances are high that many of the formulas that refer to F will have to be changed if F is changed.
Read More About It
If you'd like to learn more about spreadsheet code smells, take a look at the Spreadsheet Smells paper written by Felienne and her colleagues, to see how their research was done, and what their conclusions were. You can also read other papers that Felienne has written on this topic, if you'd like to learn more: Felienne Hermans Publications
Have you read anything similar, or heard about code smells before?