There are many ways you can import Access data into Excel, the simplest way is via the Data Import wizard contained within Excel. Read on and learn the fast way to import Access data into Excel…
As an Excel analyst there will be occasions when the data you need lives on another software system, Microsoft Access databases are a common place for that data to live as many businesses use it as part of the Microsoft Office suite.
When creating reports and dashboards in Excel, which use data from Access, it is not practical to copy and paste the data directly from a table, especially when tens of thousands of rows exist or a regular update is required, the best solution is to import the Access data into Excel using the import wizard and create a connection.
Let’s jump right in:
1) Locate your Access database and data table. If you are importing data from Access into Excel then you need to know what you are trying to import (the Access table) and where that file is located (the location and name of the Access database). These things are always best completed at the outset.
2) Now you know what you are going to get from Access you can open up your Excel file and select the “Data” option from the ribbon, followed by the option “From Access” (as shown in the below image):
3) When you select “From Access” a new window will open up called “Select Data Source”, this is where you must navigate to the Access database that contains your data, when you have found it either double click to make the selection or left click the name once to highlight the database, then select “Open”:
4) The next window is the “Select Table” window. From the list select the Access table that you want to import into Excel and click OK. It’s worth noting that if there is only one table in the Access database this part will be skipped automatically:
5) After you select your table the “Import Data” window appears. This is where you can toggle with some last minute options such as where you want to place the data, you can even use the data straight in a Pivot Table or chart, for this example just click OK to go ahead and import the Access data into Excel:
6) After you have clicked OK your Access data will import into Excel. Depending on the size of the table it might take a few seconds to complete:
Now you can manipulate the Access data within Excel or build reports and create charts from the data.
Very useful tip
By importing the data this way you create a direct connection to the Access data, that means if your Access table changes, i.e. the data inside it grows, all you have to do is refresh the table in Excel and the latest data will update. This is extremely useful for setting up frequent reports or dashboards.
One of the useful features often ignored is within the “Properties” of the import. When you are on stage 5 and are faced with this window:
Select the “Properties” item and then you will be faced with the “Connection Properties” window. Here you can check the box to refresh the data when opening the file which is a useful technique if the Access database is frequently updated and you want your Excel version of the data to stay up-to-date:
Hopefully you will find this method one of the easiest ways to import Access data into Excel, there are lots of great uses for this technique in connected Excel dashboards or advanced reporting so it is a great process to understand for all Excel analysts.