Slicer Sorting Simplified: The quick and effective way that actually works!

[@RichardJones73]

I hate saying it after every video you do, but you sir are a genius

[@ExcelOffTheGrid]

I'm just sharing what I know or discover. So if that makes me a genius then I'll take it. 😁

[@bonmonil]

briliiant trick, I broke my head trying to find a similar solution, this is far more simple it actually works 😀

[@ExcelOffTheGrid]

Yes, it can be tricky to get your head around. Hopefully this method will work for you.

[@spilledgraphics]

Woah! Excel never ceases to amaze! this will come handy Mark, thank you!

[@ExcelOffTheGrid]

Thanks Carlos - I’m sure that’s a technique you could implement for your chart interactivity.

[@kndeepak]

Brilliant!!! Did not know about the zero width character usage!!

[@ExcelOffTheGrid]

Zero width space is quite useful for forcing sort orders in various places. So go explore and see what else you can use it for.

[@Neel-m4t]

Great tutorial! No match! Thank you so much!

[@ExcelOffTheGrid]

Thank you so much, I’m glad you liked it.

[@MrrrrTony]

This is a great video indeed! Thank you!

[@ExcelOffTheGrid]

Thank you, I hope you can put the technique to good use.

[@Back1Ply]

Thanks, also this Zero width space can be used for months names first charter (that are sorted) (i.e J,F,M,A..), this "zero width" also works in power bi.

[@ExcelOffTheGrid]

Yes, lots of use cases. I use this technique in Power BI all the time. It’s great for forcing the sort order in P&L and Balance Sheet reports.

[@kebincui]

Very creative and excellent as always❤❤ Thanks Mark 👍👍

[@ExcelOffTheGrid]

Thanks Kebin. 😁

[@davidcchambers]

Quite brilliant. Thanks for sharing an elegant solution to a vexing problem.

[@ExcelOffTheGrid]

Glad it was helpful! I hope you can put it to good use.

[@chrism9037]

Great video Mark, very useful!

[@ExcelOffTheGrid]

Thanks Chris, I’m glad you liked it.

[@ankursharma6157]

Another Interesting Video . . Thank You Very, Very Much Mark! Best Wishes!

[@ExcelOffTheGrid]

You’re welcome. I’m glad you enjoyed it.

[@SanjuShresthaSsanjy]

Great trick indeed. I used to add serial number before my list and lookup to sort in my own order.

[@ExcelOffTheGrid]

Ultimately this is the same technique, but using invisible characters, rather than numbers.

[@GrainneDuggan_Excel]

Mark Proctor, you are just scary smart! 🧠

[@ExcelOffTheGrid]

Is it smart to spend huge amounts of time in Excel? Probably not. 😂

[@GrainneDuggan_Excel]

@ExcelOffTheGrid there is a wise-guy in every crowd

[@samsergiesam388]

Brilliant! Tip to simplify your Sort-Formula: remove “Rows(TableName)-” and sort the slicer in descending order.

[@ExcelOffTheGrid]

100% agree about the easier formula. Conceptually, it's difficult not to think of "ascending" as being the order we want. So, I purposefully made that decision to force it that way.

[@rajeshmajumdar4999]

Thank you so much. 😊 Such a wonderful Trick! 🎩✨

[@ExcelOffTheGrid]

You are welcome, I hope you can put it to good use 😁

[@davidferrick]

Brilliant.

[@ExcelOffTheGrid]

Thank you. 😁

[@DataVisualisation]

Perfect solution, as ever! I can't test it with a MAC, but I believe you. 😊

[@ExcelOffTheGrid]

To be honest, I don't have a mac either. But... it should work... 😬

[@cric8482]

So clever ! Thanks

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@josh_excel]

Good solution

[@ExcelOffTheGrid]

Thank you, I appreciate that.

[@stanTrX]

Genius

[@ExcelOffTheGrid]

Thanks. 😁

[@ExcelOffTheGrid]

★ Get the example file ★ Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0235 Sort slicers.zip

[@lvtutorials3039]

Awesome

[@ExcelOffTheGrid]

Thank you.

[@GeertDelmulle]

This is exactly the same method that Goodly (Chandeep) demonstrated a while ago for sorting the names of the months in chronological order in PQ-M. The issue is that you can’t use that column to match/join/lookup/connect with other tables based on the month names unless those tables got the same treatment. This could be frustrating if one doesn’t know these details (and you can’t see why it doesn’t work - by design!). Therefore, columns like these should be used for presentation purposes only - where they work excellently. :-) Just a friendly warning.

[@ExcelOffTheGrid]

100% Presentation only. To use this for actual data would be a very bad idea. If there were a way to do this without adding presentational information to data tables that would be great. But sometimes that is the only way. I should have known that Chandeep would have a video, he's always 1 step ahead of me 😂

[@williamarthur4801]

Really good methods, I still find the sorting something I have to think about as far as how it sorts ie "aza" and "aaz" , etc. I remember Chandeep did really good vid on sort order using a similar method but then found an error. is there any advantage in Unicode 8203 orver CHAR ( 10 , I know there is 32 but that skews things, I guess 8203 better. Now my head hurts.

[@ExcelOffTheGrid]

CHAR(10) and CHAR(32) both occupy visible space, they are printed characters. While UNICHAR(8203) should be invisible to the eye as it is a non-printed character.

[@henryg5735]

Ingeneous! My slicer problem is different. Say I have data of invoices and a column with account codes 1-100. I want a slicer that has a "button" for 1-50 and another for 51-100. Can this be done? Actually, a helper column should do it, but any other solutions?

[@ExcelOffTheGrid]

Helper column is the only was I can think of. So, I think you’re in the right track.

[@goldylock]

What if i want NECSW/ CNESW, just play around with the number is it?

[@ExcelOffTheGrid]

Pretty much. You can change the order of the items in table to match any order you could want.

[@SimplyDigitalLK]

Wow.. awesome. Thats one of the most unexpected solutions. I knew about custom list and data model solutions, but never ever heard of this one and its possible also in PQ. trully awesome! Im pretty sure this one become my go to next time i will need sort slicers.. and honestly - i do need that a lot! I still dont know why we dont have in build solution in excel :(

[@ExcelOffTheGrid]

I hope you can put it to good use. It takes a bit of thought the first few times, but after that should be do-able quite quickly.

[@SimplyDigitalLK]

@@ExcelOffTheGrid Well, didnt take that long and i tried for the first time :) worked fine, i just replaced part of that logic with another column and numbers and then xlookup for it. It give me a bit more clarity of how it works.

[@ExcelOffTheGrid]

Great stuff. 👍

[@peltiertech1879]

Smoke and mirrors!

[@ExcelOffTheGrid]

All the best magic tricks are 😁