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.

Code Smells

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.

Spreadsheet Smells

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?

_______________

You may also like...

4 Responses

  1. Robert Ryan says:

    I had not heard of spreadsheet “smell” before, but I’ve certainly been long aware of the KISS (Keep It Simple, Silly) method, which I attempt to apply to all areas of my life. I think there is also a gray area here. There are times I use INDEX / MATCH when VLOOKUP would suffice because INDEX / MATCH is so powerful and flexible and I want to keep somewhat familiar with it.

  2. Jon says:

    It would be interesting to see what my worksheets look like after having them tested.

    I don’t think I quite get the referencing other worksheets too much though. If your data is on one worksheet and then on the staging area is on another, wouldn’t one need to reference the raw data quite a bit? I don’t have a ton of experience in this area so I haven’t seen others spreadsheets too much.

    This is how I structured my time card:

    Data (multiple worksheets) Settings
    —————————- ———–
    | | | |
    Staging Pivot Tables Visual Tables Staging
    | |
    ————-
    |
    Dashboard

    I wonder if that is a good layout?

    As for Excel not being able to show the values as the data is being worked on. I wonder if that is the time to start using VBA and creating forms so you can manipulate your data more easily from a central location. I’m sure it depends on what the person is doing.

  3. Jon says:

    Let me try the diagram once more.

    Data (multiple worksheets)__________________________Settings
    ---------------------------------___________________|
    |____________|__________________|___________________|
    Staging______Pivot Tables_______Visual Tables_______Staging
    |____________|
    --------------
    |
    Dashboard
  4. Roger Fisher says:

    Debra,

    I was not familiar with the term “smells”, but I’ve read some long code that could have been much simpler.
    Thank you for the post!

    Roger

Leave a Reply

Your email address will not be published. Required fields are marked *