Articles on all things data...

When should you use a database?

 January 26

by Andrew Richards

10 signs that you need to move your spreadsheets into a database

If you're like most people, you will work with many more spreadsheets than databases. Yes, you may work with 3 or 4 - or maybe half a dozen - different databases for your job, but you probably have a dozen or more spreadsheets that you use regularly.

I spend a good part of my life going into people's offices, doing a mixture of training, trouble-shooting and advising. And when I look at a "problem" spreadsheet (that is, one which isn't performing as the users would like) it's really common that I'll have to say the same thing to my client. That thing is some variation on "I'm really sorry - but you're using the wrong tool for the job."

How can that be? How can people spend so much time working with, say, Excel, when a tool like Access, or SQL Server would do the job better for them?

It comes down, most commonly, to familiarity. People like what they know. They develop skills. And then, understandably, they want to use the tools that they're familiar and skillful with.

It's best summed up with a saying:

To a person with a hammer, every problem is a nail.

If you've got a tool, and you're good at using it, there's a tendency to want to use that tool for everything - even when there's a much better tool for the job.

I use Excel all the time, every day. But I also spend a great deal of time using, designing and building databases. I'd like to think that gives me a good overview on when to use each tool.

So, here are the top 10 signs that you should be using a database, rather than one or more spreadsheets.

  1. You have the same data spread over multiple spreadsheets, with a spreadsheet per month, year, office, department, region or whatever. This makes getting any report of data across these spreadsheets really difficult.
  2. You struggle with inconsistent data or data which contains errors. Excel's tools for data quality management (such as data validation, which I discuss more here) are limited at best.
  3. Your spreadsheet is (or your spreadsheets are, when you've got lots of spreadsheets doing the same job, as in point 1 above) getting too big to be efficient. They're taking longer to open every time, longer to recalculate and longer to save.
  4. You'd like to have a better, more flexible user interface than Excel's grid. Perhaps the sorts of screens that you can see in our database examples page, or perhaps something else. Just something more than endless rows and columns.
  5. You'd like to be able to automate processes. Perhaps that means link to other data, or export data into PDFs, automatically generate emails from your data or something else. But Excel's not going to make this easy, whereas these are all entirely possible with a database.
  6. You'd like to be able to roll back your data from time to time. Not just to last night's backup, but something more precise. Now not all database technologies allow this, but, as an example, SQL Azure (one of the more common places to store database data) allows you to roll back to any time over the past week or more, at the click of a button.
  7. You'd like different people to have different views of the data, when they log in
  8. You'd like different people to have different permissions on the data
  9. You'd like multiple people to be able to work with the data - simultaneously
  10. You'd like to be able to link different part of your data together, so that, for example, you can't delete a customer who has orders, or a pupil who has test results, or a patient who has a medical history, or... well, you get the idea.

There are lots of other ways in which databases can give major benefits over spreadsheets, but if you find yourself nodding along to one or more of the points above, you really should think about whether a database would suit you more.

It's a conversation we're happy to have with you (without any obligation for the conversation to go any further) - and we work with both spreadsheets and databases all the time, so we won't want to push you down any particular route.

Fill in the form below, or give us a call on 020 3397 1333 if you'd like to have a chat or ask questions!

share this

Related Posts

Migrating from Access to SharePoint

Excel waterfall charts – a step-by-step guide

Creating Excel Macros (part 2)

Sign up now to be the first to hear about new articles!