How to create a PivotTable from the Excel PowerPivot Data Model

Power Pivot’s PivotTables offer unique features, but creating and utilizing them can be challenging for beginners. This post offers a brief overview:

Inserting the PivotTable

Creating the Data Model in Power Pivot is one thing, but actually using it in Excel is another. To do so, click the Manage button from the Power Pivot tab on the ribbon, then select an option from PivotTable on the home ribbon of Power Pivot:

PivotTable load options

Power Pivot provides a number of configuration options for loading a PivotTable into the workbook.

Many of these include both PivotTables and PivotCharts, as Power Pivot is often used to create basic dashboards and reports. The last option, Flattened PivotTable, displays all values in a single row or column. It can simplify data presentation but may obscure important patterns and require manual formatting.

For now, go ahead and select the main PivotTable option. Click OK on the Create PivotTable menu insert into a new worksheet, and you should have something like this:

This “Power” PivotTable works mostly in the same way as classic PivotTables. Perhaps the biggest difference, of course, is the ability to pull data from multiple tables.

For example, drag person from the people table to Rows and Sum of sales from orders to Values. You may notice that each table is double-stated in the PivotTable. You want to use the ones that include a small orange cylinder icon, as these are the tables from the actual Data Model.

The duplicate tables visible in the PivotTable are the raw tables stored in the Excel workbook, and would not appear if the data had been imported from external sources like csv files. Unfortunately, there is no way to hide these duplicate tables from the PivotTable.

With Power Pivot comes more features…

As you keep learning about Power Pivot and its PivotTables, you may find more advanced features that can be beneficial for complex data analysis, although they might require more effort than classic PivotTables.

What challenges and opportunities have you found while working with Power Pivot PivotTables? Let me know in the comments.