Excel Slicers, EVERYTHING You Need to Know - includes workbook with step by step instructions
Vložit
- čas přidán 17. 07. 2024
- Excel Slicers are a professional way to enable users to easily and intuitively interact with your reports, filtering data in PivotTables, Pivot Charts and Excel Tables. They're available in Excel 2010 onwards for PivotTables, and for Excel Tables from Excel 2013. In this comprehensive tutorial I cover EVERYTHING you need to know about Excel Slicers.
🔻 DOWNLOAD EXCEL FILE HERE: www.myonlinetraininghub.com/i...
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟♀️ CONNECT with Mynda 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 Introduction
0:34 Slicers Overview
2:03 Slicers for Tables
2:28 Inserting Slicers
3:44 How to Use Slicers
4:37 Arranging & Formatting Slicers
6:43 Slicer Shortcuts
7:11 Slicer Properties
7:55 Connecting Slicers to Multiple PivotTables
8:41 Naming PivotTables
9:11 PivotTables not in Report Connections List
10:36 Slicer Settings
12:11 Removing Items No Longer in the Dataset
12:43 Download Excel File with Step by Step Instructions - Věda a technologie
How interesting that your email came in this morning. I was pondering (with my limited excel skillsets) how to view a mass of construction project management data to better filter and isolate, and here comes your video showing tools sitting there in my 2016 Excel. But without your clear and crisp presentation, I would have struggled to work with this new tool. Now I have easily deployed it, and we are working on a dashboard cover for strategic planning that will blow the other departments out the back door! Thanks Mynda for generously sharing your knowledge!!!
My pleasure, Steve. I love hearing success stories like yours. Congratulations, I hope you blow them away with your Excel mastery :-)
I have gone through many of the tutorial videos, they are wonderful, interesting and educative. I am very inspired to know you have put more energy and time to create and share knowledge generously. Thank you and remain bless as you continues to upgrade many.
Thanks for your kind words, Danesi!
Thanks Mynda
I appreciate your clear instructions and patient manner. You remove the mystery of learning to use unfamiliar tools.
Glad I can help 😊
What a skill!... the way you present the elements in your videos or sessions is tremendous.. Thanks a lot for the learning which you are sharing with everyone.. God bless you..
Thanks for your kind words, Suresh. I'm so pleased you find the videos helpful.
You are awesome! Thanks for sharing your excel file. It’s such a great help!
for the longest of time, I've been applying the same filters to different Pivots. This has helped me a lot.
Great to hear!
One of my favourite channels.
This quick training was on the spot. Thank you for the learnings
Very welcome, Jorge :-)
Thank you for this amazing video. Instructions are crystal clear. I will start my dashboard this week and will follow up with your other tutorials.. Such generosity of you to share all of this knowledge. Thank you so much 🌷🌷
Thanks for your kind words 🙏
so Complete and easy to understand explanations, I almost tear up 😭
I've been struggling with pivot table all days for the past 2 days, literally struggling from the moment when I wake up to like 2-3 AM... I don't know, it's so hard to understand it. But your video shed some lights. and your voice is calming, and your words makes it easy to understand, you also always added useful information when explaining which I appreciate a lot. The information adds up useful knowledge that I can implement on the future chance.
I'm currently interning in a consultant firm, and we have been making this dashboard for 3 weeks. I can't tell you enough how happy and relief I am to come across this very video. thankyou!
Aw, that's so lovely to hear, Bea. I'm glad I could help. If you get stuck in the future, you can also post your question and sample Excel file on our forum where we can help you: www.myonlinetraininghub.com/excel-forum
Thank you Mynda! OMG, I've been shoe horning information into Access and struggling to get out reports in Access and for my purposes. Slicers are the perfect alternative. I wish I had known about them before, but, thanks to your video, will be using them more and more going forward.
Glad it was helpful! Better late than never 😉
You just amaze me ...you make it look so simple and easy to understand. God bless you
Thanks so much 😊
Thanks for the slicer video Mynda! They are amazing!
Glad you liked it, Kevin. Slicers are indeed, amazing :-)
Amazing videos! Your experience with Excel is very broad and deep.
Glad you like them! 🙏😊
Great videos Mynda. Sliders are fairly basic but good to know how to link to more than one PT and also didn't know they could be used for tables.
Glad it was helpful!
Thank you for showing this to everyone, it was easy to understand, and clear
Great to hear, Johann!
Just in love with your tips and teachings!
Thank you, Johnatan! :-)
This is brilliant! Many thanks, Mynda.
My pleasure, Ian!
Thanks for this! Always been afraid to mess with these. I don't know why. Having fun playing with these today
Great to hear 😊
Awesone explanation!
I've watched a few of your videos. This is my favourite. Thank you!
Wow, thank you!
Excellent Presentation, taught me a lot in a very short time. Thanks a ton!
Thanks, Aditya :-) Have fun with Slicers.
thank u very much..i wish i could keep on liking u r videos...they r that good and i thank u from the bottom of my heart....
So nice of you, Raj!
Fantastic Video with very useful tips and tricks for us Excel Enthusiasts! Keep it up!
Thanks, will do!
extremely useful course, I learned a lot of new techniques, really looking forward to putting into practice, thank you for the excellent training session.
Great to hear!
I subscribed last week after watching one of your dash board tutorials 👍👍👍
Brilliant
!
I love you!
Thank you very much!
Happy to help, Eduard :-)
Thank you। so much for your helpful tips. It really helped me to fix an issue I was struggling with for hours
Wonderful to hear I could help.
Very clear and concise!
Glad you think so!
Nailed all basics of slicer succinctly👌✌👍
Glad you liked it :-)
Great help, thank you Mynda. Regards.
You're welcome, Carlos :-)
Thank you. It is really helpful.
Glad to hear that!
Very informative :) Thank you! 🙏
You're welcome :-)
Thanks for sharing. It’s great as al your videos.
Thanks for your kind words, Cristina :-)
You are a Pro my friend!!!
Cheers, Rene :-)
Awesome Tutorials.. Thank you
Glad you like them!
Very nice.. thank you so much for this
Most welcome 😊
Thank you so much!
You're welcome!
Great help!
Glad to hear it.
hi mam ! great fan of your work . Thank you for your guidence .
It's my pleasure 🙏
Outstanding
Thanks so much!
Well Done
more than great
You were right, how did I not have slicers before now! Thank you
:-) glad you'll find them useful, Matthew.
You are awesome. Thank you :-)
Glad you found my video helpful 😊🙏
thanks and keep going
Will do, Ahmed :-)
Thank you so much for your videos
Glad you like them!
@@MyOnlineTrainingHub They are highly useful and to the point. Love your channel. 👌🏾
This is brill!
Glad you liked, it, Kana. Slicers are one of my favourite tools :-)
Thanks for letting us know what version of Excel this applies to.
Glad to help!
Muito boa a sua aula, parabéns e obrigado.
Obrigado, Luciano :-)
I like ur trainings
Great to hear!
Oh wow!
Glad you liked it 😊
excellent content
Thanks, Ruphy :-)
Super! 😍
Thank you!
thank you
You're welcome 😊
Excellent video, you forgot to mention how to reader from the slicer any slicer selections
Excellent video. Clear and straightforward. Also "contiguous" is my word of the day now - it feels so good in the mouth! Question: can you get slicers to work with the new functions in Office365, for example to select the column to sort in SORT. It probably requires other functions in the background. It might be fun although I am not sure why you would need it!
Thanks, Ross! Yes, I'm sure with a clever hack you can get the Slicer to control the sort order. This might give you some ideas: www.myonlinetraininghub.com/use-excel-slicer-selection-in-formulas
@@MyOnlineTrainingHub Clever! Thank you.
Wonderfull video. I use slicers with a datamodel a lot. Sometimes it causes issues when connecting multiple slicers to multiple pivot table. Eg an issue, where slicers stop filtering each other, allthough the slicer column fields belong to the same table in the model. Remove one of the pivot connections, and they - supprisingly - filter each other again. Would be great if you had a similar video on slicers in a data model contex 😊
Thank you and thanks for sharing that workaround. I've not experience that problem before, so good to know.
Great
Glad you liked it 😊
I love you.
Glad you liked my video!
Thanks!
My pleasure, Ian!
@@MyOnlineTrainingHub I commented recently on one of your other videos that I didn't like pivot tables. But I've started using them since then, on your recommendation, and I'm getting into them. Now that I've seen slicers in action, I will investigate these, too.
Great to hear, Ian!
Thats great. Thank you. How do you incorporate a pie chart and graphs with slicer
My pleasure! Create a pie chart from a PivotTable and then connect the Slicer to the PivotTable.
Very informative channel and videos! Thank you for sharing. One quick question - I have a dual axis chart but when I use the linked slicer to filter, the dual axis disappears. Any idea what the cause is or what the fix might be?
Hi Tyler, I've not see a dual axis disappear before. Typically a Pivot Chart will lose formatting if the items selected in the Slicer result in series dropping out of the chart, so I wonder if this is the issue. If you can post your question and share your file on our Excel forum (www.myonlinetraininghub.com/excel-forum) I can try to help further. Mynda
Good
Glad you liked it 😊
Timeless video
Thanks so much 😊
Amazing tutorial. Many thanks. Can you please tell me how did you get Year filed in as the Slicer. I can't see any columns with that Name. Many thanks.
The Year field is automatically created when you group dates in the PivotTable.
Thank you Mynda - i could not figure out why pvtTables i had were not appearing on the Report Connection
Glad I could help, Seamus 😊
I just love your tutorials. Question. When slicing is there a way to keep row categories with no data that are filtered out? For example if the are sales for 5 products for 3 shops and one shop only sold 3 product types, the other two would still show but as nil sales. I have one unfiltered chart with 8 columns but it filters to one column at times and looks rubbish.
Hi Stuart, Try right-clicking on the field > Field Settings > Show Items with No Values.
@@MyOnlineTrainingHub brilliant. Thank you!
Can you share some specific videos for data modelling and power pivot ?
Will keep it in mind.
Thanks for sharing all this, can we add search bar in slicer in order to find specific item in bulk data.
Hi Salman, there's no search functionality in a Slicer. If you have a long list of items then I recommend this technique instead, which is searchable: www.myonlinetraininghub.com/searchable-drop-down-list-in-excel
Link this searchable list PivotTable to a Slicer that is linked to all the other PivotTables you want to filter. When you make a selection in the filter they will flow through to the Slicer and your other PivotTables and Pivot Charts that are linked to the Slicer.
Hope that makes sense. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub THANKS ALOT
Thanks for the Slicer lesson! One question, can I clear all the selected slicers (and reset my table or Pivot table) back to the default view with one click?
If you only have one PivotTable/Table then you can use the Clear Filters button on the Data tab in the 'Sort & Filter' group which is active when the PivotTable or Table is selected.
@@MyOnlineTrainingHub oh duh, I wasn't even thinking about that button. Thanks!
thank you for your work, i appreciate your videos :)for me the connexion button is not selected?you should also make a video only for the cash pivot table because it s complicate concept.Can you please show how you create sub categories? why should we use the slicer if there is already a filter in the table please?
Glad it was helpful. The slicer is just easier and more intuitive to use than the filter drop down.
@@MyOnlineTrainingHub Can you te'll me why the connexion button is not highlighted?
Thank you...i think the problem was that my data was in a table but not a pivot table...i noticed you can make a sliver from a table but was not able to group by year or month...do you know if it’s possible to sort that way? I was hoping to be able to filter for year without losing the ability to sort the main table...
Oh ok, yes you can't group data in Tables, only PivotTables. Just put the data in a PivotTable and put every field in the row/column labels to create a PivotTable that contains all of the data from your table. You can also sort PivotTables etc.
Hi There, created two slicers beside a pivot table. I went to size and properties then select the box that says disable resizing and moving. For some reason the moving part only works with one with the slicers the other one moves when i hide some of the columns or when i delete some of the blank columns on the left side of the P table. Note i put the slicers on top but i cannot get both to be fixed at the same time. If you have any trick to fix this will be greatly appreciated. thanks
Hi Gustavo, I'm not familiar with this problem, but if you want to post your question on our Excel forum (www.myonlinetraininghub.com/excel-forum) where you can upload your Excel file with the problematic Slicers, we'll take a look.
Awesome video, I can't seem slice my data correctly. When I copy a slicer to another tab to try to manipulate the data on the second tab, it doesn't work, please advise
Thanks, Juan! Sounds like you haven't connected the Slicer to the PivotTable on the second tab as shown in the video. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Dear Mynda please advise what is the correct way to create an activity planning tool in excel. Have a list of Strategies with multiple projects each with multiple activities and each activity with list of indicators that a planner could pick from. Tried to use slicers but with no sucess. Would like to automate the anual planning process. Thank you.
Sounds like you need some Excel consulting. Please get in touch via email: website at MyOnlineTrainingHub.com and we'll put you in touch with someone who can help.
Very Nice, thanks! You didn't create any videos on TimeLine. How do you make Timeline Immovable? Thanks
You can't make the timeline immovable!
@@MyOnlineTrainingHub Thanks! You're so smart. Please find a way to make them immovable. I need to send my file to another Group and I don't want them to move my Timeline wherever they want.
It's not possible, unfortunately. It's just the way it was designed. You would have to use Slicers instead.
Possible to add table (not pivot table) slicer in Excel online? I haven't been able to find a way to do it, so I create them in the desktop app but this is an issue since our company uses a document storage tool which makes downloading and uploading an Excel file a hassle (people only use Excel for the web).
Not yet.
I have just discovered slicers via your youtube videos and I am loving them...However, I am finding that some of the options you show are not currently showing in my menu (the one you get when you right click) I am not able to group my data or check the report connections. I do not know if this is because I am using an excel template that was premade or if I simply need to go in and add something...any ideas? I plan on watching all you videos but I think this is pretty basic and doesn't have a video. I am eager to learn more about using slicers to sort my data, which is more related to staff training and not cost/revenue.
Hi Tanya, Slicers can only group numeric data. Likewise, Excel dates should be entered in their date serial number format. If they are entered as text then they cannot be grouped in the PivotTable and you'll get the error 'cannot group that data'. If your data is in Power Pivot then you may also get that error in earlier versions of Excel (pre Excel 2013 from memory). Please post further questions in our Excel forum where we can troubleshoot and share images and files: www.myonlinetraininghub.com/excel-forum
Do you have teams or what's up
Am enjoying ur training I have a change
If you have Excel help questions, you can post them in our forum: www.myonlinetraininghub.com/excel-forum
Thank you for the video, however my issue is my raw data changes weekly. Therefore, how can I update all my pivot tables while using slicers because I always get the following error message. The PivotTable filed name is not valid.
If your column names remain the same then you just replace the rows of data below them in your source data table. If the column names change then I’d say your data is in the wrong layout. It should be in a tabular format: www.myonlinetraininghub.com/excel-tabular-data-format
Hi guyz! can you assign different colors on ear year on slicer? example, the color button for 2020 is red, while 2021 is blue. is this possible?
Nope, sorry.
Wandeering if i can get support with a situattion. So i have 2 data tables, made 2 separate pivot tables for these. When i create a slicer, it only affects one of these pivot tables or the other, when i open the report connectuions tab (8:20) it only dispalays the current pivot table, cannot check the other as it does not appear...
This happens when you have created the PivotTables from different data sources, or when you have inadvertently created a separate Pivot Cache for the second PivotTable. More on the Pivot cache here: czcams.com/video/uIpNti9WYeM/video.html
Can you connect a slicer to both table and pivot table??
No. Only one Table, or to multiple PivotTables.
What if i need to do reccurring job like sale month report? Do i need to create new file every month?
Hi Dollawat, with Slicers you can choose the month you want to view, so there's no need to create a separate file for each month.
How do I get my pivot table in a fiscal year format April 2019 thru March 2020 and have my dashboard data and pivot tables pullling fiscal year data ..
Please refer to this tutorial: www.myonlinetraininghub.com/excel-convert-dates-to-fiscal-quarters-and-years
MyOnlineTrainingHub thank you so much!!... will review the link
How could I change the header font of the slicer, for example the font size?
You can create your own custom Slicer format as explained here: czcams.com/video/KEZzGu1MIY4/video.html
Hi Great Videos and very informative.
I'm struggling with creating a Slicer to Filter two pivot tables. I've been trying everything this morning. I can create a slicer to work on one Pivot Table, but I can not make it work with one Slicer. I have checked relationships, the froantbif the date in the two tables but am absolutely stuck. I've followed numerous videos but am stuck with a Slicer that does nothing. Please please help.
Mike
Thanks, Mike! It's hard to diagnose the problem without seeing your file or at least screenshots. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Hi. Many thanks for the quick reply, and apologies for my late reply.
Unfortunately I can not share the file as it is confidential, but the good news is I managed to get it to work. I had to enter a Field into the 'Values' area of the Pivot Table, even though I did not want to. The Tables and the subsequent Pivot Tables are being used to track a series of requirements/tasks for a Construction Project. The Pivot Tables are being used to collate all the information from the various Tables to highlight which tasks have not been completed. As such, there are no 'Values' in the sense of figures/currency/numbers, so I did not place a Field in this area. I have got around it by placing a Measure I created in the 'Values' area to indicate whether it is complete or not. Again, I didn't really want to do that, as the idea is using the Single Slicer to be able to Filter and show all Tasks which are not complete on the Dashboard Sheet.
The annoying thing is, that if I use a Single Slicer on a single Pivot Table, it works as planned without the additional measure.
Hope that makes sense.
Mike
Hi can you hide or remove the vertical and horizontal scroll bars for Slicers?
The only way to remove them is to make the Slicer big enough that all buttons are visible.
@@MyOnlineTrainingHub Thanks
I'm looking for an option to hide/unhide slicer according to currency cell values. I also want to disable the slicer when the sheet is locked. Does this option exist or did I exaggerate this time? :)))
There's no option to hide or unhide a Slicer, but you can lock it when the sheet is protected.
@@MyOnlineTrainingHub sorry, I meant I want a slider that hide/unhide cells values in certain columns. and the slider to be disabled when sheet is locked
That's not the functionality of Slicers. You'd need to use a form control and VBA to program the button.
Hello! Is it possible to connect a table and a pivot table to the same slicer?
No.
@@MyOnlineTrainingHub thx for the reply! Sad tho 😞
What's the difference, if any, between slicer and filter in the pivot table?
Both filter the PivotTable, the filter has more options for filtering, but the Slicer is more user friendly.
I have a multi sheet pivot table/chart and am having issues with renaming the slicer buttons, even using the custom lists under the advanced options. I want the buttons to be labeled the same as the sheet name. Is there a way to do this?
The slicer buttons are populated from the items in the source data. If you want different button names, you need to change the names in the source data.
@@MyOnlineTrainingHub I’ll check it out. Thank you so much.
How do you increase the font size of the slicers?
You have to create a custom style and in the formatting options you can set the font size.
Hi Mynda
I have just created my first dashboard using Pivot tables, charts and Slicers (thanks for the great instruction videos). I am using a dark theme for my dashboard and everthing looks good except from the Slicers.
I managed to change most parts of the Slicers into grey shades, but the Slider itself remains white. You don't mention the Sliders in your tutorial, is that because it's not possible to change the properties of Sliders?
I hope you can help as this would be the final touch on my dashboard ;-)
Congratulations! Have you seen my Slicer formatting tutorial: czcams.com/video/KEZzGu1MIY4/video.html
@@MyOnlineTrainingHub
Hi Mynda
Thanks for your prompt reply👍
Yes, I've watched both the "Excel Slider, Everything You Need to Know" as well as "Excel Slicer Formatting incl. Hidden Trick.." but I haven't seen anything in those, that could help me formatting the sliders in the Slicers.
BR Flemming
When you say 'the sliders' I presume you mean the buttons. In which case, you have to format the elements called 'selected item with data', 'selected item with no data' etc.
@@MyOnlineTrainingHub
Hi again
No, I mean the slider you can use to scroll the buttons up and down if there are to many to be shown in the same Slicer window (I hope my explanation makes sense? ;-)
I don't how to add a screenshot in this reply, which would be much more explanatory.
BR Flemming
Ah, the scroll bar. Correct, you cannot change the colour of it AFAIK.
I am on a project wherein I want the number shown in a cell if multiple selection in the slicer, can you please help?
You can use the technique described in this video: czcams.com/video/T7UUgYN-7Lo/video.html
Is it ok to have multiple dashboards if I have a lot of pivot tables ?...
My graphs may not all fit in one dashboard
Yes, that's ok, but keep in mind that a dashboard is designed to be viewed at a glance, so another option is to use smaller charts like Sparklines (www.myonlinetraininghub.com/excel-sparklines) or mini charts (czcams.com/video/ody_09oUaHY/video.html) so that you can keep it to one page.
Ok got it thank you so much!.... love your content !
I dont have sort by custom list on my slicer option. Using office 365. And 2019
Is there alternative to slicers? I'm bored of using slicers.
Maybe you are using Power Pivot PivotTables, which have different sorting options. For Power Pivot you must add a sort by field to your table containing the Slicer items and then set the 'sort by' field. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub so manual add additional coloumns in the data model and sort? I can't share the file due to it being confidential work related.
Yes, kind of. Please post your question on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
having problem connecting slicer, other pivot did not appear selection whenever i right click the slicer only 1 pivot appear.
I explain the cause of that problem and how to fix it at the 9:11 mark in the video.
😀
😊