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 in the PivotTable you might want to display all of the rows in the PivotTable, but change the order in which they appear. So let's say for example that I have PivotTable here that contains sales data for two companies, broken down by month for the years 2009 and 2010. The current PivotTable arrangement organizes the data first by year and the years are sorted in numerical order, so 2009, 2010, and then the months are sorted January, February, March and so on, in order of the months as they occur in the year, as opposed to an alphabetical order.
But let's say that I wanted to sort the data inside the PivotTable from highest to lowest. So let's say for example that I wanted to find the months where FirmB had the highest sales within the year 2009. To do that I click any cell in the field and in the column that I want to sort and then on the Options contextual tab of the ribbon in the Sort and Filter group, I can click one of three buttons. I can click either Sort Smallest to Largest or Sort Largest to Smallest or I can create a custom sort by clicking the Sort button.
In this case, I'll click the Sort Largest to Smallest button. It goes from Z to A. Click that and you see that Excel has sorted the PivotTable data within each year based on the months for FirmB that have the highest sales values. If I wanted to I could do the same thing for FirmA. So click any cell in the FirmA column and then let's say that I want to have the smallest values on top. I can click these Sort Smallest to Largest button and sort in that direction. If I want to undo a sort, then I can press Ctrl+Z or click the Undo button on the Quick Access toolbar, but I will press Ctrl+Z once to undo the first sort and then Ctrl+Z a second time to undo the second, and now my data is back in its original order.
Sorting a PivotTable moves the data you want to highlight to the top of the PivotTable, enabling you to concentrate on the values that you want to focus on.
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.