Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
PivotTables help you summarize large amounts of data, but you can always limit the data that appears by creating a filter. If the data you want to display in your PivotTable fits the rule, such as all values greater than 1000, you can define that rule and use it to filter your PivotTable. To filter a PivotTable using the values in the field, you can click any cell in the body of the PivotTable and then go over to the PivotTable Field List task pane and position your mouse pointer over the field that you want to filter by. In this case, I want to filter by month, so I will move the mouse pointer over that field, and then on the right edge click the down arrow that appears and you can select the type of filter that you want to create.
You can create two different types of filters: a label filter or a value filter. As the name imply,s a label filter lets you filter on the values in the label area. So for example, in this PivotTable we have Years, 2009, Quarters, 1 2 3 and 4, and then Month, January, February, March and so on. If we want to filter based on one of those label values, we would create a label filter. However, if we wanted to create a filter based on the values within the data area of the PivotTable, then we can create a value filter.
So for the Month field, I will create a value filter and then I can select the type of filter that I want to create. So in this case I want to display any month that has a total of more than $200. So I will click Greater Than and then in the Value Filter dialog box, I have Sum of Revenue is greater than, and then I can type in my criteria, which is 200. When I click OK, Excel applies the filter to the PivotTable and you can see that it only displays rows where the grand total is more than 200.
To remove the filter, I can go back into the PivotTable Field List, click the Month header, and then click Clear Filter from Month. Now let's say that I want to filter based on label values and for this example I want to display only those values for Quarters 3 and 4, in other words Quarters with a value greater than 2. To do that I will filter based on the Quarter label. So back in the PivotTable Field List, I will position my mouse pointer over the Quarter field, click the down arrow, point to Label Filters and then click Greater Than.
I want to display any Quarter that is greater than 2 so I will type 2 in the second box and click OK. When I do, Excel filters the PivotTable so it only displays results from Quarters 3 and 4. Filtering PivotTable data helps you gain insight into your data by focusing the display on the values you want to examine. You should experiment with the many types of filters available to you, so you will know which to use to answer particular questions about your data.
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.