How To Sort Excel Slicer By Number

%3CLINGO-SUB%20id%3D%22lingo-sub-2695179%22%20slang%3D%22en-US%22%3EHow%20To%20Sort%20Excel%20Slicer%20By%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2695179%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20multiple%20data%20in%20this%20order%20as%20you%20see%20below%3A%3C%2FP%3E%3CTABLE%20width%3D%22138%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22138%22%3EStories%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.3%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.4%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.5%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.6%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.7%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4.8%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6.3%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.3%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.4%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.5%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.6%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.7%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.8%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.9%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.3%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.4%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.5%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.6%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11.2%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13.1%20Story%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20I%20take%20it%20to%20slicer%2C%20I%20see%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-28%20190650.png%22%20style%3D%22width%3A%20156px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F306417i0321BC061B24C52B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-28%20190650.png%22%20alt%3D%22Slicer%20Image%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESlicer%20Image%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eeven%20if%20the%20slicer%20is%20set%20to%20sort%20data%20from%20source%20order.%20I%20don't%20understand%20why%2C%20please%20guide%20me%20on%20how%20to%20fix%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2695179%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2695229%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Sort%20Excel%20Slicer%20By%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2695229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140119%22%20target%3D%22_blank%22%3E%40Pranav_R_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20structured%20table%20slicer%20you%20may%20change%20sorting%20order%20by%20adding%20custom%20list%20as%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.thespreadsheetguru.com%2Fblog%2Fhow-to-customize-order-of-excel-slicer-buttons%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHow%20To%20Customize%20The%20Order%20Of%20Your%20Excel%20Slicer%20Buttons%20%E2%80%94%20TheSpreadsheetGuru%3C%2FA%3E%26nbsp%3B%2C%20don't%20think%20that's%20feasible%20in%20this%20case.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20that's%20PivotTable%20slicer%20you%20may%20add%20helper%20column%20in%20data%20model%20with%20stories%20numbers%20and%20sort%20stories%20by%20these%20numbers.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOtherwise%20it%20is%20sorted%20as%20source%20data%20will%20be%20sorted%2C%20i.e.%20as%20texts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2695416%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Sort%20Excel%20Slicer%20By%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2695416%22%20slang%3D%22en-US%22%3EIt%20is%20a%20PivotTable%20slicer.%20What%20is%20a%20helper%20column%20and%20how%20to%20add%20that%3F%3CBR%20%2F%3ECan%20you%20explain%20a%20little%20bit%20more%20about%20it.%20Please%20explain.%20Thanks%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have multiple data in this order as you see below:

Stories
1.1 Story
1.2 Story
2.1 Story
3.1 Story
3.2 Story
4.1 Story
4.2 Story
4.3 Story
4.4 Story
4.5 Story
4.6 Story
4.7 Story
4.8 Story
5.1 Story
6.1 Story
6.2 Story
6.3 Story
7.1 Story
8.1 Story
9.1 Story
9.2 Story
9.3 Story
9.4 Story
9.5 Story
9.6 Story
9.7 Story
9.8 Story
9.9 Story
10.1 Story
10.2 Story
10.3 Story
10.4 Story
10.5 Story
10.6 Story
11.1 Story
11.2 Story
12.1 Story
13.1 Story

 

But when I take it to slicer, I see something like this:

Slicer ImageSlicer Image

even if the slicer is set to sort data from source order. I don't understand why, please guide me on how to fix this.

 

Thanks a lot

4 Replies

@Pranav_R_ 

If that's structured table slicer you may change sorting order by adding custom list as here How To Customize The Order Of Your Excel Slicer Buttons — TheSpreadsheetGuru , don't think that's feasible in this case.

 

It that's PivotTable slicer you may add helper column in data model with stories numbers and sort stories by these numbers.

 

Otherwise it is sorted as source data will be sorted, i.e. as texts.

It is a PivotTable slicer. What is a helper column and how to add that?
Can you explain a little bit more about it. Please explain. Thanks

@Pranav_R_ 

First you need add data to data model, check this setting creating PivotTable

image.png

If add Stories to it now it'll be like

image.png

Open PowerPivot and add another column Story to the table

image.png

add storyNumber column, right click on it and Hide from Client Tools

image.png

Select Story column, Sort by Column and use stotyNumber

image.png

Hide Stories column.

Now you may use Story column and slicer on it

image.png

Please check in attached.

@Pranav_R_ 

Sorry, formula for storyNumber column is

=LEFT( Table1[Stories], FIND(" ", Table1[Stories]) -1 ) + 0

didn't update screenshot in previous post.