Merging filters in PivotTable

Copper Contributor

Hello

 

I have an Excel file with PivotTable Fields. In one of the fields it has 50 filters that can be selected. I only want 5 of these filters to work with. What I would like to do is select those 5 filters and then merge/combine them into 1 new filter. Does anyone know how I can do this?

 

Thanks in advance

4 Replies

@S_YOffice 

 

Do I understand you have a Column with 50 unique items in it and you'd like to group those into a single item that you can then use to filter a Pivot Table?

 

If you're using Power Pivot then:

If that's the case then using a mapping table to group the 5 items into the 1 item and the 45 items into "other" could work, and then either use that as a Table with a Relationship in the model or use Power Query to merge the new table definitions onto the the side of the existing table (option 2 better if your 50 item table is currently being used as a lookup table rather than the fact table)

 

Wyn

[MVP]

 

If you're not using Power Pivot then you could add an extra column to the source table of your Pivot and copy down an XLOOKUP or INDEX MATCH formula that references a table that classifies your 5 items and 45 other items

@Wyn Hopkins 

Thanks for your response. I already have a Pivot Table that was sent to me, that has pre-set filters. Under one of the filters is 50 categories, I want to combine 5 of those 50 into a single category. I'm unsure how to use a mapping table, any help would be great. Thanks

@S_YOffice 

I'm not quite clear on what you mean,  but you may be able to Ctrl select multiple items and group them and then use them as a filter

 

image.png