Creating a PivotTable with PowerPivot Data

Creating a PivotTable with PowerPivot Data

In this article, we will delve a little deeper into how to create a PivotTable with PowerPivot Data in Microsoft Excel.

For instructor delivered Microsoft Excel training classes in Los Angeles call us on 888.815.0604..

If you see a security warning, click Enable Content. Ensure that the PowerPivot tab is displayed on the ribbon. If you do not see this tab, please complete the steps in the “Enabling PowerPivot” topic first.

Open the PowerPivot window by clicking PowerPivot → Manage:

Open PowerPivot Window

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):

Select PivotTable

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

New Worksheet Opened

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:

PivotTable Field Pane

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:

Add Field to 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:

Access pivottable in worksheet

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

Refresh Powerpivot

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


Classroom based Microsoft Excel available in LA

Join our 1-day Excel workshop. We run classes for every skill level each month. Check out our student testimonials Excel class reviews.