Create a PivotTable from PowerPivot Data (Tutorial)
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 Create Your First PowerPivot Workbook (Tutorial).
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. Ensure this field displays in the Row Labels window of the Field List.
In the DimDate table, select the CalendarQuarter field. In the PivotTable Field List, move this field from the Values box into the Column Labels box.
Rename the PivotTable by double-clicking Sum of SalesAmount in the first cell, erasing the current text, and typing Sales by Channel.
Format the display of the data to make it easier to read and compare. Highlight the data cells, right-click and select Format Cells. In the Format Cells window, select Currency and make sure that 2 is selected in the Decimal places dropdown. Select $ for Symbol. Click OK. Change column widths if numbers aren’t readable.
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.
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 ProductCategory table.
Select the ProductCategoryName field. Ensure this field displays in the Row Labels window of the Field List.
In the PowerPivot Field List, locate the DimDate table.
Select the CalendarYear field. In the PivotTable Field List, drag this field from the Values window 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.
Format the display of the data to make it easier to read and compare. Highlight the data cells, right-click and select Format Cells. In the Format Cells window, select Currency and make sure that 2 is selected in the Decimal places dropdown. Select $ for Symbol. Click OK. Change column widths if numbers are not readable.
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).