Hidden Formatting Tricks for Slicers

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

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

50% 1

How to transform Slicers from chunky to sleek, stylish, overcoming challenges posed by default sizing.
Whether you're building intricate Excel dashboards or just looking for ways to enhance your data presentation, this guide is for you.
⬇️DOWNLOAD the example file here: www.myonlinetraininghub.com/e...
🍿WATCH RELATED LESSONS NEXT:
==============================
Force Single Select in Slicers: • Force Excel Slicers to...
WHO AM I?
=========
If we haven't met yet, hi! 👋 I'm Mynda Treacy. I'm a CIMA qualified accountant turned Excel and Power BI trainer. In 2014, and every year since, I've received the Microsoft Most Valuable Professional award for my contribution to the Microsoft 365 community. I love sharing Excel and Power BI tips and techniques that will get you noticed and promoted. Follow me for a new video every week.
🎓LEARN MORE
==============
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join 450K+ 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 problem with Slicers
0:31 Custom Slcier Styles
1:46 Applying Slicer Styles
3:06 A Cool Trick!
3:39 Fixing Annoying Features

Наука

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

27 июн 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

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

Your ingenuity never ceases to amaze me. Always love coming back to your Excel content, especially dashboards, power query and pivot tables and charts 😊

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

Wow, thank you!

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

Great video. I've spent way too much of my life messing with slicers. One thing to note, if you turn off the slicer display header (and hence lose the multi-select and the clear-filter buttons), you can still reset the slicer WITHOUT having to select each slicer button. With only 1 button selected (doesn't work if you have more than one button selected) just Ctrl+click the button that is already selected, and now they all will be selected (equivalent to having clicked the "clear filter" button in the header that you have hidden)

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

Awesome tip, Quidisi! I'll try it out. 🙏

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

Yes! Good tip. I usually just do a quick macro and assign to a filter shape from the icons menu (or look up icons in iconfinder for different ones). That way the user still has the familiarity of the button.

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

Another option is to add ”clear all” to your QAT tollbar

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

That's exactly what I was looking for. Thanks Mynda.

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

Glad it was helpful!

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

Another great video. Thanks Mynda 🙏🙏

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

Thanks for watching! 🙏😊

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

No way! I’ve been looking for a way to reduce the font size for a while and it’s been there the whole time? Thanks Mynda!

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

😁glad you can make use of it, Darryl!

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

Thank you so much , very very important slicer customization

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

My pleasure 😊

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

Thank you for this thorough review of slicer design that can improve so much user experience. I like your idea of make it like a menu, or inserting it into a graph. … and also to your comment. Thank you

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

Thanks so much! Great to hear 😊

@aiasaiascon3894 6 месяцев назад

Within All, you are a tremendous BI Designer!!!

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

Thanks for your kind words 🥰

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

Very nice Mynda!!!

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

Cheers, Chris!

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

Oh Mindy, you're lovely - you really know your stuff.

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

Thank you!

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

Love this!!

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

Great to hear!

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

always great Mynda

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

Thanks so much!

@debasishsamal4646 День назад

Much Appreciated.

@MyOnlineTrainingHub День назад

Glad it was helpful 😊

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

When hiding the header, right clicking the slicer gives you the option to clear the filters

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

Ah, nice tip. Thanks for sharing.

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

:-) @@MyOnlineTrainingHub I also use some VBA sometimes; beware the difference between the slicer name and the slicer code name, which both can be found in the properties of the slicer: Sub ClearFilters() Application.ScreenUpdating = False Application.EnableEvents = False With ActiveWorkbook.SlicerCaches("Slicer_Sales_year1") ActiveSheet.Shapes.Range(Array("Sales year")).Select 'refers to name of slicer ActiveWorkbook.SlicerCaches("Slicer_Sales_year1").ClearManualFilter 'refers to code name of slicer ActiveSheet.Shapes.Range(Array("CategoryName 2")).Select ActiveWorkbook.SlicerCaches("Slicer_CategoryName2").ClearManualFilter ActiveSheet.Shapes.Range(Array("Country 2")).Select ActiveWorkbook.SlicerCaches("Slicer_Country2").ClearManualFilter End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub

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

Hi Mynda Very good and useful tricks, thanks. And please, allow me to comment that I’m glad to see a video that is just about the subject without any noise.

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

Thanks, Ivan!

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

thank you so much

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

Welcome 😊

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

Thank you!

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

You're welcome!

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

Awesome!

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

Glad you think so! 🙏

@albertogarcia8084 6 месяцев назад

Thanks

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

Welcome 😊

@learnspreadsheets 6 месяцев назад

This is brilliant! Wish you had more resizing capabilities but oh well

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

Cheers, David! Yeah, it's also a shame we can't do simple things like left or right align text in the buttons etc.

@AhmedOmar-mq2ed 8 месяцев назад

Good Job👍

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

Thanks 😁

@shanabenjamin8945 Месяц назад

thank you:)

@MyOnlineTrainingHub Месяц назад

You're welcome!

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

Amazing

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

Thank you! 😊

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

thanks

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

My pleasure 😊

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

Perfect

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

