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.

10 Common issues with using spreadsheets for data operations

While we advocate for using specialised tools and programs for your budgeting, accounting, and forecasting needs, if you need more convincing, here are 10 quick reasons that you should make the change from traditional spreadsheets to something a little more reliable.

With a traditional spreadsheet:

1. There is no error control
2. There's little reusability
3. There is low coverage of data operations
4. The scalability can be problematic
5. It is not open
6. There is a lack of automation
7. It can be time-consuming
8. Collaboration can be difficult
9. It's often not user-friendly and has a steep learning curve
10. Productionizing is tricky

Moving away from traditional spreadsheets

Using a specialised tool or platform to reliably gain insights from your data is key in today's modern age. While a single spreadsheet error may not be the end of the world, when used for data management, analysis, and forecasting, it can lead to bigger, systematic issues. 

Each year, as you can see from the examples we've touched on, businesses lose out on billions in revenue due to lost productivity, duplicated data entry, and manual data input. The time to make the move away from traditional spreadsheets has come and there are many benefits to moving toward a sophisticated online accounting system, etc.

As the UK's most cost-effective online accounting service, with an award-winning customer service team and Chartered Certified Accountants, we encourage you to take a look at Crunch's free accounting software and our integration with Brixx, an easy-to-use income and cost-forecasting tool. Simplify your processes, reduce the risk of human error, increase productivity, and better manage your data by giving automation a try today as it can reduce your company's overall financial risk.