The Seven Deadly Spreadsheet Sins

by Kien Leong

Excel Sins

Business has a love-hate relationship with spreadsheets.  Universal, familiar and essential to running a business.  They are also unsecure, error-prone and disconnected with business systems.

Depending on your point of view, spreadsheets are a users’ paradise or a necessary evil.  Excel worksheets are the daily work-horse to over 500 million users, yet many IT departments seem hell-bent on stamping out spreadsheets and moving users toward business intelligence, budgeting and ERP applications.

Here at Production-Scheduling.com, we are in the business of taming spreadsheets.  You might suspect that we always come down in favour of using spreadsheets for business applications.  The truth is that there is only ever one answer to the question:

Should we be using Excel to ………….. (insert your business function here)

The answer is: “It Depends!”

It depends on the functional requirement.  It depends on what else is available.  Most importantly, it depends how you do it.  Ella Fitzgerald once sang “It ain’t what you do, it’s the way that you do it”.  (I don’t admit to having heard of Fun Boy Three or Bananarama…).  Spreadsheets are a tool, and like any tool you need the right one for the job.  You need to use the right tool in the right way.

So, it might be useful to list the Seven Deadly Spreadsheet Sins, how to avoid temptation and be delivered from all evil.  In other words, if you are going to use spreadsheets for whatever business function, then here are the most important things to avoid.  On the flip-side, there emerges some best practices for Excel use in business.

1. Static data

Probably the number one complaint of spreadsheet use in business.  Every workbook has a different version of truth.  A profileration of “spreadmarts” across the organisation.  No-one has all the facts and to get hard evidence to support decisions, someone is tasked with trawling across Excel files and gathering  it manually.

Where does spreadsheet data come from?  In Spreadsheet-Hell, it has been manually punched or cut-pasted from another source.  If it was imported at all, that was long ago and the data are no longer up to date.

This problem is easily fixed:  Do not store data in a spreadsheet at all.  The best place to keep data is a database.  Instead of managing data in Excel, import data from a system.   USe an ODBC connection or a simple text file.  There are some simple techniques to do this here.  Good business planning and intelligence begins with the relationship between the data store and the analysis tool:  Separated, yet integrated.

Bringing data into Excel is generally easier than sending data back to a host system.   If there are some values that you wish to keep in speadsheet files and share with others, the best way is to update a database or export a table.  An ASCII text file export or data connection is a better way of sharing data than sending Excel workbooks back and forth.

2. Incomplete, inconsistent data

This is also a major bugbear.  Most spreadsheets are user-defined and tend to contain only the data that the user needs.  Or thinks they need.  The structure of the sheet follows the user’s habits and quirks.  People make decisions without full set of the facts.

Inconsistency can be ironed out by using a systematic worksheet structure.  Yes, it does depend on coordination and compliance, but then so does the solution to every business problem.  It certainly helps if the method makes sense to the user and helps them by saving time and effort.  Fix the next sin, and the biggest obstacle simply melts away.

3. Mixing data, calculation and reporting

This is the biggest, baddest sin.  And if you are guilty of it, you are probably unaware that you are doing anything wrong.  The problem is that people like to view data in a totally different way than a computer likes to process it.  This is usually some matrix layout that attempts to present data like a report.  If you have ever spent hours cut and pasting numbers from someone else’s spreadsheet, or built twisted formulas that reach across multiple sheets then you will have suffered at the hands of this problem.

Excel has PivotTables for a good reason.  It is much easier to render a table to a matrix than the other way around.  Bring data into a workbook in tables, calculate in tables and then present a report using matrices, graphs, charts, summarized tables or anything else that makes the data readable.  More on normalized tables versus matrices here.

4. Multiple live formula

Spreadsheets are error-prone because they have thousands of live formulas that are almost impossible to check.  So goes a common criticism.  Not only do multiple live formulas increase the probability of error, they can also make the workbook fat, unwieldy and slow.

The solution is to set each formula in one cell, paste it to every record and replace with values.  The Fast Excel Method always use row 8 for formulas, row 10 for table headings and row 11 and below for data.  Checking formulas is much easier, as they are in a single row and there is only one instance of each formula.  This technique drastically reduces the file size and speed.  It controls the calculation sequence and ensures that each result is stable.  The Development Template provides a nice way to automate this process.

5. Updating records with formats

A user wants to prioritize a list and highlights priority cells using fill formatting with colours.  Usually a bright yellow.  This provides a visual highlight, but it is impossible to use this in a calculation without resorting to very awkward VBA code.  It is much better to indicate priorities, families or attributes by updating a cell value.  By all means, use colours and formats to decorate reports if it genuinely aids readability.  Excel has conditional formatting to do this.  Like PivotTables, this is an asymetrical relationship:  it is much easier to format from a cell value then it is to update a cell value from a format.

