Excel Slicer Custom Sorting Hacks - Masterclass!

Подписаться 616 тыс.

Просмотров 30 тыс.

50% 1

⬇️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

Наука

Опубликовано:

30 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 81

@martyc5674 8 месяцев назад

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 👍

@MyOnlineTrainingHub 8 месяцев назад

So glad it helped 😊

@RogerStocker 8 месяцев назад

Well done! It's always interesting to see it from a different angle.

@MyOnlineTrainingHub 8 месяцев назад

Glad you enjoyed it 😊🙏

@IndiaDekhowithVipin 8 месяцев назад

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

@MyOnlineTrainingHub 8 месяцев назад

You are most welcome 😉

@esltube 8 месяцев назад

Imho you run one of the smartest Excel channels on YT. This video was just splendid and fun. Thx Mynda.

@MyOnlineTrainingHub 8 месяцев назад

Wow, thank you for your kind words 🥰

@chrism9037 8 месяцев назад

Very clever tips, thanks Mynda!

@MyOnlineTrainingHub 8 месяцев назад

Cheers, Chris 🙏😊

@user-wg1vw1ku9s 7 месяцев назад

I got a good chuckle out of this video; not that i haven't enjoyed any of your previous ones.

@MyOnlineTrainingHub 7 месяцев назад

😁thanks for watching. Glad you enjoyed it!

@hectorbello2104 7 месяцев назад

Brilliant indeed!... Thanks for sharing... loved the new video format too (fun to watch)

@MyOnlineTrainingHub 7 месяцев назад

Awesome, thank you! 😁

@ricardogutierrezdiez9170 8 месяцев назад

Waw! My brain is going to explode with you Mynda 😊, it's amazing how easily you do

@MyOnlineTrainingHub 8 месяцев назад

😁 thank you, Ricardo! Glad you enjoyed it.

@kebincui 8 месяцев назад

Excellent as always. Thank Mynda👍 🌹

@MyOnlineTrainingHub 8 месяцев назад

Thanks so much 😊

@user-el9ok3kv2d 8 месяцев назад

I love this solution. Thank you!

@MyOnlineTrainingHub 8 месяцев назад

Glad you can use it!

@spilledgraphics 7 месяцев назад

minute 6:21 is gold! wow! thank you Mynda!

@MyOnlineTrainingHub 7 месяцев назад

So pleased you liked it 😁🙏

@henryg5735 8 месяцев назад

Very good 👍 Will move from Custom List to PP for sorting.

@MyOnlineTrainingHub 8 месяцев назад

Awesome to hear 😊

@mangal2cool 8 месяцев назад

Awesome, Thank you 👍

@MyOnlineTrainingHub 8 месяцев назад

Thank you too!

@JixelTech 7 месяцев назад

Great Video. Thanks

@MyOnlineTrainingHub 7 месяцев назад

Glad you liked it!

@aqynbc 8 месяцев назад

Amazing video Mynda! Combining fun and serious learning! :) Arnaud

@MyOnlineTrainingHub 8 месяцев назад

Glad you enjoyed it! 🙏😊

@abdelazizallam 8 месяцев назад

Amazing video Mynda

@MyOnlineTrainingHub 8 месяцев назад

Thanks so much!

@sseemm 5 месяцев назад

Thank you Mynda 🙏❤

@MyOnlineTrainingHub 5 месяцев назад

Pleasure 😊

@IamTheReaper911 8 месяцев назад

I am just loving your creativity, and now story lines 😁😍 😎

@MyOnlineTrainingHub 8 месяцев назад

😁thank you! Not sure YT likes them as much.

@IamTheReaper911 8 месяцев назад

@@MyOnlineTrainingHub well i ❤️ them, it makes learning fun

@manojjohnaxelsson2856 8 месяцев назад

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 !

@MyOnlineTrainingHub 8 месяцев назад

😁thank you!

@user-dn5gd1rn9f 8 месяцев назад

I never knew the slicer setting did not travel. Thank you!

@MyOnlineTrainingHub 8 месяцев назад

The slicer setting travels, but the custom lists don't.

@overdraftracing 7 месяцев назад

ok, so this was good as usual, but CTL-R???? I had no idea about this feature... SO useful! Thanks for the unintended bonus!

@MyOnlineTrainingHub 7 месяцев назад

So pleased there was a bonus tip for you 😁

@remuslupinhp 8 месяцев назад

Super helpful video! On a side note, I love Kirsty... 😂

@MyOnlineTrainingHub 8 месяцев назад

😁Glad it was helpful!

@TakeonmeLiive 10 дней назад

I build a lot of dashboards and I love your videos! Question: why does the data model take up to 5 seconds to refresh?

@MyOnlineTrainingHub 10 дней назад

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

@TakeonmeLiive 9 дней назад

@@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!)

@MyOnlineTrainingHub 3 дня назад

Hmm, I don't have this performance issue. Not sure why it would be.

@user-kg6wp5kj9g 7 месяцев назад

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?

@MyOnlineTrainingHub 7 месяцев назад

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.

@LosoIAm 7 месяцев назад

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.

@MyOnlineTrainingHub 7 месяцев назад

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.

@LosoIAm 7 месяцев назад

@@MyOnlineTrainingHub that's awesome, thank you. How do I register so I can purchase the courses?

@txreal2 3 месяца назад

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.

@MyOnlineTrainingHub 3 месяца назад

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”.

@steventate9276 8 месяцев назад

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.

@MyOnlineTrainingHub 8 месяцев назад

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.

@steventate9276 7 месяцев назад

@@MyOnlineTrainingHub I sent emails and complained in the feedback but no reply.

@TheIvalen 8 месяцев назад

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.

@johnpalmer3848 8 месяцев назад

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

@MyOnlineTrainingHub 8 месяцев назад

@Thelvalen great question. I see @johnpalmer3848 has answered it. Thanks, John!

@TheIvalen 8 месяцев назад

@@johnpalmer3848 Works like a charm. Thanks much!

@mohammadhosseinesnaashary8228 7 месяцев назад

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.

@MyOnlineTrainingHub 7 месяцев назад

You can do this with Power Query: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-L4BuUzccLpo.html

@UstedTubo187 8 месяцев назад

Is there anything better than one Mynda? Yes, two Myndas... I mean a Mynda and a Kirsty! 😉

@MyOnlineTrainingHub 8 месяцев назад

😁 you're too kind! 🙏

@tihomirhristov77 7 месяцев назад

💚

@MyOnlineTrainingHub 7 месяцев назад

🙏

@miteshvohra6948 8 месяцев назад

Is this something we can do with MS365 for Mac?

@MyOnlineTrainingHub 8 месяцев назад

Yes, except for the last option with the data model.

@sebastianl6809 2 месяца назад

Cannot sort month in the slicer on connected pivot table

@MyOnlineTrainingHub 2 месяца назад

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