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.
You can use PivotTables to summarize huge data collections, but many times you will want to limit the data displayed within a given category. For example, if your company's operations are divided into four regions, you might want to display just those results for one of the regions. You can limit the data displayed in a PivotTable by creating a filter. There are three main ways to filter the contents of the PivotTable field: by selection, by rule, and by search term. Filtering by selection means you display a list of values in the field and select the values you want to display.
So for example, let's say that I have the results here in those PivotTable that displays sales for four different regions, East, North, South, and West, and let's say that I only want to display those values for the North and West regions. To do that I'll click any cell on the PivotTable and then over in the PivotTable Field List I move my mouse pointer over the header of the field that I want to filter, and then click the downward pointing black triangle that appears, and when I do, the Sort and Filter menu appears.
In this case I want to select the values that I want to appear and those are North and West. So I can go into the Filter list, clear the Select All checkbox, and then check North and scroll down, and check West. With those two values selected I can click OK and Excel applies the filter limiting the data to only North and West. If I want to edit the filter, then I can display the Filter menu again, and again that's over in the PivotTable Field List, and then let's say that I want to display East and West. So I'll clear the checkbox next to North and checkbox next to East, click OK, and Excel updates the filter.
If I want to clear the filter, then I can go back into the Filter menu and on the menu a little bit higher up above the select list, I can click Clear Filter From Region and the item in double quotes then will change, based on the fields that you're working with. So in this case it's Clear Filter From Region. Click it and Excel restores the PivotTable to its unfiltered state. You're not limited to creating one filter at a time. So let's say for example that I want to display the values for the North and West regions.
So I will click Region, click the down arrow that appears next to it, clear Select All, and then check the North and West boxes and click OK. And then let's say that I only want to see the values for January and February. So then I will move the mouse pointer over the Month header, click the downward pointing black triangle, clear the Select All checkbox, which removes the checkmarks from all the boxes in the list, and then select January and February.
When I click OK, Excel applies the second filter to the table. Filtering by selection gives you pinpoint control over the values that appear in your PivotTable. You should use this type of filter when you want to display or exclude a few values from the display.
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.