6.  Invalid data

A database-driven system usually has definitions for data that restricts input to certain types of data, eg. dates, numbers, text strings.  A standard cell has formats for data types, but no restrictions on the data itself.  This can lead to validation problems, particularly with dates.  Excel’s data validation feature goes a little way to help, but it is not foolproof.  To demonstrate this, simply copy an unformatted cell and paste it on top of a validated cell and see what happens.

It is possible to provide more robust validation with forms or event handlers in VBA.  However, it is often more desirable to have data input in a system that is set up for this, either an ERP/MRP system or some other database-driven system.  Of course, having an ERP system doesn’t guarantee data validity.  Invalid data is a data management problem, not a spreadsheet problem.

7.  Facts embedded in formula

The cell shows a calculated value.  The formula is built from a mixture of number constants and cell references.  In order to change the inputs, you have to examine every cell and see where a constant has been used to calculate a result.  A long and painful process follows.  Do not build formulas with constants, unless they are universal; e.g. 60 minutes in an hour, 2.2lbs in a kilo.  Put formula inputs in a cell and then build the formula with cell references.  This will lead to a dynamic, accurate workbook and a happy set of users.

Excel heaven

So there you have the most common problems with spreadsheets and some simple solutions.  Follow these principles and you will be on your way to a virtuous life of fast, flexible and accurate Excel-based business applications.

For more details on Excel best practices and some development principles, see our section on Fast Excel Development Method.  The Development Template gets you using the right principles from the start.  Download for free or view tutorials on using them.

{ 14 comments… read them below or add one }

David September 1, 2010 at 7:21 am

Kien,

This is fantastic! I am very proud of the site, and look forward to sharing it with the world!

Regards
Dave

Mike September 14, 2010 at 2:47 am

How about merged cells? I want to sort and filter a sheet and someone has merged cells in the table. People who a table summary should use pivot tables.

Kien Leong September 14, 2010 at 5:41 am

yes, merged cells can be painful. It stems from a confusion on data vs. reporting. A pivot-table can be a good solution, although the sorting can be more restrictive than auto-filter in a table. Another idea is to replace merged cells with conditional formatting: each cell contains the value, but only the top one is visible as a heading. You can also fix the borders this way for dynamic formatting that places a border only around a range identical values in a table.

Jamie Bulloch June 23, 2011 at 11:08 am

Kein,

Many Thanks for giving me the clearest explanation of what ERP is and can do when built and managed with proper planning and rigour, knowing what and more importantly what NOT to do is invaluable, I will be using the template to understand and tailor my requirements, this will help me immensely,, Jamie

Kien Leong June 27, 2011 at 8:16 pm

Jamie,

I am glad you find it helpful. I hope you get going quickly with the development template. Let me know if you think I can be of any more assistance.

Seven wastes October 24, 2011 at 5:33 am

The seven method to protect Deadly temptation and I most agree in number 4 “Multiple live formula” this you can explain so clear. thank for share the great post.

Kien Leong October 24, 2011 at 11:30 pm

Thanks, Seven Wastes

I am happy you liked it. We have just done a survey and released a tool that ranks the number of live formula to the number of columns. I find this is a great indicator of how well an Excel tool has been designed. Find it here: http://production-scheduling.com/rate-your-planning-in-excel-spreadsheets/

gift sigauke October 31, 2011 at 7:15 am

if any 1 grasp the idea of deadly sins of spread sheet and repent,he/she will be the most super planner ever

Rebecca Mott October 31, 2011 at 7:28 am

Some great ideas here. I love the concept. But for we regular spreadsheet users, easier said than done!

Declan McTiernan October 31, 2011 at 7:58 am

Some great points of interest and guidelines in this article

Kelly Knecht October 31, 2011 at 8:46 am

Very insightful information. I’ve been guilty of all 7 excel sins. But now I can avoid them.

Thank you!

ifiok otung November 6, 2011 at 8:29 am

i think this whole website is awesome! I’m just starting out with production management and using spreadsheets and to have access to this wealth of wisdom is amazing.

I will share this around. Also I’m finding that I’m using a lot more of google spreadsheets which has the advantage of multiple user collaboration on the same document as well as pretty much live changes. Is this a tool that you’d recommend using?

Laki January 31, 2012 at 9:11 am

Hy, i want referencing in my paper on this article. I need year of publishing this article.

Kien Leong January 31, 2012 at 9:36 am

@Laki, this post was published 29/8/2010.

Thanks for the reference. I would be interested to see a copy of your paper if it is available when published.

{ 5 trackbacks }

Previous post:

Next post: