Why Excel Is A Terrible Database For Your Business

Repeat after me: Excel is not a database.

I’m sorry to deliver the news like this. I know it may be a tough pill to swallow for some of you. Many folks in your department will likely argue that we’re wrong, that Excel can be a database, but remember: denial is just the first stage of grief.

The good news is, it’s all going to be okay.

Excel is a very powerful program, and there are more than a few straight up warlocks out there who can work its black magic, using the interface to create presentations, dashboards, and analyze data sets with an adroitness that’ll make you wonder whose soul they had to sell for their skills.

While the Excel program is robust, the spreadsheets created by Excel are incredibly fragile. There is no way to trace where your data came from, there’s no audit trail, and there’s no easy way to test spreadsheets. Still, the biggest problem is that anyone can create Excel spreadsheets-badly. Because it’s so easy to use, the creation of even important spreadsheets is not restricted to people who understand programming and execute it in a methodical, well-documented way.

And just as we still can’t turn base metals to gold, we cannot turn Excel spreadsheets into a functional database.

Stop Us If You’ve Heard This One

Here’s a familiar scenario: you enter your data into a shared spreadsheet. You try to save it and receive a notice that the workbook is currently in use. You decide between closing out and losing all of your inputs, or saving a copy of the file, telling yourself that you’ll go back and merge the data later. But, that never happens, and your team ends up with multiple copies of a spreadsheet, each one carrying a portion of the truth.

This is a common story. The reason is that Excel only really works when one person has editorial control over the data, and that’s just impractical when it comes to large data sets that need to be accessed by personnel and systems across an organization. When only one person is in charge of data, no one else is really there to ensure that the data and functions are all entirely accurate. This leaves room to introduce errors, and Excel errors have a long, storied history of creating problems when inappropriately used as a database.

Here are a few examples:

In 2008, Barclays Capital purchased 179 bad contracts from Lehman Brothers as the result of an Excel error; Barclays “sent an Excel spreadsheet containing a list of contracts to be included in the purchase agreement…The spreadsheet contained nearly 1,000 rows and with more than 24,000 individual cells and had to be reformatted…a junior associate reformatting the work was unaware that the original Excel document included hidden rows containing contracts that were marked with an ’n’ to signify they should not be part of the deal.”

In 2012, an Excel miscalculation cost JPMorgan Chase $6.2 million after a trader after cells were added instead of averaged. This famously became known as “The London Whale Incident”.

In 2014, Canadian-based electric power generator Trans Alta lost $24 million in a contract purchase when a copy/paste error in Excel accidentally increased purchase prices, wiping out 10% of their profits for the year.

But Why Isn’t Excel a Database, Exactly?

To function as a database, the software must be able to handle different inputs from multiple sources at the same time, and allow many applications to access and manipulate that software simultaneously without corrupting or shifting the source data, even updating it in real time. Excel just doesn’t do that.

So how can you replace your ersatz Excel database with a real database? And what will that even get you?

I’m glad you asked.

The first (and arguably most difficult) step away from Excel is cultural. It’s likely that many of your team members have been using Excel for a big portion of their career by now. Don’t be surprised when they push back against your insistence that the industry-standard tool they’ve grown accustomed to needs to go. And maybe you don’t need to build a database for them. If you only have one or two Excel enthusiasts, well hey, they probably know what they’re doing, right?

Well, okay sure, maybe they do. But you can’t be sure there aren’t any errors in their records. In fact, a study published in the Journal of End User’s Computing found 88 percent of spreadsheets contain errors. Excel doesn’t have any tools for verifying its inputs against your raw data. So if you want to be sure that your data is accurate, you’re going to need proper software.

Even worse, any errors which do occur are going to be very difficult to identify and rectify because of the lack of verification against your input data.

Excel doesn’t offer access permissions either, so if you want to keep that information secure, you’re going to need to implement security protocols and infrastructure to keep it safe, as well as find some way to ensure that everyone adheres to the policies set in place. Creating that security also comes with some maintenance cost as well, so be prepared to allocate some resources for that, too.

Err, so maybe it’s not such a great idea to lean on Excel for this stuff, ever.

Thank You, Based Data

We can’t give you a lot of advice on the cultural end of things, considering every company is different. But people who need a database deal with data all the time. If you bring up these points to them, they’ll probably come around once you decide on the software you do want.

That said, we can help you find out what software is right for you. Of course, there are a few things to consider when selecting a database. Here are some key questions you need to ask yourself:

• How much data am I storing?
• How many people need to access this data at once?
• What programming languages do your teams use?
• What is our budget?

There are other elements, to be sure, but these will get you started on the right track.

Once you’ve figured out these parameters, you might be surprised to find that building your own database isn’t as daunting as you might think. One of the downsides to specialized, single-duty point solutions is that it’s expensive and may not integrate well with your existing architecture. Even more expensive is having a company code a custom solution for you.

Enter: No-code Database Builders

Because of the integrated nature of no-code platforms, creating a secure, permissioned database with tracked changes and simultaneous accessibility features is not a bridge too far. Creating a knowledge base for data handling protocols can be done from within your database app itself, and building out other apps for accessing that data is an accessible task for anyone to tackle.

Stop using Excel for this kind of stuff. You’ll thank us later.

Finding An Alternative To Excel

If you’re interested in learning more about how to step away from Excel and build your own database out of your existing spreadsheets, we’ve got just the thing. We’ll show you how you can turn your spreadsheets into a highly-powerful, crazy customizable database with Kintone.

Originally published at https://blog.kintone.com.