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.
- [Instructor] PivotTables can summarize huge data collections, but many times you'll want to limit the data displayed within a given category. For example, you might want to limit your data by state, or perhaps by category. In this movie, I will show you how to create filters that limit your data. My sample file is the Filter Pivot Workbook, and you can find it in the Chapter Nine folder, of the exercise files collection. This workbook contains a PivotTable, on the Sheet1 Worksheet that summarizes orders by value, by state and then for each product category.
Let's say that I want to limit my data a little bit because, to be honest, this is a fairly busy worksheet. For example, I might only want to see values from specific states. To create that filter, I can go to the Row Labels area, which is where I have my states, as my Row Label headers, and click the down arrow. This is a filter arrow, just like you will see in an Excel table, and here I can sort by customer state, I can create filters, and I can also create what's called a selection filter.
At the bottom, I have selection boxes, basically check boxes, for each individual value, as well as Select All. So I will clear the Select All checkbox, everything goes away. Now let's say that I only want to see results for California, Colorado and Connecticut, and you can see, when I click those, that the values are added to the Worksheet. I will click away, the Filter Arrows interface goes away, and you can see those states.
You'll also notice that the Row Label Header filter arrow has a funnel icon appearing on it. That indicates that a filter has been applied. If I want to remove the filter, then I can either press Command + Z, to undo it, or I can click the Filter button and click Clear Filter and when I click away, the filter has been taken off. One thing to note is that you can create filters if there are multiple fields in the same area.
For example, let's say that I put Product Category below Customer State in the Rows area. So I'll drag Product Category from Columns, put Customer State in Rows, and I have a new PivotTable arrangement. Now I have two fields in the Row Labels area, so if I click the Row Labels filter area you'll see that I have the Select Field control at the top, I can either do Product Category or if I click it, I can click Customer State and that allows me to select which field I'm using as my filter.
Finally, let's say that you want to filter, but without changing the outline, or the layout, of your PivotTable. To do that, you can drag a Field from the Field List to the Filters area. So let's say that I only want to see orders where there was one item that was ordered. That means Quantity one. So I will drag Quantity from the Field List to the Filters area, and you can see that the Field was added, here, to the top, to the Filters area but in fact was in fact was not used to change the outline of the PivotTable.
I'll click the Fields Down arrow, and I can either search or I can use a Selection Filter, so I will clear Select All, click 1, click away, and there are my values. As you can see, 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, and as always, if you don't want to change the organization of your PivotTable, add a Field to the Filters area and use that to further narrow down by selection.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.