Create a PivotTable from PowerPivot Data (Tutorial)
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
Once you've added data to your PowerPivot workbook, PivotTables help you efficiently analyze your data in detail. You can make comparisons, detect patterns and relationships, and discover trends.
This tutorial assumes that you are already familiar with using PivotTables and PivotCharts. If not, see the following topics on Microsoft Office Online for an introduction:
PivotTable and PivotChart reports
Overview of PivotTable and PivotChart reports
Prerequisites
This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.
Add a PivotTable to Your Analysis
You'll continue using the PowerPivot workbook you created in the previous tasks. It already has the data imported and relationships created. Now you will add a PivotTable.
Important
Always create PivotTables from the PowerPivot window or the PowerPivot tab in the Excel window. There is also a PivotTable button on the Insert tab in the Excel window, but standard Excel PivotTables cannot access your PowerPivot data.
To Add a PivotTable to Your Analysis
In the PowerPivot window, on the PowerPivot Home tab, click PivotTable.
Select New Worksheet.
Excel adds an empty PivotTable to the location you specified and displays the PowerPivot Field List. The Field List displays two sections: a field section at the top for adding and removing fields, and a section at the bottom for rearranging and repositioning fields.
Select the empty PivotTable.
If you get an error message telling you the data list is no longer valid, right-click the table and select Refresh Data.
In the PowerPivot Field List, scroll down and locate the FactSales table.
Select the SalesAmount field. Ensure this field displays in the Values window of the Field List.
In the DimChannel table, select the ChannelName field. Move this field to the Column Labels window of the Field List.
In the DimDate table, select the Dates hierarchy. If necessary, move this hierarchy into the Row Labels box.
Rename the PivotTable by double-clicking Sum of SalesAmount in the first cell, erasing the current text, and typing Sales by Channel.
The Sales by Channel PivotTable lists the sum of sales for Contoso and for each Sales Channel, by quarters from first quarter 2007 through fourth quarter 2009.
Expand each year to drill down into quarterly, monthly, and daily sales figures.
To Add Another PivotTable to Your Analysis
In the Excel window, on the PowerPivot tab, click PivotTable.
Select New Worksheet.
Excel adds an empty PivotTable to the location you specified and displays the PowerPivot Field List.
Select the empty PivotTable.
If you get an error message telling you the data list is no longer valid, right-click the table and select Refresh Data.
In the PowerPivot Field List, scroll down and locate the FactSales table.
Select the TotalProfit field. Ensure this field displays in the Values window of the Field List.
In the PowerPivot Field List, locate the DimProduct table.
Select the Categories hierarchy. Ensure this field displays in the Row Labels window of the Field List.
In the PowerPivot Field List, locate the DimDate table.
Drag the CalendarYear field from the PivotTable Field List, into the Column Labels window.
Rename the PivotTable by double-clicking Sum of TotalProfit in the first cell, erasing the current text, and typing Profit by Category.
The Profit by Category PivotTable lists the sum of profits, by year, for each Contoso product category.
These are simple analyses of your data. To dig deeper, you will add a PivotChart and Slicers.
Delete a PivotTable
Keep the PivotTables in the workbook in order to complete the tutorial, but if you want to delete a table at some point, follow these steps.
To Delete a PivotTable
Click inside the PivotTable.
In the PivotTable Tools tab, select Options.
In the Actions group, click Select.
Select Entire PivotTable. On the Home ribbon select Delete and then click Delete Sheet.
Next Step
To continue this tutorial, go to the next topic: Create a PivotChart from PowerPivot Data (Tutorial).
See Also
Concepts
Create a PivotTable or PivotChart Report