Excel Slicer Custom Sorting Hacks - Masterclass!
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- ⬇️DOWNLOAD the example file here: www.myonlinetraininghub.com/c...
Improve the useability of your reports with these tricks to custom sort Excel Slicer buttons, enabling them appear in a specific order that suits your needs.
- Sort Slicers for months based on fiscal periods
- Sort Slicer buttons based on a custom list
- Sort a single Slicer for month & year
- Sort Slicer buttons based on another column
🎓RELATED LESSONS:
- Power Pivot & DAX Course: www.myonlinetraininghub.com/p...
- Slicer Formatting Hacks: • Hidden Formatting Tric...
LEARN MORE
===========
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join 340K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 The struggle
0:37 Sorting with custom lists
1:35 Sorting slicers for month & year
2:52 Custom sorting text labels
5:14 Custom sorting date slicers in data model
#excel #pivottables #slicers - วิทยาศาสตร์และเทคโนโลยี
Imho you run one of the smartest Excel channels on YT. This video was just splendid and fun. Thx Mynda.
Wow, thank you for your kind words 🥰
Well done! It's always interesting to see it from a different angle.
Glad you enjoyed it 😊🙏
Few weeks back I requested you to help me selecting months as per INDIAN FISCAL YEAR (Apr-Mar) this is a great help
Thank you
You are most welcome 😉
Very clever tips, thanks Mynda!
Cheers, Chris 🙏😊
Brilliant- this is so timely for me- I have a power pivot that wouldn’t lock the sort- I had a sort field exactly like you had but I was using the unsorted fact table rather than the sorted dimension table in the pivot- thanks a lot 👍
So glad it helped 😊
I got a good chuckle out of this video; not that i haven't enjoyed any of your previous ones.
😁thanks for watching. Glad you enjoyed it!
Waw! My brain is going to explode with you Mynda 😊, it's amazing how easily you do
😁 thank you, Ricardo! Glad you enjoyed it.
Brilliant indeed!... Thanks for sharing... loved the new video format too (fun to watch)
Awesome, thank you! 😁
Great Video.
Thanks
Glad you liked it!
I love this solution. Thank you!
Glad you can use it!
Excellent as always. Thank Mynda👍 🌹
Thanks so much 😊
minute 6:21 is gold! wow! thank you Mynda!
So pleased you liked it 😁🙏
Awesome, Thank you 👍
Thank you too!
Amazing video Mynda
Thanks so much!
Thank you Mynda 🙏❤
Pleasure 😊
Very good 👍 Will move from Custom List to PP for sorting.
Awesome to hear 😊
Amazing video Mynda! Combining fun and serious learning! :) Arnaud
Glad you enjoyed it! 🙏😊
I am just loving your creativity, and now story lines
😁😍
😎
😁thank you! Not sure YT likes them as much.
@@MyOnlineTrainingHub well i ❤️ them, it makes learning fun
I never knew the slicer setting did not travel. Thank you!
The slicer setting travels, but the custom lists don't.
Super helpful video! On a side note, I love Kirsty... 😂
😁Glad it was helpful!
You are definitely going to be canonised by the high priest of Excel as the "Patron saint of Excel"... you just EXCELL all the time !
😁thank you!
ok, so this was good as usual, but CTL-R???? I had no idea about this feature... SO useful! Thanks for the unintended bonus!
So pleased there was a bonus tip for you 😁
I build a lot of dashboards and I love your videos! Question: why does the data model take up to 5 seconds to refresh?
Thank you! There could be many reasons the data model is slow to refresh and it's probably less to do with the data model and more to do with the source of your data and how you're connecting to it, assuming there is Power Query in between. I'd be looking to see if it's the query that's slow. This post has some tips for speeding up slow queries: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
@@MyOnlineTrainingHub It's your training file excel_slicer_sorting.xlsx on tab Data Model Examples. When I change the sort order and then refresh, recalc is 5 seconds. Is there a setting I need to change? If this feature is for internal analysis, this is not an issue. But if used for a dashboard, the end-user will think a 5 second refresh is the model blowing up. (Side note...keep up the amazing output...I'm a major fan!)
Hmm, I don't have this performance issue. Not sure why it would be.
Dear Mynda, what series of your training courses do you recommend for someone who has some familiarity with simple Excel formulae (like VLOOKUP, INDEX/MATCH, etc.) and wants to be proficient in Excel charts/dashboards, Power Pivot & Power Query, and Power BI? I believe I have already asked this before (somewhere) but I just cannot find your reply, sorry.
I am conversant with MS Access/VBA and SQL and I want to make (slight) a career change.
Which version of MS Office do recommend? For learning/training purposes, is Office 2021 sufficient or should I go for Microsoft 365?
Many thanks and regards.
Hello and thanks for your interest in my courses. From what you describe, my Excel Dashboards + Power Query + Power Pivot + Power BI course bundle is ideal: www.myonlinetraininghub.com/power-bi-course
With your Access and SQL skills, you'll pick Power Pivot up easily. You can use Office 2021, but if you're going to purchase a license, I would definitely recommend getting 365 as it's always up to date.
If you have further questions, please reach out via email: website at MyOnlineTrainingHub.com as I won't see follow up questions on this thread due to the volume of comments I get.
@@MyOnlineTrainingHub that's awesome, thank you.
How do I register so I can purchase the courses?
Hi Mydna. Great video. I've followed the instructions but unfortunately "Use Custom Lists When Sorting" is not visible. Do you know what would be causing this issue?
You've probably loaded the data to the data model upon creating the PivotTable. In which case, Custom List sorting is not available. Instead, use the Data Model solution shown in the last example.
Is there a way to move all the highlighted values (data selected) to the top of a slicer? Besides increasing the number of column to 5 , which takes up space.
Scrolling down a list of 100 choices is taking time. Thanks.
The only way to do this is if there are no values for the other items at that filter level as you can go into the slicer settings and check the option to “hide items with no values”.
Q: Unrelated question (but I heard Kirsty had the same question :) ! Is there a way to highlight cells in the same column that are duplicates of 1 or more of the previous contiguous cells? For example a column should contain ONLY alternating 1s and 0s: I would like to highlight/identify those cells in the same column that contain a 1 or 0 that following a previous 1 or 0 respectively.
Conditional formatting of cell(s) based on a formula.
Since the column should contain only alternating 1's and 0's adjacent cells should not be equal. Formula for a value in A2 might look something like: =OR(A2 = A1, A2 = A3). This formula will highlight both duplicate cells. To highlight only the second cell, use only the first test from the OR: =A2 = A1
@Thelvalen great question. I see @johnpalmer3848 has answered it. Thanks, John!
@@johnpalmer3848 Works like a charm. Thanks much!
Is there any way to get data of a separate file into a new excel file?
I know about get data, but I want (without getting data manually) when clicking a button in Excel (produced by macro), a window appears and ask me to add the demanded file. Then the data loads automatically in the new excel file.
You can do this with Power Query: th-cam.com/video/L4BuUzccLpo/w-d-xo.html
Is this something we can do with MS365 for Mac?
Yes, except for the last option with the data model.
💚
🙏
Ok I got a great idea for a video. I have the same problem in excel and word. everything is in English. the menu, ribbon tabs options etc. but for some reason all the template titles are in Spanish.
That is odd 🤔 I’d send a frown via the help tab in Excel. This goes to the Excel team at Microsoft and they can help you.
@@MyOnlineTrainingHub I sent emails and complained in the feedback but no reply.
Is there anything better than one Mynda? Yes, two Myndas... I mean a Mynda and a Kirsty! 😉
😁 you're too kind! 🙏
Cannot sort month in the slicer on connected pivot table
You shouldn't need to sort the month if it's based on a proper date. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Uau ...
🙏😊
👍😎✊
🙏😁
Do you have Kirsty’s number? ❤
😂
Did you get that Kristy🥱? 😂
😁thanks for watching!