I am often shown Excel workbooks that have been set up as a database to store information. Nothing wrong with that! However, as an experienced Microsoft Office Trainer, I sometime feel that the workbook I am being shown is large, cumbersome and very user unfriendly and would be better if the data wasn’t stored in Excel at all, but stored in a simple Access database. In this article I am going to examine in which circumstances Excel is best for databases and for which Access is better suited.

On the face of it, both Excel and Access look very similar due to their tabular structure as both are designed to store data. What differentiates between the two is the type of database, Flat (Excel) or Relational (Access). This is just one of the many questions we need to ask ourselves before deciding which is better.

So what is a flat database? A flat database is a simple list that is standalone and not connected to any other data. A simple list of friends or the contents of a stationary cupboard can be classed as flat database structure. The advantage of the flat database is that they are easy to create and maintain as long as there is not too much information to be stored in them.

A relational database on the other hand, splits up the information into groups of related data, each group of data is stored in its own table. For example, a company’s customer database will have information about customers, products, employees and sales. Each of these data groups is stored in a separate table. You could say that a relational database is made up of many related flat databases.

An advantage of a relational database structure includes the prevention of data duplication which means the data only ever appears once in the database and there is no need to update or change data in more than one place. Also, relational databases are easier to query and extract the required data. If you have a lot of repeated data like names of office branches, cities or counties, these can be put in a different tables and create a relational structure. This will save time and effort as there will be no need to repeatedly enter the same data again and again.

Other considerations may depend on what the objective of the database is and how much data there is to store. If the main objective is to store and retrieve data then almost certainly a relational database will be required. If the main objective to analyse data, in which case, a flat database in Excel will be appropriate.

Another advantage of Access over Excel is user input. Many users find it difficult to enter data in to a large spreadsheet structure with all the data visible. In Access, you can create forms for easier and comfortable user input increasing the data accuracy.

When deciding on a database structure ask yourself who will be using the database and will users want to access the data in real-time at the same time? Will users need to view and update external databases or just to view them? Excel is more designed for multiple users at different times where as Access is designed for multiple users at the same time. Both Excel and Access can connect to external data sources. However changes can only be made to these external data sources by using Access.

In this article we’ve examined the difference between Excel and Access databases and the advantages that each one possesses.