Excel Is NOT a Database!
We all know what Microsoft Excel is. It’s a spreadsheet software. It’s not a database. It’s never been a database. It never will be a database. Do I sound strident there? GOOD!
Seriously, it’s not a database, folks, so please quit using it as one. I can’t tell you how many places I’ve worked keep critical data in spreadsheets, jumping through all kinds of hoops and writing all kinds of code to get it to act like a database. The employee hours they spend maintaining these spreadsheets and getting information back out of them just astounds me. Why don’t they just convert it to a database and be done with it?
Why Businesses Don’t Use Databases
I’ve heard many excuses over the years as to why businesses use Excel over Access. Let’s debunk some of those reasons.
Access Is Too Hard To Learn
It really isn’t, you know. Most employees who interact with the database only need to know how to fill in a form or click on an already created report. The only people who need any in-depth knowledge of Access are the ones who create the database and those who maintain the back end of the database. And honestly, they need less in-depth knowledge than the person struggling with coding in Excel to try and get a spreadsheet to act like a database, and they don’t have to spend nearly as much time to manage the database.
We’ve Always Used Excel for This
So what? You may have always used a hammer to crack nuts, but it doesn’t make it the best tool for the job. If you’re working on the company financials, by all means, use a spreadsheet. If you’re detailing your budget, of course you’ll use a spreadsheet. But if you’re maintaining non-numerical data, use a database. Actually, even most financial data is kept on a database these days (that’s what accounting programs are, they’re databases). They simply export cumulative data to spreadsheets for special financial calculations such as projections and planning.
There’s Not Enough Data Here to Warrant a Database
How many times have I heard this? And a year or three later, they’re still using that spreadsheet, only it’s grown to 10,000 rows and takes 5 minutes to open. Unless you’re intending this spreadsheet to be a list of your 50 or so employees with phone numbers, you’re probably better off with a spreadsheet. Although, even the list of employees could possibly grow exponentially, so scratch that idea. Seriously though, if you think you’re just making a small list, go ahead and put it into a spreadsheet. But if over time that list is growing unmanageable, then create a database and import your data from the spreadsheet. It’s really simple to do and well worth the 5 minutes it’s going to take you. Why You Should Use a Database
Here’s the nitty gritty of why you should use a database to store data and not a spreadsheet:1. More than one person can update a database at a time. Only one person at a time can update a spreadsheet. Right there you have an efficiency incentive to use a database. And how many times has someone opened a spreadsheet in Read Only mode, saved changes to it to their harddrive, and then copied it back over the network copy, eliminating any changes the other person made shudders. I’ve seen that happen a number of times over the years.
2. A database stores information more efficiently. Relational databases, such as Microsoft Access, uses tables to segregate data. You’ll have one table for customer information, and another one for customer purchases. These would be tied together via the customer ID. So you don’t have to repeat all of your customer information for each purchase made. Therefore, your database is smaller than your spreadsheet. This is a crude example, but it gives you an idea how, overtime, your database is hugely more streamlined than your spreadsheet.
3. Queries, and the reports based on those queries, are easier to write and run. Because your data is relational, you can mix and match and re-sort your data in all kinds of different ways. You can do that with a spreadsheet, given enough time and code. But with a database like Access, a person with relatively little knowledge can produce complex queries and reports.
4. Data recovery is more efficient. If you have a large amount of data, finding the particular data point you want can be especially difficult in a spreadsheet. Those of you who work with large spreadsheets know this. You get a spreadsheet with thousands of lines, and paging through the spreadsheet can slow to a crawl, especially if you don’t have an ultra-fast computer.
Just Say No
The next time your boss wants to use a spreadsheet for a project better suited to a database, just say no. Explain why a database is better and get permission to use a database. Like I said, Excel is a good tool for certain things, but it just isn’t a database.