When you work with a PivotTable, you'll often find that you want to locate data that contains a particular string of characters. For example, if you have monthly data, you might want to look for data that only occurs in the months of June and July. Because both of those months have the characters J-U next to each other, you can create a filter that looks for exactly those characters. This is a new capability in Excel 2010 and is called a search filter. To create a search filter, you go over to the PivotTable Field List task pane, and then you move your mouse-pointer over the field name that you want to filter.
In this case, we'll filter by Month, then click the downward-pointing black triangle that appears and then type the character string that you want to search for in the Search box. In this case, I'm looking for the characters J and U, and when I type that in, you'll see that Excel indicates the values that would be selected if we were to apply this filter. So right now, we have June and July. When I click OK, Excel applies the filter to the PivotTable.
Let's say that I'm interested in June, July, but also August. I can create a second search filter that adds on to the filter that I've already created. To do that I'll go back into the Filter menu for the Month field and then in the Search box I will type AU. And all the characters I have typed so far occur at the beginning of the month name, JU for June and July and AU for August. But the search string that I type in can occur anywhere within the field name. So for example, if I were to type in ER, then I would get December, October, November and so on.
So I have typed in AU and August is displayed. If I check the Add current selection to filter box and then click OK, Excel adds the month of August to the filter. If I want to remove the filter, I can click the Month header's down-arrow and click Clear Filter From Month and when I do, Excel removes the filter. The filters I have created so far using the Search capability are the equivalent of what is called a contains filter. In other words, we're looking for values that contain the letters JU in that order.
If you want to create the inverse of this type of filter, you can create a does not contain filter. So let's say that I want to exclude any month that has the characters ER in its name. To do that, I can click the Month header's down arrow, point to Label Filter, because I'm filtering based on values in the label area, and then in the list of filters that are available, I can click Does Not Contain. Now, I can type in the characters ER, and again they can occur anywhere in the Month name, click OK, and Excel lists the data based on that filter.
Again, to remove the filter, just click the Month header's down arrow and click Clear Filter From Month. Search filters help you limit the data in your PivotTable to those values that contain a specific text string. If you find you want to exclude items that contain a common text string, you can also create a Does Not Contain filter to limit your data that way.
Author
Released
4/19/2011- Formatting data for use in a PivotTable
- Connecting to an external data source
- Refreshing a data source
- Adding, removing, and positioning subtotals and grand totals
- Creating a calculated field
- Grouping PivotTable fields
- Clearing and reapplying PivotTable filters
- Applying field styles
- Formatting cells
- Creating a PivotChart
- Printing PivotTables and PivotCharts
- Creating relationships between tables in a PowerPivot model
- Using the DAX language for advanced summaries in PowerPivot
Skill Level Intermediate
Duration
Views
Related Courses
-
Excel 2010: Tips, Tricks, and Shortcuts
with Dennis Taylor3h 43m Intermediate -
Excel 2010: Macros
with Dennis Taylor2h 44m Intermediate -
Excel 2010: Charts
with Dennis Taylor3h 38m Intermediate -
Excel 2010: Financial Functions
with Curt Frye2h 18m Intermediate
-
Introduction
-
Welcome52s
-
-
1. Creating and Pivoting PivotTables
-
Introducing PivotTables4m 2s
-
Creating a PivotTable4m 20s
-
Pivoting a PivotTable3m 47s
-
Configuring a PivotTable3m 22s
-
Managing PivotTables3m 26s
-
-
2. Summarizing PivotTable Data
-
Creating a calculated field2m 27s
-
Grouping PivotTable fields3m 17s
-
3. Sorting and Filtering PivotTable Data
-
Creating a custom sort order2m 48s
-
Formatting slicers3m 43s
-
4. Formatting PivotTables
-
Creating a PivotTable style4m 37s
-
5. Applying Conditional Formatting to PivotTables
-
6. Creating and Manipulating PivotCharts
-
Creating a PivotChart3m 29s
-
Pivoting a PivotChart3m 5s
-
Filtering a PivotChart3m 45s
-
Formatting a PivotChart3m 35s
-
Changing a PivotChart layout3m 14s
-
-
7. Printing PivotTables and PivotCharts
-
Printing a PivotTable4m 2s
-
Printing a PivotChart1m 55s
-
-
8. Manipulating PivotTables Using Macros
-
Running a macro5m 57s
-
9. Getting Started with PowerPivot
-
Introducing PowerPivot2m 8s
-
Importing PowerPivot data3m 14s
-
Managing table columns4m 1s
-
-
10. Working with DAX Expressions
-
Introducing the DAX language2m 58s
-
Using DAX operators4m 44s
-
Surveying DAX functions2m 40s
-
Using aggregate functions4m 24s
-
-
Conclusion
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Filtering a PivotTable using a search filter