Join Curt Frye for an in-depth discussion in this video Pivoting a PivotTable, part of Excel for Mac 2011: Pivot Tables in Depth.
- [Voiceover] The real power of a pivot table comes out when you want to rearrange your data dynamically. A task that would take several minutes if done by hand, takes just a few seconds when you summarize your data in pivot table. In this movie, I will show you how to rearrange your data dynamically, which is called pivoting. My sample file is the Pivoting workbook, and you can find it in the Chapter One folder of your exercise files collection. This workbook contains a pivot table summarizing hotel revenue data, and in addition to the revenue column, I have columns for year, quarter, month, and room type.
Currently, I have my rooms in the Row Labels area, my room type and the years in the Column Labels area. I have year for columns and room type for rows. If I want to reorganize the data, perhaps going by room type and seeing a breakdown for the revenue for each year, but in a linear or tabular fashion as opposed to a cross tab like I have here, I can drag the year field from the Column Labels area, so it's below room type.
And you can see that the data has been reorganized, I have Cambridge 2014 and 2015 and Piccadilly, again for 2014 and 2015. That one switch shows two different things. The first is that changing around column header position changes the data's arrangement, and the second, is that adding a second row or column header creates subdivisions within the data. And you can see here that I have two different organization levels, I have room type at the top, and year at the bottom.
And of course, that organization is matched here, in the pivot table builder. If I want to change the arrangement I can move fields around in the pivot table builder, for example, if I want to drag room type to the Column Labels area, I can do that, and I have my years broken down by room type. I can add additional fields from the field list by dragging them down to a particular area, so let's say that I want to drag month down, and put it below year in the Row Labels area. So I go to the field list and drag month down below year, and you can see how the organization changes.
If I want to get summaries by quarter, then I can do that as well. I'll drag quarter between year and month, and there you can see the summaries. And again, I have subtotals based on quarter, and also based on year. And if I scroll down you can see how the rest of the pivot table appears. And of course, if I want to remove a field, for example, to take quarter back out, I can drag it from the Row Labels area back to the builder or to the file list, or field list, and my data returns to the way I had it before I added the quarter field in the first place.
Changing a pivot table's arrangement shifts the data's emphasis, which enables you to examine the data from different perspectives quickly and easily. I encourage you to work with your data, and find the perspective that best meets your needs.
- Creating a PivotTable
- Refreshing PivotTable data
- Managing totals
- Creating calculated fields
- Grouping PivotTable fields
- Sorting and filtering PivotTable data
- Printing PivotTables