7 scary Excel errors your team is probably making

Halloween, the scariest of holidays. But perhaps nothing could be scarier to watch than all the Excel mishaps your colleagues are committing. Excel spreadsheet errors and mistakes are far scarier than anything you could find on Huluween.

While Hollywood is full of fake blood and fangs, these Excel errors are very real, and they’re creeping up all around you:

1. Not normalizing data

With 800 million users of Microsoft Excel, issues are bound to happen. In fact, research estimates that somewhere around 90% of spreadsheets contain errors. One of the most common of these errors is not normalizing data. 

Excel is often relied on for data capture, but users often don’t understand just how important it is to only utilize normalized data. Data that is set up incorrectly can introduce errors into formulas, (such as dates being read as numbers, which is what happened when one employee got an accidental bonus for the pound sterling equivalent of $52,000).

2. Merging headers to improve design

Uh oh! I bet a lot of people at your company are committing this terrifying Excel mistake. (By a lot, we mean everyone.) Merging cells to make nice-looking data headers seems innocent enough, but like my vampire jokes, it really sucks.

Unless you’ve got a very good reason to do so (such as the title of the entire tab), you should avoid merging cells as a means of giving space for a large header. The reason is you can’t easily use the “fill down” formula. Also, it can confuse others as to where the data is supposed to go, and you end up with mis-used or unused columns. 

3. Using Excel to solve organizational delays

Another big Excel mistake? Using Excel when you shouldn’t. While there are many valid uses of Excel, there are also plenty of times when an organization should be making better use of software they’re already paying for. 

But, employees might utilize Excel to speed up something in the short term, only to create siloed data in the long run. For example, they may not know how to run a complicated calculation in their accounting system, and instead of reaching out to the software’s support team, they download the data and run a formula they learned fifteen years ago. 

Or, instead of asking someone from another department to generate a report from a software that department owns, they piece together data from systems they have access to and work with it in Excel.

4. Relying solely on self-taught formulas

A spreadsheet error caused Canopy Growth, a cannabis research and development company, to massively underreport its losses for the final quarter of 2018. The quarterly losses posted weren’t $50 million, but rather should have been stated as $114 million. Since their revenue shot up so quickly that year, it wasn’t that big of a loss difference for the highly profitable company, but it did cause its stock to drop 2% after the mistake was announced.

It all came down to a formula error. Unsurprisingly, formula mistakes are pretty common. This is likely because the vast majority of Excel users are self-taught. 

To mitigate this, identify the important spreadsheet formulas used in your organization and have an expert train employees in proper calculations.

5. Leaving cells out of sums (a common Excel error)

This Excel mistake is so simple and obvious, you might assume that you would never commit it. But because we have full confidence in ourselves to not leave certain cells out of sums and calculations, we’re that much more vulnerable to this beginner-status mistake.

This error frequently happens when another row is added to an existing sum formula, so when adding rows, just remember to double check the sum calculation to make sure your new data is included. 

(Boo!) Don’t pretend your organization is immune to this scary mistake. After all, some Harvard professors committed this exact same mistake and miscalculated their prediction for how bad economies shrink when their country’s debt reaches 90% of GDR. So, go check your important spreadsheets for this problem.

6. Adding blank rows to make the spreadsheet easier to read

In Word documents, it’s common to add extra whitespace between one section or another, or to even put in a page break. However, this doesn’t translate well in Excel. 

You should avoid the temptation to use blank rows as a way to enhance readability and design. Why? Doing so can affect your formulas and cause another error in your Excel spreadsheet. Depending on the functions utilized, the blank rows could be treated as data. Autofill and certain formulas will simply stop at a blank row, which means your Excel spreadsheet won’t turn out as you expected and your work will take longer than it should, as you’ll need to make manual corrections.

Instead of blank rows, make sure that everyone in your company is trained in using Excel formatting, such as fill color, borders, and font size.

7. Not using data validation for templates and shared files

Data validation is critical to make sure that everyone who touches an Excel spreadsheet is putting the right types of data in the right places. With data validation, you can’t enter a whole number in a cell that is supposed to have a time input. Similarly, you can’t put time data where there should be a decimal.

Remember the accidental $52,000 employee bonus we mentioned in mistake #1? That likely wouldn’t have happened if the data had been normalized after the fact, or (better yet), if the spreadsheet had data validation for critical cells, to ensure that only numbers in the right format were entered. For example, that number would have been added as a date, and would not later have been interpreted as a dollar amount. 

When you create a file explicitly for your own use, you trust yourself to put the right type of data in the right place. But what if a year later, you forget the type of data that’s supposed to be in a certain cell? It’s wise to use data validation even for spreadsheets you don’t expect to share. 

While you can't fix everything that goes bump in the night, you can fix these Excel errors with better training. So get to it!

And for seamless XLS imports that developers and customers love, check out Flatfile.