Pivoting a PivotTable report

- The real power of a PivotTable 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 a PivotTable. In this movie, I will show you how to pivot your data. My PivotTable is set up very simply: I have the years as my row headers and quarters as my column headers, and I have room revenue as the data for the body of the PivotTable. If I want to pivot the PivotTable, I need to change the order of the fields in the PivotTable Builder.

So I'll go over and activate that. The PivotTable's organization is based on years as the row headers and quarters as the column headers. If I wanted to subdivide my data by year and then by quarter, I could drag Quarter down below Rows, and you see that I have more of a tabular format. I can also drag Year to the Columns header, and I have essentially transposed what I had before. This organization, to me at least, reading horizontally, focuses on quarters and see how they compare year on year, whereas the previous organization, emphasizes years.

If I want to change the organization, I can drag Quarter back to the Field List, and then add Month below Year, so I'll drag the Month field below Year, and now I have my tabular data again, and it's a summary of data across both room types by month for the years, with subtotals and a grand total at the bottom. I can also then add Room Type to the Columns area to see how revenue breaks down across the Cambridge and Piccadilly room types, which are the only two room types listed in my original data set.

But one thing to note is that some organizations don't make a lot of sense. So let's say that I have this organization with year and month providing my row headers and room type providing column headers, and I drag Quarter to the Columns area and put it above Room Type. You can see that I have my data broken down by quarter, but the design is extremely inefficient, or rather the arrangement is extremely inefficient, so I have a lot of white space, because April, May, June, and the other months, except for January, February, and March, aren't in quarter number 1.

Then we have quarter number 2, quarter number 3, and behind the PivotTable Builder is quarter number 4. So if I wanted to include Quarter, then I would need to find another way to do it. So I'll drag Quarter back to the Field List. One thing to note is that if the PivotTable Builder dialog box doesn't appear, say, for example, if I close it, you can redisplay it by going to the PivotTable Analyze contextual tab that appears when any PivotTable cell is selected, and click the Field List button.

Doing so brings the PivotTable Builder back, and you can work with your PivotTable normally.