Microsoft's guidance on when to abandon Excel in favour of Access

by Simon Hurst on 28.04.2010 04:29

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.