Join Curt Frye for an in-depth discussion in this video Pivoting a PivotTable, part of Excel for Mac 2016: Pivot Tables in Depth.
- [Voiceover] The real power of the 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 using a pivot table. In this movie, I will demonstrate how to pivot a pivot table, and as my sample file I will use the Pivoting Workbook which you can find in the Chapter One folder of your exercise files collection. This workbook contains a pivot table on Sheet Two, and as you can see, it's laid out so that the years are the column headers, 2014 and 2015, and the two room types that I've summarized, Cambridge and Pickadilly are the row labels.
Let's say that I want to do some time-based analysis and to do that I'll flip the 2014 and 2015, the years, to the rows area and room types to the columns area. I can do that by dragging a field from within the pivot table builder to a new area. So I'll click the pivot table builder to activate it. And I will drag the year field, and you can see its header here, from the columns area to the rows area. So I'll drag it over to the side and put it below room type, and when I release the mouse button, you can see that it appears.
And because I drag year below room type, we see room type first in the body of the pivot table, and then subtotals for the year. Now I can drag room type to the columns area, and I'll do just the reverse of what I did before, drag the field from rows to columns, and that changes the organization. I have other fields available to me, so for example if I look in the field name list, and I'll scroll up, you can see that I have here and the checkmark indicates that field is within the pivot table right now.
Quarter and month are not. And then room type and revenue. Let's say that I bring month down, and I want to separate my years by month. To do that, I can drag the months field to the rows area below year, and my pivot table organization changes. Basically what happened is that I added another level of detail. I broke the data down by year and then by month. And I could reverse that if I wanted to, if I took year and dragged it below month within the rows area, then I would be able to compare January for 2014 to January 2015 with the numbers right next to each other, and I could do the same thing for the other months as well.
However, in most cases you probably want to look at your data based on the hierarchy of dates, and that means month would go below year. So I'll drag it down, and you can see that the organization that I had before is restored. Changing a pivot table's arrangement shifts the data's emphasis, enabling you to examine the data from different perspectives quickly and easily.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Creating a Recommended PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats