Pivot Tables - Filter Behavior, Limiting Filter Drop Downs Based on Previous Filters

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.

Dora

Posted 2013-12-16T20:29:30.933

Reputation: 11

Answers

2

  1. Create a Pivot table with fields as required.
  2. Click on any cell in the table and insert a slicer (click on the insert slicer button that appears on the ribbon). You'll be asked which fields are to be added to the slicer, so here you should add all the fields on which you want to filter.
  3. Slicers will be displayed, and when you select a value in a slicer, the next slicer will display the options available under the first slicer in blue, and the rest in white.

Have a look at this screenshot and be sure to also check out Microsoft's documentation on the subject.

ruchika

Posted 2013-12-16T20:29:30.933

Reputation: 21