1
I am working in Excel 2010. I am looking for an option to change how the pivot table drop down filters behave.
In a normal Excel spreadsheet (non-pivoted), if I apply a filter to a dataset and then filter a field, the remaining field filters are limited by my first filter.
For example, if I have a spreadsheet of color names/types. I have the two fields, "category" and "color name". Categories are "colors in the rainbow" (the traditional 7, Roy G. Biv), and "other". If I filter my normal, unpivoted spreadsheet for the category "colors of the rainbow," not only does it limit my dataset to those 7 colors (red, orange, yellow, green, blue, indigo, and violet) when I then select filter in the color name field, all I see in my drop down filter list are those 7 options. The previous filter has constrained the options available.
In my experience, pivot table drop down filters show all the possible options within the dataset for a given field, even if I have constrained the data with filters or slicers in other fields. IE - in the example above, if I pivoted my dataset tabular-style, and I filtered my pivot table by category "colors of the rainbow", the pivot table would only show up with red, orange, yellow, etc, as expected. However, if I select the drop down filter for the color name field, the drop down filter would still show me all the extra irrelevant color names not relevant to the category I selected, like ochre and black. I want that drop down filter in the pivot table "color name" field to just show me the constrained set of 7 values, based on my previous category filter.
Programs like Tableau have the option to "only show relevant data" in their filters. I would like something similar for Excel pivot tables, if possible.
There are workarounds, like copying my pivot table and pasting the values and filtering them, but I am doing a huge amount of complex quality check work, and it would be a lot easier if the pivot table filters worked the way I have described.