By default, PivotTables allow you to filter a given field based on either labels (text) or values, but not both. Want to show customers named "Tim" who purchased at least 10 bananas? Not with default filters, you won't. But not to worry. In this tip I show you a simple way to activate multiple filters in your pivots, and use both text- and value-based criteria to explore your data.
- [Instructor] All right, time to share one of my favorite … Pivot Table pro tips, applying multiple filters … to a single Pivot Table field. … Now, by default, you can either apply label filters, … which are based on text, or value filters, … which are based on numbers, to a single given field … in a Pivot Table, but not both at the same time. … So, in other words, if you had a label filter applied, … and then added a value filter, that new value filter would … overwrite or wipe out the existing label filter. … So to change this, what we're going to do is head to our … Pivot Table Options, we're going to drill into our … Totals and Filters tab and check this box that says, … allow multiple filters per field. … Now, as you might expect, what this will allow us to do … is apply filters that are based on both text and value … attributes at the same time. … So, in this case, what we're going to do is filter down to … titles that end in the number two, which is a label filter, … and also drove a certain volume of revenue, …
Note: The instructor uses Office 365 ProPlus. Some features may not be available in all versions of Excel.
- Customizing a PivotTable field list
- Grouping dates and values
- Enabling multiple filters
- Conditional formatting
- Refreshing source data
- Adding custom sort lists