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.
★ 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
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?
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.
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 😂
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.
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 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.
I hate saying it after every video you do, but you sir are a genius
I'm just sharing what I know or discover. So if that makes me a genius then I'll take it. 😁
briliiant trick, I broke my head trying to find a similar solution, this is far more simple it actually works 😀
Quite brilliant. Thanks for sharing an elegant solution to a vexing problem.
Glad it was helpful! I hope you can put it to good use.
Brilliant! Tip to simplify your Sort-Formula: remove “Rows(TableName)-” and sort the slicer in descending order.
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.
Woah! Excel never ceases to amaze! this will come handy Mark, thank you!
Perfect solution, as ever! I can't test it with a MAC, but I believe you. 😊
To be honest, I don't have a mac either. But... it should work... 😬
Mark Proctor, you are just scary smart! 🧠
Is it smart to spend huge amounts of time in Excel? Probably not. 😂
@ExcelOffTheGrid there is a wise-guy in every crowd
Brilliant!!! Did not know about the zero width character usage!!
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.
So clever ! Thanks
Very creative and excellent as always❤❤ Thanks Mark 👍👍
Thanks Kebin. 😁
Great video Mark, very useful!
Good solution
★ 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
Awesome
Thank you.
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?
Helper column is the only was I can think of. So, I think you’re in the right track.
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.
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 😂
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.
What if i want NECSW/ CNESW, just play around with the number is it?
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 :(
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.
@@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.
Smoke and mirrors!