Thank you! 😁

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

I would love to see something similar for Power BI! for an application designed for data visualization, I find the lack of formatting options for slicers (or obvious options anyway...) very frustrating

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

Yes, Power Bi formatting for Slicers isn't great.

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

Thanks. 💎💎

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

You're welcome 😊

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

Thank you Mam the Tutorial was Great. Please Make more tutorials on None productive time report and production loss analysis report 😊

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

Thank you. Will keep those topics in mind.

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

Great Video Mynda- Slicers settings are not at all intuitive, some brilliant workarounds in there that i have just been able to put into good use 👌👌

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

Awesome to hear! 🙏

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

Instead of unchecking the Header box you can just remove text in the Header Caption. This way you will have your clear filters available, although you will still have a line above the slicers.

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

Nice tip! Thanks for sharing 🙏😊

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

Super

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

Thanks so much! 🙏😊

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

This is cool. I really wish we had more built-in options for the format of slicers (combo box, radio buttons, etc). Know it can be done in other ways but it's a hassle.

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

Yes, great idea. More like what Power BI has for Slicers would be awesome 😊

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

Thanks 🎉🎉

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

My pleasure 😉

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

@@MyOnlineTrainingHub 🤍🤍

@shoppersdream 4 месяца назад

This was nice, thanks! Excel works best on Windows and I have a Mac, so many things do not work on Mac.

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

Yes, Excel for Mac is behind but Microsoft are working hard to bring it to parity.

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

Could you please advise which software you have used to record this. I like minimizing and full screen of your physical appearance in the video. It was very smoothly transitioned

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

Premiere Pro.

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

Does it record your face from a computer camera or dslr Mynda...I mean to record face what have you used as a camera. Laptop or dslr because generally laptops won't have that clarity .

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

Thank you very much for the sharing. Now i know that i didnt click set as default

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

Glad I could help 😊

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

Thank you Mynda. 1- How can we reorder slicer buttons as we desire? 2- Can we change slicer buttons as image like describing button meannig? 3- Can we make slicer’s button names position as center because it is alligned by Left

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

1. video coming soon! 2. www.myonlinetraininghub.com/symbols-in-excel-slicers 3. no

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

Awesome Mom.. I'm from Indonesia, I'm so impressed.. I want learn more about excel.. Maybe U can give me advise please, Mom.. 😊

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

Glad you liked it! You can learn Excel in my courses here: www.myonlinetraininghub.com/courses

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

❤❤❤❤

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

🥰

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

Hi If you want quite a few slicers on a data pivot table...but you want them smaller than the default...Can you somehow set them to a size that then remains each time you open a new slicer?

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

Yes, you can create a custom slicer style and set it as the default: right-click the style in the gallery > set as default.

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

Mynda, do you have any videos on Pivot Table Two-Level Slicer? It is mainly because my Column has too much data. Thanks

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

You can increase the number of columns in the Slicer on the Slicer formatting tab if you want to make it wider to allow for less scrolling through the buttons.

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

@@MyOnlineTrainingHub Thanks! I can increase the number of columns but my columns are so big like "Hidden Formatting Tricks for Slicers :)" and if I increase the number of columns, it looks way ugly.

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

Ah, I see. Then the only option is to abbreviate the name!

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

Absolutely gutted, was so excited when I found this - only to realise it doesn't work on MAC 😢

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

Oh, no! Time to get a PC?

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

👍

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

Thanks for watching!

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

I always use date format yyyy-mm-dd, but when I make a date slicer, it comes in dd-mmm. Is there a way to format it?

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

You can add the date as a text field to your source data table: =TEXT(date, "yyyy-mm-dd") then use this field in your Slicer.

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

Always amazing 👍 🫶

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

😁🙏

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

how to format text in slicer for aligning it in Center?

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

Not possible, unfortunately.

@Kokoro-Gamer 8 месяцев назад

Hi, how to change grand total to average while having calculated fields in pivot table. I tried changing it and got a message from excel which says it can’t be when having calculated fields.

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

If the aggregation type is average, then the total will also be the 'total average', that is the sum of all items, averaged, as opposed to the average of the averages shown in the rows above. If the values in the column are summed, then you can't show an average in the grand total in a PivotTable. You'd have to add another column containing the averages. HTH. If you're still stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@Kokoro-Gamer 8 месяцев назад

@@MyOnlineTrainingHub Thank you so much for your reply, I’ll add another column for averages as you recommended.

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

Nice, but the "clean all filters icon" is not visible. Regards from Cali-Colombia.

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

You can always put that icon on the QAT and use it from there.

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

@alvarorodriguezlasso glad you liked it. Like Geert said, use the shortcut key, or you can left click and drag to select all items again, or select the first button, hold SHIFT and select the last button to select all items in long lists.

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

The main annoying feature of slicers that you cant track its changes, so you need to make additional table with tracking its values. Thanks for video, i also have no enough time to dig deeper into slicers settings.🤭

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

Glad you liked it. Not sure why you're tracking changes because Slicers don't change anything, they just apply filters. You can see what filters are applied based on the button status. Maybe I've misunderstood what you mean.