Small errors can lead to big mistakes when using spreadsheets – trust us. Actually, ask anyone and they’ll surely tell you that a spreadsheet has given them headaches, or even nightmares, at one point or another.
As you’ve probably experienced at least once before, even making the smallest of changes to the way you format data in a spreadsheet can have the biggest impact on the results you’re searching for or are looking to create.
The 11 most common spreadsheet errors
The use of multiple tables
This not only draws false associations between things for your computer, but it’s also more likely that you’ll use the same name field in multiple places. This means that it’ll be more difficult to clean up your data into a usable form and reduce the reliability of your analysis.
Using multiple tabs
By creating multiple tabs, you fail to allow the computer to see connections in the data that are there, meaning you’ll need to introduce spreadsheet application-specific functions to ensure this connection is seen and explicitly tell it how to combine tables. This increases the risk of accidentally adding inconsistencies to your data.
Leaving cells blank
Leaving a cell blank means that your computer won’t measure it and will instead interpret it as an unknown (or null) value.
Using problematic null values
It would be good here to create a new column like ‘data_missing’ to capture the different reasons why as opposed to entering notes within cells.
Using special characters in data
Line breaks, dashes, etc. are best left to a different column or field when setting up your spreadsheet as these can cause big issues when reading and interpreting your data.
Placing comments or units in cells
Even the best software can get confused if you use comments within your data cells or enter units or measurements in them. It’s best to add another column or field for notes and comments and use one column per different unit of measurement.
Entering more than one piece of information in a cell
Programs are limited in the ways they can read and analyze your data if you include more than one piece of information within a single cell. As mentioned above, you should best design your datasheet to include the type of information in a new column or cell, rather than lumping it all together.
Using problematic field names
Spaces are often misinterpreted by those that use whitespace as delimiters, some programs aren’t fans of field names that are text strings starting with numbers, and some platforms have their own preferred naming conventions.
Including your metadata in a data file
Bet you haven’t given this one any thought. Well, not many do. When it comes to the metadata (in the form of legends) of your spreadsheet, you shouldn’t include it in a data file as it could disrupt how computer programs see and interpret it.
Conveying information through formatting
Here, we mean the highlighting of cells, rows, or columns that should be excluded from your analysis or leaving blank rows to indicate separations. It’s best to create new fields where you can encode which data should be ignored.
“Prettifying” your data sheet using formatting
Having the right layout for your data is better than using formatting to make it look pretty. For example, merged cells can lead to misinterpretation of your data which can easily be avoided by planning the layout of your spreadsheet better.
So, when it comes down to it, do your best to avoid spaces, numbers and special characters in column headers; avoid special characters in your data, and remember to record your metadata in a separate text file.
5 Examples of how minor errors in spreadsheets have hurt big brand names
It’s often easy to think that these types of issues only happen to the “little” guys. But the truth is that even the biggest of brand names have taken hits due to some common spreadsheet errors. Scary isn’t it?
Accidental release of confidential information by AstraZeneca, 2011
- Cause: Excel templating error
- Result: The accidental release of sensitive information such as employees’ social security numbers, credit card numbers, etc. resulting in legal and financial hassles.
An $11mil severance error by Kodak, 2005
- Cause: Faulty spreadsheet
- Result: A miscalculation of severance pay accrued resulted from too many zeros being added to the figures on the spreadsheet, costing Kodak a reported $11 million.
A $2.6 billion dividend estimate error from Fidelity,
- Cause: Omitting the “minus” sign
- Result: When transcribing information from the financial records to a separate spreadsheet for further calculation, an omission of the minus sign by the accountant on the net capital loss of $1.3 billion resulted in it being treated as a gain, throwing calculations off by $2.6 billion.
A $6 billion trading loss for JP Morgan, 2012
- Cause: An Excel copy-and-paste error
- Result: As innocent as pasting the right information into the wrong spreadsheet during the expense process – this mistake cost JP Morgan $6 billion in trading losses.
A public embarrassment for the London Olympics, 2012
- Cause: A simple Excel typo
- Result: The committee vastly oversold the number of tickets and was forced to confess its carelessness, making amends to thousands of ticket-holders.
