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.
PivotCharts can summarize huge data collections. But many times you want to limit the data displayed in a given category. Just as you can limit the data displayed in a PivotTable, you can also limit the data summarized by a PivotChart by creating a filter. There are three main ways you can filter the contents of a PivotChart: by selection, by rule and by using a search filter. Filtering by selection means that you display a list of the values in a field and select the values you want to display. Filtering by rule means that you create criteria that Excel uses to select which values to display.
Finally, creating a search filter means that you type in a character string that you want to appear in all of the labels that appear in the body of the PivotChart. You can create filters for a PivotChart by using the PivotTable Field List and that procedure is exactly the same as if you were creating a filter for a PivotTable. So let's say for example that I wanted to create a filter where I only displayed values for January, April, July, and November. To do that I can move the mouse pointer over the Month field, click the down arrow, and then using the selection list, I can select then the months that I want.
So off Select All and do it for January, April, July, and November. With those selections in place I can click OK and Excel updates my PivotChart to use just those values. If I want to remove the filter then I can move back into the PivotTable Field List, click the down arrow, and click Clear Filter From "Month". Now let's say that I want to filter by rule. Say that I want to display results only for quarters three and four.
In other words, any quarter with a number greater than two. To do that, I will pivot the PivotChart by removing the Month field and adding the Quarter field to the Axis Fields area. So now I have my results by quarter for each of the years 2009 and 2010. To create that rule I will click the Quarter field header's down arrow and then point to Label Filters, click Greater Than, and then I want any quarter with a value greater than 2, so I'll type 2 in the box to the right, verifying that "is greater than" appears in the Comparison Operator box. Click OK and Excel updates my PivotChart based on my filter.
I will once again clear that filter, going into the PivotTable Field List box and clicking Clear Filter. Now let's say that I want to create a search filter. To do that I will bring the month back in. So I'll take Quarter out and add the Month field back into the Axis Fields area. Now let's say that I want to filter for any month that includes the letters ER in the name of the month. So to do that, I will click the Month header's down arrow and then in the search box I'll type ER.
When I do Excel updates the contents of the search list, indicating that September, October, November, and December will all appear when I create my filter. Everything looks good so I can click OK and Excel filters the PivotChart, and if you want you can always create multiple filters. So let's say that I was only interested in the values for 2010. I can go up to Year, click the down arrow, and clear the Select All button, and check 2010, click OK, and I see the results for September, October, November, and December of 2010.
Creating filters gives you control over the values that appear in your PivotChart. When you want to narrow your focus and examine a subset of your data, PivotChart filters enable you to do just that.
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.