Spreadsheet or database - How to decide

There are those who will tell you that every application should be a database and that spreadsheets should never be used, whereas others will look to address every problem with a spreadsheet. How do you decide what is best for your specific requirement?

As with most of these things, neither is right for every application. In this post I want to give you my thoughts about when I believe you should favour either - I would appreciate your views in the comment too, whether agreeing or opposing.

I thought the best way to address this would be as a series of questions about your specific requirement and some comments about how your answer might influence the decision.

I have assumed in the questions below that the decision has already been reached that a bespoke solution is required.

1. Does my application require access (and particularly editing) by multiple users at the same time?
Although there are ways to achieve this with a spreadsheet (for example using Sharepoint with Excel, or if you do not require the functionality of Excel, Google spreadsheets are great at this), a yes to this question should certainly push you down the database route. In most cases, if there is only one user then a spreadsheet is the most cost-effective option.

2. Will large amounts of data need to be held in the application?
If the data really does need to be held in the application, a yes would favour a database, however if the data is already held elsewhere (for example your accounting or ERP system), Excel is an excellent tool for reporting from the data.

3. What interaction (if any) does the application require with other applications?
As stated in point 2, Excel can be an excellent reporting tool from other applications where the data flow is one way (i.e. into Excel). If your application needs two-way communication with other applications, or if it needs to trigger real-time events, such as reminder emails, then a database would normally be more appropriate.

4. Who will use it?
Many users are comfortable with Excel and will find it easy to use, however it is much harder to make "idiot-proof". If you really want to lock it down in such a way that it can't be edited by the user at all, then a database may be more appropriate. With a reasonably competent Excel user, the ability to edit and enhance might be a positive for the spreadsheet solution.

5. Where are the users?
If everyone who might use the application is on the same network (and point 1 is not a major issue), then a spreadsheet held on the server might be what is needed. Alternatively, where the application performs a task (rather than holds data), multiple copies can be used - although this may need to be controlled. An on-line database can be great way to deal with multiple users who need to access the same data from anywhere as all they need is a web browser.

6. What is my budget?
In the real world, this one can't be ignored. Assuming that either approach could address your requirements, it will almost certainly be cheaper to pay a third party to have a spreadsheet built - in many cases you can do it yourself.

I hope this helps. If you need any help in deciding, please feel free to drop me an email. At Spreadsheets by Email we can help you with both spreadsheet solutions and online databases.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.