When customers come to us for Excel training we ask about what they would like to achieve from the training and if they have any specific problems they need to solve. Also we encourage asking questions during the training to help keep the course as relevant as possible.

The solutions to the problems or answers to these questions are sometimes not to use Excel at all, but to switch to Access. This is a well-trodden path that is normally easy to follow, but for a customer who already has skills in Excel and has heard Access is much more complicated, they may feel it is easier to simply carry on with Excel.

Access can become complicated because of the extensive functionality, but it doesn’t have to be. If Access is right for the job, with a little investment up front, it will provide a simpler and more effective solution in the long term.

Indicators

So how do we recognise the point at which to convert an Excel solution to Access?

• Storing data on multiple sheets but with the same or related headings
• Storing multiple options for a single entry
  e.g. multiple addresses or contacts for an organisation
• Using a large number of VLOOKUP (also INDEX and MATCH) functions
• Storing so much data that opening and saving Excel files takes some time
• Multiple people need access to the data possibly at the same time
• Having problems maintaining data consistency over a period of time

Other Options

I was talking to an SQL guru the other day and he mentioned that often he would use SQL Server (even the free version) rather than use Access. However, we think there is a sizeable number of people for whom Access sits very nicely between an Excel spread sheet and a custom designed SQL database with Reporting Services bolted on to provide the end result.

Best of Both

While there is some overlap between Excel and Access, Excel is the best tool to analyse data while Access is best at efficiently storing and retrieving data. One of the advantages of using Microsoft Office is the integration between the products, so store data in Access and export a selection of it to Excel to analyse.