Spreadsheet v Database

A question that we are being asked more and more is “When should I use a database rather than a spreadsheet?”

Excel and Access are, in their own rights, powerful tools and enable users to store large amounts of data, run powerful queries and provide analysis tools to slice and dice data, and perform sophisticated calculations that return the information that you need.

So here is the dilemma; which application should I be using? Is one application better than the other?

Each application has clear strengths with regard to storage, data analysis, data validation and integrity, multi-user collaboration, security and reporting. Therefore, before a solution is chosen it is important to understand the benefits of both applications, what your requirements are and how you can work with both programs to turn your data in to meaningful information.

One such trigger that may mean that it is time to move to an Access database is if you are linking to multiple spreadsheets or worksheets within a file using lookups to get the data you require.

For example you may track orders with data for customers, products, prices, etc… being held in different files and/or worksheets. The way that Access maintains and stores this data in tables makes it much easier to link the information, either via automatic lookups within tables, or in structured queries.

Another trigger is where multiple users are sharing an Excel spreadsheet on a network, each making their own changes and updates to the spreadsheet. These changes are not saved until the workbook is either manually saved or closed. If any changes clash, then the user is asked to resolve the conflict.

More seriously and I’m sure most of us have experienced data loss when using a standalone Excel file and it crashes or there is a power cut, is the impact can be more significant when a file is shared as each user has to identify when their inputs were last updated and saved in the Excel file.

Here Access’ big benefit is that it helps with data loss prevention as changes are made and saved continuously so that, in the event of an unexpected failure, you are unlikely to lose much work, if any. In Access only the record currently being changed is open and only to one user, with the rest of the data being available to other users without conflict meaning that system crashes usually have much less impact.

One of the main factors for considering a switch to Access is volumes of data. Whilst later versions of Excel can now cope with over one million rows and one thousand columns, experience has shown that Excel slows down once you get past a few tens of thousands of rows, Access on the other hand can happily cope with several millions rows of data.

Two Applications Are Better Than One

Often the solution is to use a combination of the two applications, using Access for data storage and Excel for analysis and reporting.

The two applications work very well together, meaning that either a sub-set of the main data can be exported to Excel, or Excel can link directly to Access. Excel’s superior reporting capabilities (pivots, graphs, advanced formatting, etc...) can then be used to their full effect.

Where reporting is fixed, such as the production of a board pack, Excel’s additional security measures can be added, including protecting cells and formulae to ensure the integrity of the final report.

For more ad-hoc analysis such as what-if scenarios, pivot tables can be linked directly to Access. In both cases you can be sure that the initial data has integrity.

Excel then allows us to present this information in a format that meets our target audience needs, some people like tables of data some like graphical analysis.

The choice is yours with Excel and your audience.

Excel v Access Considerations

If you currently use a spreadsheet and some or all of the following apply you should consider using Access:

More than one person needs to access the spreadsheet and make changes to data or you use a shared Excel file on your network.
You use and/or link several spreadsheets with related data and changes in one could affect other spreadsheets
There is a large amount of data collected and stored
Information is subject to change (change of address, prices, foreign exchange rates, etc.)…