Forum

Notifications
Clear all

Sorting a slicer

8 Posts
3 Users
0 Reactions
114 Views
(@jycccwjc)
Posts: 64
Estimable Member
Topic starter
 

Mynda,

How can I sort the slicer (see the attachment) by K,1,2,3,4,5,6,7,8,9,10?

Thanks!

Jim

 
Posted : 14/09/2019 3:10 pm
(@fravis)
Posts: 337
Reputable Member
 

Can't find the attachment Jim, please try uploading again.

Frans

 
Posted : 16/09/2019 3:38 pm
(@jycccwjc)
Posts: 64
Estimable Member
Topic starter
 

Frans,

 

Here you go. ThanksSlicer-Sorting.JPG

 
Posted : 17/09/2019 6:57 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Jim,

If the numbers are formatted as Numbers then they will sort correctly. It looks like your source data is formatted as text, hence the sorting problem. If you can't change the format to numbers, then the other solution is to add a leading space in front of the numbers less than 10. This leading space tip was shared with my by fellow Excel MVP, Ingeborg Hawighorst.

Mynda

 
Posted : 17/09/2019 6:05 pm
(@jycccwjc)
Posts: 64
Estimable Member
Topic starter
 

Mynda,

The numbers along with K are grades such as Kindergartner, 1st grade, 2nd grade up to 10th grade. Which option is better to sort K, 1, 2, 3, 4,....10?

 
Posted : 18/09/2019 12:28 pm
(@mynda)
Posts: 4761
Member Admin
 

If you want K first in the list then you need to set up a custom list with the values and their sort order: File > Options > Advanced > General > Custom Lists. Then select this sort method in the Slicer settings.

Mynda

 
Posted : 18/09/2019 5:36 pm
(@jycccwjc)
Posts: 64
Estimable Member
Topic starter
 

Mynda,

Sorry to keep bothering you. I already have a custome sort list. The slicer is under a Power Query and Pivot Table. How can I add this sort option to the slicer as I only see ascending and descending or "sort data source order" options?

 
Posted : 19/09/2019 10:54 am
(@mynda)
Posts: 4761
Member Admin
 

Ah, Power Pivot Slicers work differently. For them you need to add a numeric column to the table containing your Slicer items (K-12) and set that as the 'Sort by' column in the modelling tab.

Mynda

 
Posted : 19/09/2019 5:24 pm
Share: