Creating a PowerPivot PivotTable helps you condense all of the data you've brought into PowerPivot into a summary state in PowerPivot for Microsoft Excel 2013. Learn how to create and manage relationships with data and use the PivotTable button to display an easily readable, formatted summary of your work, in this online tutorial.
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.
Author
Released
4/11/2013- Creating a PivotTable
- Summarizing multiple data fields
- Managing subtotals and grand totals
- Grouping PivotTable fields
- Filtering with selections, rules, slicers, and search filters
- Applying PivotTable styles
- Formatting cells
- Creating PivotCharts
- Enabling PowerPivot
- Using DAX operators
- Visualizing data with matrices, cards, and tiles
- Building charts and maps
Skill Level Intermediate
Duration
Views
Related Courses
-
Office 2013 New Features
with David Rivers2h 31m Intermediate -
Managing and Analyzing Data in Excel 2010
with Dennis Taylor1h 32m Intermediate -
Excel 2013: Shortcuts
with Curt Frye3h 16m Intermediate
-
Introduction
-
Welcome58s
-
-
1. Creating and Pivoting PivotTables
-
Introducing PivotTables3m 14s
-
Creating a PivotTable2m 37s
-
Pivoting a PivotTable3m 13s
-
Configuring a PivotTable3m 17s
-
Managing PivotTables3m 48s
-
-
2. Summarizing PivotTable Data
-
Grouping PivotTable fields2m 40s
-
3. Sorting and Filtering PivotTable Data
-
Sorting PivotTable data3m 41s
-
Creating a custom sort order3m 52s
-
Formatting slicers3m 7s
-
-
4. Formatting PivotTables
-
Applying a PivotTable style2m 36s
-
Creating a PivotTable style4m 27s
-
-
5. Applying Conditional Formats to PivotTables
-
6. Creating and Manipulating PivotCharts
-
Creating a PivotChart2m 34s
-
Pivoting a PivotChart2m 24s
-
Filtering a PivotChart2m 48s
-
Formatting a PivotChart3m 38s
-
-
7. Printing PivotTables
-
Printing a PivotTable2m 53s
-
Printing a PivotChart1m 14s
-
-
8. Manipulating PivotTables Using Macros
-
Running an Excel macro4m 4s
-
9. Starting with PowerPivot and the Data Model
-
Introducing PowerPivot4m 32s
-
Importing PowerPivot data3m 24s
-
Managing table columns3m 21s
-
10. Introducing Data Analysis Expressions (DAX)
-
Introducing the DAX language2m 29s
-
Using DAX operators4m 35s
-
Surveying DAX functions2m 35s
-
Adding calculated columns2m 11s
-
Adding calculated fields3m 7s
-
-
11. Visualizing Power View Data Using Matrices, Cards, and Tiles
-
Starting out with Power View2m 16s
-
Creating a card3m 30s
-
Creating a tile3m 18s
-
Filtering Power View objects3m 31s
-
-
12. Visualizing Power View Data Using Charts and Maps
-
Creating a pie chart2m 7s
-
Creating a line chart2m 40s
-
Creating a map2m 31s
-
Creating chart multiples2m 38s
-
Conclusion
-
Next steps54s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating a PowerPivot PivotTable