Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
When you summarize your data using a PivotTable, Excel organizes the data based on the order of the fields and the Columns area and in the Row area. You can then use the expand and collapse controls at each organizational level to hide the details for those rows. So for example in this PivotTable, the Row area is arranged by Company, Year and Month. If I wanted to hide all of the details for the months within the year 2009 for example, then I could click the Collapse control here beside 2009, and when I do I just see the summary for 2009 as opposed to the individual months.
If I click the expand control, then the details reappear. Now the months represent the lowest level of organization so you can't hide them individually. As the PivotTable stands now, you can only hide the monthly results by hiding every month for an entire year. But if you do want to show or hide groups of months, you can do so by creating a group. A group is a user-defined set of rows that you can expand or collapse as desired. To create a group, select one set of PivotTable rows that contain the values you want to include in the group.
So for example, let's say that I wanted to select February, March, and April. I move the mouse pointer, which you can see here, over the left edge of one of the row labels. When it changes to a right pointing black arrow, then I know that it is in position to select rows. So I will click the left mouse button and drag down, and I will select February through April. Now you will notice that Excel is selecting February through April 2009 and it was also selecting February through April of 2010.
So regardless of which months you select, the selection would be the same for every year within the PivotTable. Now with those of PivotTable rows selected go to the Options contextual tab and then in the Group group, click Group Selection. When you do, Excel creates a custom group for those months. If you want to hide the details of that group and just see a summary, then you can click the collapse control beside the name Group1 and hide those details.
If you want to bring them back, you can click the expand control to bring them back. Now the name Group1 isn't very descriptive, so if you want to edit that name, you can click the cell that contains the name Group1 and then select the value on the formula bar and it changes. So for example, it might be Sale Month, and press Enter. When you do, you change the value of the label. Now note that normally double- clicking a cell would allow you to edit the value within the cell, but in this case double-clicking the cell that contains the label Sale Months would collapse the group and double-clicking it again would expand it.
If you want to remove a group from the PivotTable, in other words to ungroup the selection, you can click the header cell and then on the Options contextual tab, in the Group group, click on Ungroup. Grouping PivotTable fields enables you to hide or display rows of data that belong together without creating a new field in your data source. Be sure to change the name of the group to something more descriptive than Group1, and when you're done with your analysis, consider ungrouping the fields to make the PivotTable's layout simpler.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.