The Seven Deadly Spreadsheet 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.