IT Counts has addressed the spreadsheet v. database argument
in the past and a question that often crops up during the Faculty roadshows is:
how do I know when I should be using a database rather than a spreadsheet?
Microsoft has contributed their own suggestions as to how to
go about making the decision in their 'Help and How-to' training resource.
Entitled "Confused about whether to use Access or Excel? Learn how to choose the right program for your needs", the short online course tries to explain the differences between a relational
and flat-file data structure before going on to consider some practical issues:
- Access is better for storage, Excel for analysis
- Access copes better with large quantities of data
- Excel is better at numbers than text
- Access is better at data entry
- Access copes better with sharing data
- Access allows users to connect to several different data
sources, and edit the data therein
- Excel is better at what-if analysis
- Excel is better at interactive analysis using tools such as
PivotTable reports
- Excel is better at conveying information visually
- Not everyone has Access, not everyone knows how to use it
The course is clearly designed to keep things as simple as
possible, perhaps oversimplifying certain issues. For example, when comparing
the issue of the accuracy of the data in relational data structures compared to
flat files, the only argument used is that a relational structure stops people
entering data into the wrong table. You could argue that by reducing data
duplication it's actually the enforced consistency of the data within each
table that is the main argument for using a relational structure.
Fortunately, the Microsoft site does have a rather more
detailed, text-based, section that goes into greater depth on the reasons for
choosing Access or Excel. Using Access or Excel to manage your data
fills in many of the gaps in the coverage of the simplified course and also
adds many other useful arguments. In particular it points out that using Access
to store, manage and control the input of data, whilst using Excel to link to
the data to analyse it, can often be the best solution.
Of course, there's often no better guide to how to deal with
a particular requirement than past experience. Please let us know of any real
examples of choosing a database over a spreadsheet, or vice versa, that you
have come across, including what the outcome was.