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.

Comments
rwmjlally's picture

Hear him, Hear him

rwmjlally | | Permalink

I have never understood why so many otherwise intelligent people think a data manipulation tool is a good place to STORE data. 

Do you think Microsoft said to themselves "Excel is a perfectly secure place to store data so let us produce a totally unnecessary database called Access"?

Excel goes to extraordinary lengths to enable you to CHANGE data easily. A database does almost exactly the opposite. 

I love Excel, it is my favourite thing, it is a fabulous analysis and reporting tool, I feel very lucky that people pay me to develop Excel solutions, so I beg you to listen to someone who knows more about Excel than you do: Excel is a very stupid place to store data.

 

-- Richard Lally Optimally Limited

tviljoen's picture

Spreadsheets v Databases - A third alternative!

tviljoen | | Permalink

I enjoyed reading the article above and thought I would add another dimension to the debate:

 

7 Complexity of data?

Spreadsheets are in essence 3 dimensional storage devices (rows, columns and sheets). However, most situations require analysis or handling of data along more than 3 dimensions - requiring much more complex "array type" formulas.

8 Historical perspective or looking into the future?

Most 'actual or historic' data reside in relational databases supporting operational systems in organisations. However, most forward looking planning and forecast modelling happens in the more dynamic spreadsheet environment.

 

So is it really a question of spreadsheets v databases or is there another alternative? I believe that OLAP aggregation engines can provide an excellent win/win solution to users facing this trade-off. Historical actuals can be loaded into what is defined as a multi-dimensional database, and the formula engine lends itself ideally for modelling in multi-dimensional data. Most OLAP engines are also Excel friendly and allow users to interact with their data via spreadsheets.

Theunis Viljoen - BIOLAP 

Spreadsheet or database

pakitchen.dubma... | | Permalink

Some useful and valuable comments in the above.

For me one important factor which is key especially in a small business is who can support the problems in the application when they occur - and there are any number of laws which say that they will. Most small businesses - which is where my efforts are focussed - simply want a tool for a job. They do not have the time to spend resolving support issues and with 'homespun' databases they will occur. While the original author of the database is around fine you will have support but when they move on or the problem exceeds their capabilities you have a problem.

I believe you should try to acquire the tools you need from proven products/suppliers - usually database applications - and which are adequately supported. Use excel for analysis and reporting by all means it can provide excellent output. Major storage requirements in excel should be avoided wherever possible. I like many do keep some information in databases but it is relatively minor and well backed up.

 

Philip Kitchen

 

 

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.