Join Curt Frye for an in-depth discussion in this video Pivoting a PivotTable, part of Excel 2016: Pivot Tables in Depth.
- The real power of a pivot table comes out when you want to re-arrange 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. I will show you how to re-arrange or pivot your data, and as my sample file, I will use the pivoting Excel workbook which you can find in the chapter one folder of your exercise files collection. I've created a relatively simple pivot table with years as my column organization.
So I have 2014 and 2015 and room type for Cambridge and Piccadilly as my row headers. If I want to change the outline or the organization of the data then I can do that by dragging a field header from one area to another. For example, let's say that I wanted to create a data list or at least an organization like a list that displayed year and then room type. To do that I could drag year from the columns area to the rows area.
So what I'll do is position my mouse pointer over year, and then holding down the left mouse button drag year, which you can see is now green because it's selected, over to the rows area. And when it's in a position where it can be dropped you will see a green highlight and also the mouse pointer changes its image. If I were to drop year below room type the green line would appear there, but in this case I want to drop it on top of it at a higher level of organization. So I'll move the mouse pointer up, and when the green line appears I'll release the left mouse button and the organization of the pivot table changes.
I could also change the organization by putting room type over in the columns area. So I'll drag room type over and when I release the left mouse button you can see that the organization changes. I can also add another level of organization or another level of detail to either the row or columns area. In this case I have my years, but I also have months available in the month field which you can see in the field list. So if I want to add month to the rows area below year I can drag it down below year and release the left mouse button, and there you can see the pivot table organization has changed again.
I still have years as a row header and room type as my column header, but now I have my data broken up by month. And what allows me to do that, is the structure of the underlying data list. So if I go back to sheet one you can see that I have individual values for year, quarter, month, room type and revenue. And it's this level of detail that allows me to create a pivot table based on the years, quarter, room type, and revenue, any of those fields that I care to include.
One final thing to note about pivot tables is that updates can occasionally take a long time. So for example if you're working from an extremely large data source, perhaps millions of rows of data or larger which is certainly possible in today's business environment, you might want to defer layout updates so they don't take as much time as they would otherwise. To do that go to the bottom of the pivot tables field task pane and check the deferred layout update box.
Now any changes that you make to the pivot tables organization won't be reflected until you click the update button to the right. So if I remove month from the rows area by dragging it up to the fields list, you see that the organization didn't change, at least in the way the pivot tables visualized. And I'll drag the quarter field down below year in the rows area and click update. And when I do, all of the updates that I have made are displayed in the pivot tables organization.
If I want to allow immediate updates again I can clear the defer layout update check-box. Changing a pivot tables arrangement shifts the data's emphasis enabling you to examine the data from different perspectives quickly and easily.
- Identify how to format data for use in a PivotTable.
- Discover how to create a new PivotTable.
- Define how to connect to data sources.
- Evaluate and consolidate data from multiple data sources.
- Identify how to create calculated fields.
- Summarize field data.
- Review PivotTable sorting.
- Review PivotTable filters.
- Discover how to work with Excel slicers.
- Recall how to format PivotTables with styles and formats.
- Assess how to build PivotCharts.
- Assess how to run macros.
- Evaluate how to create a PivotTable using the data model.