This is a step by step tutorial on how you can set up an Excel Pivot Table based on an Access table or query.

Step 1 - Setting up the Excel Pivot Table

First open up a new Excel worksheet and click on the Data > Pivot Table or PivotChart Report… option.

Create your PivotTable

Once you have clicked on this option the following dialog box should appear, click on the External data source option and then click the Next > button.

Setting up the Excel Pivot Table - External Data Source

Step 2 - Setting up the Data Source connection

A PivotTable and PivotChart Wizard - Step 2 of 3 dialog box should pop up (seen below). As we have not yet retrieved any data click on the Get Data… button.

Setting up your Data Source Connection

In this example I have not yet set up a data source connection to my Access database, to do this double click on the <New Data Source> option.

Setting up your Data Source Connection - New Data Source

Here is an example of the Data Source properties, make sure for the driver you select the Microsoft Access Driver (*.mdb) from the drop down box. After the first 2 options have been completed, click on the Connect… button.

Setting up Data Source Ms Access

After you have clicked the Connect… button, the ODBC Microsoft Access Setup dialog box will pop up. The first step is to click on the Select… button.

A file browser will ask you which Access database you would like to select, navigate to where your Access database (.mdb) file is and click the OK button.

You can now see that the ODBC Microsoft Access Setup dialog box lets you know the location of the Access database that you just selected. Click the OK button to continue.

You will now be brought back to the Create New Data Source dialog box, it will outline all the properties we have just setup.

The 4th step will ask you to select a table, this step is optional but as you can see from the example below I selected a table. If you decide to leave this blank, further down the track you will be asked to select which Query or Table you want to base your Pivot Table on.

When you are happy with the options, click the OK button.

There is another option to save your User ID and Password in the data source definition. I would check this box if your Access database is password protected as it will prevent any future password prompt boxes from appearing.

Now you are able to see that the Data Source has been added successfully, select the new data source that was just created and click the OK button to continue (make sure the Use the Query Wizard to create/edit queries option is checked).

Step 3 - Preparing the data for the Pivot Table

The Query Wizard steps are part of the Microsoft Query application, this sits between Excel and Access and provides a staging area for your data. As you will see from the steps below, you are able to filter or sort your data before it enters the Pivot Table.

The first step of the Query Wizard will ask you to select the Table or Query you want to base your Pivot Table on, as I selected a table in an earlier step we are only shown that table (if you left this blank in the earlier step all the available tables and queries will appear in the left pane).

Click on the table and then click the > arrow button, this will move across all the fields associated with that table, you can choose to only move over certain columns by selecting each column name and clicking the > arrow button as well. Click the Next button to move on.

Please note : If you are trying to add a Query click on the Options… button and make sure that the checkbox next to Views is checked. You should now be able to see all available Queries available in your Access database within the Available tables and columns area.

I ignored the next two steps (as the Pivot Table can do these) however these steps will allow you to:

  1. Filter your data before it enters the Pivot Table (good for large data sets and improving performance)
  2. Sort your data before it enters the Pivot Table

Here are screen shots of the steps:

Filter Data

Sort Data

Click the Next > button twice to get through these steps.

The last part of the wizard will ask you to either:

  • Return Data to Microsoft Office Excel - This will close down the Query Wizard and resume finishing the Pivot Table setup.
  • View data or edit query in Microsoft Query - This will open up Microsoft Query to edit your query further (editing the native SQL statement). I would suggest this if you are basing a Pivot Table on multiple tables.
  • Create an OLAP Cube from this query - You can use this option to create an OLAP cube from the query.

I selected the Return Data to Microsoft Office Excel then clicked the Finish button, as I wanted to start using my Pivot Table straight away.

Step 4 - Setting up the Pivot Table

After you click the Finish button, the original dialog box will let you know that Data Fields have been retrieved, this is letting us know that all the data has been retrieved for the Pivot Table successfully. Click the Next > button to continue.

The next step is part of the standard Pivot Table wizard. It will ask you where you would like to put your Pivot Table. Select to put it in an existing worksheet starting at a certain cell or in a new sheet. Click on the Finish button to continue.

Done! Now you have a Pivot Table which is based on a table or query within Access!

Something to Remember

If the data in the Access database changes or is refreshed, to reflect the same changes in your Pivot Table you must refresh your Pivot Table. This can be done by activating the PivotTable toolbar (right click in the tool bar section at the top of Excel and select Pivot Table) and click on the ! (Refresh) button.

Your Pivot Table should now be refreshed.

If you have any questions or problems please comment.

Enjoy!