Slicer Settings Do Not Show Custom Sort Option -- Why Missing?

[ReneeD]

Using Excel 2016 with Pivot (not powerpivot) table. Even though "custom sorting" is enabled in Pivot Table Options, the slicer settings do not show any option for applying a custom sort list. Why is this feature missing for me?

The software is latest build. thank you for any help you might provide. I have uploaded screen shot.

kind regards....

 

[Luke M]

Hi Renee,

This is just a guess, as I don't have 2016 setup yet, but if you check the 'Data Source' Option, does that perhaps mean it follows same sort as PivotTable? I could see this as a possibility as this would also let you manually re-arrange items in the PT, and then the Slicer would have same sort. In 2010, this is one of my complaints is that I can't manually re-arrange the buttons w/o having to create a custom list.

 

[ReneeD]

Hi, Luke:
thank you for your comment....interesting thought; not sure I follow exactly so bear with me. Many of my slicer fields do not appear in pivot table data (neither as row nor column), and the slicer sorting that I'd like to achieve is to order the slicer value labels so that "over 60" appears below "45-60" and the user can find values easily where a normal a-z or numeric sort isn't possible. Not really connected to how data in the pivot table is sorted, this is solely to manually arrange the order of items as they appear in the slicer itself.

Everything I can find says that excel should automatically offer a "use custom lists" option in each slicer settings dialog box, but, mysteriously, not for me!

-R

 

[Luke M]

Good point, I had momentarily forgotten that slicers don't need a corresponding part displayed in the PivotTable.

Other crazy ideas:
Delete slicer, and try again. To create, select a cell within your PivotTable, then go to PivotTable Tools - Options - Sort & Filter - Insert Slicer
See if option is available now

Create the slicer in an older version of office (if you have that access, maybe a different machine...?)

Try doing a normal A-Z sort. Alphabetically, "over 60" comes after "45-60", so it shouldn't need a custom sort. Is there some sort of PivotTable grouping going on?

 

[JojoD]

I too am having this issue in Excel 2016. I have tried deleting the slicer and recreating it. I have tested the custom sort on a simple range, and it works. However, I do not get the option in Slicer Settings to choose from a custom sort. Were you ever able to solve this?

 

[Chihiro]

Custom sort is removed from 2016 (EDIT: For slicers and PivotTables). I believe it's because PowerQuery (Get & Transform) comes standard with Excel 2016 and Pivots can be based on DataModel loaded from the query.

Workaround:
If data source is Data Model...
1. Use M Function to sort table column using array of unique items
Or
2. Create separate table with unique item list and sort index. Join that table to data source and sort based on sort index

Same principle applies to regular table. Just need lookup table with sort index and unique item list. Use formula to combine table and sort on sort index.

 

[JojoD]

Thank you Chihiro for the concise yet thorough response. I appreciate it and will try what you suggested.