Slicers - override the pick list
Slicers are a great addition! Currently, slicers get their pick list from the Table / Pivot Table, of course. I'd like to be able to override this with a list of my own - from a range of cells.
Debra Ann commented
I got on this site to add my frustration with not being able to limit the list on a slicer and saw this thread. Although some of the people on the thread seem to be wanted to add the list even if it is not there, I want to be able to limit the list from the ones that are there. However, the problem is still the same. I was hoping there was a way to filter on the column and then create a slicer that would show only the names in the filtered list. This does not work. So either having this ability or being able to create my own slicer list and select the column I want to use the slicer on would be awesome. Everyone has told me to create a new column in the table that the pivot table is made from; however, this spreadsheet comes weekly from upper management and the idea was to be able to integrate my pivot table and dashboard with the new file each week. Having to remove the ones I do not want before I create the pivot table would be nightmare.
jeffrey Weir commented
Yeah, I'm staggered that you can't do this natively. I wrote some fast workaround code at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
Kenneth Barber commented
You can always insert dummy entries into your table to force items, that wouldn't otherwise appear, to appear. If you don't want to keep them, then right-click your PivotTable and go PivotTable Options, Data tab. Change the dropdown box to "Automatic" or "Max". Then when you delete your dummy entries, they should still appear in the Slicers.
Chuck Trese commented
This would help in cases where there is a set list of possibilities, but one or more of the possibilities isn't in the table.
As one example, I have a long list of items (defect reports) that have a 'state' attribute. the state can be 'draft', 'submitted', 'discarded', 'integrated', 'closed', .... When I create a Pivot Table, then a Pivot Chart, and then add slicers for the chart, the slicer only shows the options that are actually in the table. For example, if there aren't currently any 'closed' items, then i can't filter for closed items. I want the filter options (i.e. slicer's "pick-list") to remain constant, even when some states are not included in the table. I tried using custom lists, but slicers only allow me to SORT the pick list based on the custom list, but doesnt change the pick list itself.