Creating a PowerPivot PivotTable

After you've brought data into PowerPivot for Excel 2013, you can summarize that data in the PivotTable. To demonstrate the process, I will use the CreatePivot sample file, which you can find in the Chapter09 folder of your Exercise Files archive. To create a PivotTable in PowerPivot, you need to open up PowerPivot, so I'll click the PowerPivot tab on the ribbon, and then click Manage. My PowerPivot data model contains two tables, and you can see their names here: SalesSummary, and MonthNames.

So, what I want to do is to summarize all of that data in a PivotTable. One benefit of working in PowerPivot is that I have created a relationship between the SalesSummary and MonthName tables, and if I click the Design tab on the ribbon, and click Manage Relationships, you can see in the dialog box that there is a relationship between the Month field of the SalesSummary table, and the MonthID field of the MonthNames table. That will come in handy when I show you how to work with the PivotTable inside of PowerPivot.

So, I'll click Close to close dialog box, and then on the Home tab of the PowerPivot ribbon, I'll click the PivotTable button. Doing so displays the Insert Pivot dialog box, and I want to create my PivotTable on the new worksheet, so I'll leave that option selected, and click OK. When the PivotTable appears, you can see a blank PivotTable here on a new worksheet, which in my workbook is named Sheet4. It might be named something different in yours. And you'll also see over in the PivotTable Fields pane that there are two tables instead of just one, or rather, a list of fields like you would see in a single table, if you created a PivotTable inside of the regular Excel program.

When you create the file, you'll see the files area show MonthNames, and Table1. Table name is the name of the table in the original Excel file from which the values are derived, and that's why that name appears there. See, here we have MonthNames, which if I click it, you can see that there's MonthID, and MonthName, and there's also a second table name SalesSummary, which has OrderID, Date, Year, and so on. And those table names and list of fields reflect the two source data tables that I used from PowerPivot to create this PivotTable.

Now I can add fields to the body of the PivotTable to display and manipulate its data. So, I'll add Department to the Columns area, and then I'll scroll down, and add Total to the Values area. Doing so displays sales totals for the five different departments. If you want to format this field, you can do so using the techniques that I have shown elsewhere in the course. But now let me show you what happens if I add the Month field, from the SalesSummary table to the Rows area. Doing so displays all the months that have sales, and those are 1, 2, 3, and 11 to 12; January, February, March, and November, and December.

But let's say that I want to display the month names instead of the month numbers. I can do that by dragging Month back to the Fields area to remove it from the PivotTable. Then I'll scroll up, and from the MonthNames table, I'll drag the MonthName field to the Rows area. Doing so displays the months, but you'll notice that instead of being in chronological order, the months are in alphabetical order. To fix that, I need to sort the data inside of that field. So, I'll hover the mouse pointer over the MonthName field, over in the PivotTable Fields pane, click the downward pointing black triangle that appears, and click Sort A to Z. Now, because MonthNames are a known custom list inside of Excel, when I click Sort A to Z, instead of staying in alphabetical order like they are now, the months are sorted January, February, March, November, and December, into the known order, and once you have your data inside of your PowerPivot PivotTable, you can work with it just as you would any normal PivotTable.

If you are data collection is less than a few thousand rows, then you probably won't need to use PowerPivot, but if your data collection is quite large, perhaps numbering in the hundreds of thousands, or even millions of rows, then the technique that I showed you allows you to create a PivotTable that you can manipulate using the skills that you've learned elsewhere in this course.