Excel PowerPivot Tutorial

Importing Access Data into PowerPivot

The sample Excel worksheet lists salespeople with accompanying data about the regions they work in, the products they sell, and their sales and profit amounts:

Sample Excel worksheet

We have also created a simple database that includes information about the sales team and their expenses:

Database information

While both sets of information are useful, having them together would be even better.

To start importing data from Access, click PowerPivot → Manage:

Powerpivot Manage Menu

This action will open the PowerPivot window. Click Home → From Database → From Access:

Powerpivot Window

This action will open the Table Import Wizard. Click the Browse button:

Table Import Wizard Dialog

The Open dialog will now be shown. Browse to the Exercise Files folder on your desktop.

Open Dialog

The database will now be listed inside the “Database name” text box:

Table Import Wizard Dialog, Database name field

Logon credentials are not required for this database, so click Next:

Table Import Wizard Dialog, Next button

In the next stage of the wizard, you need to choose how to import the data. As this is a very simple database, ensure that the first radio button is selected. Click Next:

Table Import Wizard Dialog, Next stage

Next, you need to select what tables in the selected database that you would like to import. This particular database only has one table (Sales), so ensure that a checkmark is placed beside it and click Finish:

Table Import Wizard Dialog, Select table

The data will now be imported. After the process is complete, click Close:

Table Import Wizard Dialog, Import complete

You will now be returned to the PowerPivot window where all of the imported data will be displayed:

PowerPivot window

Integrating Data with Relationships

In the sample workbook, data has been imported from two separate data sources. Now, we need to integrate this data. To do this, we need to create a relationship.

To start, open the PowerPivot window by clicking PowerPivot → Manage:

PowerPivot Manage menu

Ensure that the "Sheet1" tab is selected at the bottom of the PowerPivot window:

PowerPivot tabs

The data displayed here was imported from an Excel workbook. In particular, you can see that there is a column labeled Salesperson:

Sheet1 data

Switch to the Sales sheet by clicking on the Sales tab:

PowerPivot tabs

The data displayed here was imported from an Access database named Sales. Again, you will see a Salesperson column:

Sheet1 data

As the Salesperson field in each of these data sources uniquely identifies the same salespeople, it is ideal for integrating the data using relationships. To start, click Design → Create Relationship:

Create Relationship menu

The Create Relationship dialog will be displayed:

Create Relationship Dialog

Using the controls in this dialog, you need to select the tables and columns you want to use to create a relationship. “Sales” should be listed in the top table field. Choose Sheet1 for the Related Lookup Table field. Ensure that both data sources use the same column (Salesperson). Click Create:

Create Relationship Dialog

Now, in both data sources the Salesperson column header displays a relationship icon:

Relationship icons on data source

Creating a PivotTable with PowerPivot Data

Open the PowerPivot window by clicking PowerPivot → Manage:

PowerPivot Manage menu

After a relationship has been created between PowerPivot data, you can create a PivotTable. With the PowerPivot window open, click Home → PivotTable (not the drop-down arrow):

PowerPivot PivotTable menu

This action will open the Insert Pivot dialog. For this example, ensure that the New Worksheet radio button is selected and click OK:

Insert Pivot dialog

The new PivotTable will now be created in Excel. Inside the PivotTable Fields pane, you will see two sets of fields: Sales and Sheet1. Click on each item to expand them:


With both sets of fields now expanded, you can see all of the fields that are available. To add a field, simply click its checkbox. For this example, add Salesperson, Expense Account Balance, and Miles Traveled from the Sales field set. From the Sheet1 field set, add Sales and Profit. Now, you can see data from two different sources together in the same PivotTable:

PivotTable with data

You can work with this PivotTable as you would any other. If you need to access the data in this PivotTable, open the PowerPivot window by clicking PowerPivot → Manage:

PowerPivot Manage menu

If the data in the external source changes, you can refresh the data in the PowerPivot window by clicking Home → Refresh:

PowerPivot Refresh menu

Then, you would need to refresh the PivotTable in Excel by clicking PivotTable Tools – Analyze → Refresh.

See all Seven Institute Excel training Options