Abstract
Transcript
When filtering a pivot table, you might want to filter the same field by Label and Value. By default, a pivot table won’t allow multiple filters on the same field. However, you can change a setting to enable this option when you need to.
Let’s take a look.
Here we have a pivot table that shows Sales and Orders by product, sorted by Sales. Let’s filter the products to show only the top 3 products by Sales. Notice the Value Filter is now active.
Now, let’s add a Label Filter to show only products that end with the word “chocolate”. Notice that when we click OK to apply this filter, we see all products that end in chocolate, as expected, but the Value Filter is removed in the process. If we check the filter settings, we see the Label Filter is active, but the Value Filter is not.
By default, a pivot table does not allow multiple filters on the same field.
To enable multiple filters per field, we need to change a setting in the pivot table options. Right-click in the pivot table and select PivotTable Options from the menu. then navigate to the Totals & Filters tab. There, under Filters, enable “allow multiple filters per field”.
Back in our pivot table, let’s enable the Value Filter again to show only the top 3 products by Sales. Now we have a pivot table that shows the top 3 products by Sales that end in “chocolate”. If we check the filter settings, we can see that both the Value Filter and the Label Filter are active.
It’s important to understand that the Multiple Filters setting allows you to add both a Value filter and a Label Filter to a field, but not, for example, two Value Filters or two Label Filters. If we try to add an additional Value Filter to filter on products with orders greater than 100, the new Value Filter is applied and the Label Filter stays active, but the original Value Filter - top 3 products by Sales - is lost.
