Not cake, sorry. I’m talking about Excel Slicers and while they are 'sweeeet', as my 8 year old says, and a piece of cake to use, they aren't quite as tasty.
Slicers were introduced in Excel 2010 and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE functions.
Now I know you can already filter using the PivotTable or Excel Table filter tools but Slicers are better for 2 reasons:
- They can control the filtering of multiple PivotTables/Charts (but only one Table)
- They look nicer and are more intuitive to use
Have a go yourself using the interactive workbook below.
Warning: don’t go silly and choose too many areas though, or the PivotChart might implode….. what am I thinking? That’s like a red rag to a bull, of course you’re going to try it now that I mention it but at least I warned you!
Data used in chart above is from Greater London Authority (Microsoft Azure Marketplace).
Pretty cool, eh? I'm sure you're now itching to get started with your own Slicers, so here's how:
Watch the Video
Or continue reading if you prefer written instructions.
Download the Workbook
Enter your email address below to download the sample workbook.
Inserting Slicers
To insert a Slicer first select a cell in the Table or PivotTable that you want the Slicer to control > then on the Insert tab of the ribbon > in the Filters group you’ll find the Slicer icon.
Tip: In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose ‘Add as Slicer’:
Connecting Slicers to Multiple PivotTables/Charts
Once you’ve inserted your Slicer you can go about choosing which PivotTables/Charts you want it to control.
To do this; right-click the Slicer and choose Report Connections. This will open the dialog box below and you can check the box for the PivotTables you want the Slicer to control:
Note: A Slicer can only control PivotTables which share the same Pivot Cache. Typically PivotTables which reference the same data source share a Pivot Cache, but not always. If the PivotTables you want to connect to don't appear in the list then you'll know the cause is separate Pivot Caches
Using Slicers
Slicers are intuitive to use and they allow us to easily filter one or multiple items:
- Click one item to apply the filter for that area
- Click and drag through items to select more than one, or
- Click the first item > hold down SHIFT and click on the last item in your range
- Hold CTRL while clicking to select non-contiguous items
The Slicer displays the selected items in a different colour giving a visual indicator to the user:
And to remove all filters simply click the red x at the top right of the Slicer.
Formatting Slicers
One of the gripes I had with Slicers in the early days was that they were a bit chunky. Since then I’ve found some of the formatting tricks hidden deep down in the menus that allow you to make them a more manageable size.
I’ll take you through the obvious ones first and then I’ll show you the secret ones 😉
Slicer Settings
Select the outer edge of the Slicer to reveal the Slicer Tools; Options menu. Here we can access the Slicer Settings (Tip: you can also right-click the Slicer to access the Settings):
- Change the Slicer name
- Turn the Slicer header off/on, or give it a different caption
- Choose how to sort the Slicer
- Choose how the Slicer should handle items with no data. Note: in Excel 2010 you don't have the option to 'Hide items with no data'.
Slicer Styles
In the Slicer Styles group we can choose the colour and style, or create a new style (note: the colour options will be based on the Theme/Colors you have selected for the workbook in the Page Layout tab of the ribbon, mine is ‘Paper’):
Arrange Slicers
The Arrange group of tools allow you to quickly align your Slicers or move them behind or in front of other objects like charts, shapes, images etc.:
Buttons and Size
The last groups are Buttons and Size; here you can change the number of columns, button height and width and the overall size of the slicer (Tip: you can also use the pull handles which are available when you click the outer edge of the Slicer to change its overall size):
I generally find anything smaller than 0.5cm for the button height is as small as you can go.
If the overall height of your Slicer is too small to display all of the values a scroll bar will be inserted:
Now, while all of the above formatting options are great, they still only allow you to make your Slicer a bit smaller and it’s usually not enough, especially in dashboard reports where spreadsheet Real Estate is at a premium. That’s where the ‘Secret’ formatting options are essential…. Ok, they’re not really secret but they’re not obvious either.
New Slicer Styles
One option is to create your own Slicer Style. You can create a style from scratch on the Slicer Tools: Options tab > in the Slicer Styles group click on the down arrow to expand the gallery > click New Slicer Style:
This will open the ‘New Slicer Style’ dialog box where you can format each of the 10 Slicer elements exactly as you want:
You can even check the ‘Set as default slicer style for this document’ box and use it over and over again, but wait right there because I have a quicker way.
Copy and Modify Slicers
I prefer to copy a Slicer that has the colours I want to use, and then I can just modify the font size and get rid of the border to make it even smaller. To copy a style right-click the style in the gallery > Duplicate.
This will open the Modify Slicer Style dialog box:
Here you can give it a new name, then from the Slicer Elements list select ‘Whole Slicer’ and click on Format > in the ‘Font’ tab change the font size; I like 9, but it will depend on the font you choose:
Then go to the Border tab and remove the border:
Note: While removing the border serves to make the Slicer appear smaller it is actually still the same size. However, now you can squeeze it into a smaller space by placing the edges of the Slicer underneath other objects, like charts, without it being noticable.
After duplicating your Slicer style you then need to apply it to your Slicers. If you have multiple Slicers you can select them all (hold down SHIFT while you select each one), then click on your new style in the Slicer Style gallery.
Ok, now that your Slicer is more compact you’ll be able to squeeze it into your report.
Please Share
If you also think Slicers are cool, or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
KishanJung says
Hi Mynda,
Could you please clarify me my problem.
I have created a pivot table where the column field includes sum of Jan, Feb, Mar and so on. I want to filter as only Jan, feb or any other month. I could have done it if I have created from column labels but I have nothing in columns, everything in values field. How can I use slicers in this case to filter my datas.
Catalin Bombea says
Without seeing your layout, my guess is that you have sales values with column headers like Jan, Feb and so on. You should reformat the data to have all the headers in a column, this column will contain all months: Jan, Feb, then next column should have the corresponding amounts.
You can upload a sample file on our forum (create a new topic after sign-up), if you need help, if you’re not used to using power query to reformat data.
Linda Carroll says
Excellent tutorial, can’t believe it’s free. Thank you. I will be recommending it.
Mynda Treacy says
Thanks, Linda! Glad you found it useful.
sean says
First, thank you for your email with all the professional training material. You rock!!!
Is it possible that we can download your web training material in PDF?
Why I ask is that I have to print your training material from your email / website, then scan it, so I can have an electronic copy on my laptop. This results in wasted paper and additional time to have an electronic copy on my computer.
Catalin Bombea says
Hi Sean,
Glad to hear you like it.
You can simply print the web page to PDF. If you don’t have a PDF printer, you can install it, there are lots of PDF printers for free: PDFCreator, DoPDF, and so on.
Philip Treacy says
Hi Sean,
I’m actually looking at a way to do this. I’ll keep you posted about this. Hopefully will get something to yo in the next day or two.
Regards
Phil
Lori says
Nice. Can’t wait to try
Brandon Beecher says
Hello Mynda – I love your training courses and website! And I love the slicers – they are wonderful tools but I have an issue that comes up every now and then that I can’t figure out.
Sometimes, the slicer will not remove the buttons that “contain no data” when I select “Hide items that contain no data” in the Slicer Settings. Can you please help me out – what am I missing? Thanks
Mynda Treacy says
Hi Brandon,
Great to hear you’ve embraced Slicers.
The answer to your question is, it’s easily fixed by right-clicking the Slicer > Slicer Settings > uncheck ‘show items deleted from the data source’. If your version of Excel doesn’t have this option then you can right-click the PivotTable > Options > Data tab > set ‘Number of items to retain per field’ to None.
Mynda
Marie McCooey says
Hi Mynda,
Thank you so much for all the valuable Excel information you provide in an easy to understand manner.
I’ve created a slicer for my dashboard which includes 3 years of data. The charts display a different color for each year. Is there a way to coordinate the color displayed on the chart with the corresponding Year slicer button?
Mynda Treacy says
Hi Marie,
Thanks for your kind words. Glad we’ve been able to help 🙂
You can’t set Slicer buttons different colours within the same Slicer so this wouldn’t work for the different years, sorry.
Mynda
Tom Doyle says
Hi Mynda,
The interactive workbook at the top of the Slicers Tutorial is blank with a “The Connection Was Reset” message where the workbook should be.
Is there some way this can be fixed? It would help a lot in understanding the Tutorial to be able to see the workbook.
Thanks,
Tom D
Mynda Treacy says
Hi Tom,
The interactive workbook is displaying for me. Perhaps you could try again or try a different browser.
Mynda
Johan says
Hi Mynda,
First of all my compliments to the information you give on this website. For me it is a great source of help. Specially the way you explain things is of great help.
Now my question. I am creating a pivot table and want to use quite a number of slicers . Problem is that they use a lot of space on the worksheet. I was wondering if it is possible to turn them into dropdown lists. I cannot find it as a standard layout option, so I assume I need some kind of trick, if it is possible at all.
Thanks in advance for your answer.
Best Regards,
Johan
Mynda Treacy says
Hi Johan,
You can’t collapse Slicers, the best you can do is make the buttons and font in the buttons smaller.
Alternatively you can use a Combo Box (drop down list) and a macro to control multiple PivotTables similarly to how Slicers work. I teach this technique in my Excel Dashboard Course.
Kind regards,
Mynda
Valerie says
I attended the one-hour webinar on Pivot Tables with Slicers and learned so much! I created an informative dashboard for our 5-year budget prep and it has been a great hit! I want to put them everywhere now!
Mynda Treacy says
That’s wonderful, Valerie 🙂 I’m glad you found it so useful.
Happy dashboarding!
Mynda
JoAnn Paules says
I loved slicers on pivot tables in Excel 2010 but slicers on tables in Excel 2013 are flipping awesome! I maintain some metrics for one of our managers. He wants to track a 13-month rolling time period. I was using pivot tables and regular tables to collate the info and each month had to go in and manually adjust 7 different charts. I dreaded working on that file.
Enter Excel 2013. I did a complete renovation of my tables. I combine formulas to minimize the number of tables I needed, lined the 7 tables with charts up left to right, and inserted a slicer on the first table. Excel only charts what it can see so when I select the months in the slicer, all of my tables change. After I paste in my raw data, two clicks and all of my charts are updated. Sweet!!
Mynda Treacy says
Niiiiice! Very nice. 🙂
TC says
So this does not work for an Excel Table. Is there a Options setting that needs to be changed?
Mynda Treacy says
Hi TC,
Slicers only work with PivotTables in Excel 2010. In Excel 2013 they also work with Tables.
Kind regards,
Mynda
Wanda says
I hope this isn’t a stupid question and I have overlooked the answer. After I have inserted my slicer and selected my fields, I now what to remove a couple fields and add a different one to the slicer. How do I do that?
Mynda Treacy says
Hi Wanda,
The items in the slicer are based on your PivotTable source data. If you want new items to appear then they must be present in your source. If you have removed any trace of old items and they’re still in your slicer then you can change the settings:
Right-click – Slicer settings – uncheck the ‘show items deleted from the data source’.
Mynda
Nanaji Rao says
Thanks
Xolani says
Thank you very much for the wonderful article. I can now slice and dice my data and produce brilliant dashboards. I have one question though…..Can we connect two pivot tables of different pivot caches with a single slicer?
Kind Regards
Xolani (South Africa)
Mynda Treacy says
Hi Xolani,
Great to hear you found this article useful. Unfortunately you can’t control PivotTables with different pivot caches with the one Slicer.
Kind regards,
Mynda
Michael says
Mynda: These are great formatting tips. I am a new fan .
My question is: Can you remove individual slicer buttons from the list of sliders that are created when you Insert Sliders?
For example, I might base my sliders on the field “Color” which has 20 different possible values (in my case – color of widgets). But I only want certain of these “colors” to show up as slicer buttons. Unfortunately, all 20 buttons are created. I have tried hiding the colors I don’t want to pivot on, by putting a text box in front of them, but this is a very ineloquent solution. I have searched and searched for a solution to be able to delete the unwanted slicer buttons, but so far no luck, Hoping you may provide a work-around to this limitation in slicers.
Thanks in advance…
Mynda Treacy says
Hi Michael,
Glad you’re now a Slicer fan 🙂
You can’t choose which buttons to display in your Slicer but you can hide items with no data. So, what you need to do is inset another column in your source data for your Slicer. Let’s call it ‘Slicer Colors’. In this column only enter the colors you want to appear in your Slicer. I’d copy your original color column and then delete the colors you don’t want in your Slicer (just the color name from the cells, not the whole row).
Now refresh your PivotTable and insert a Slicer for this new column. You should have an item called ‘Blank’. Now right click the slicer > Slicer Settings > check the ‘Hide items with no data’ under Item Sorting and Filtering.
Your slicer should now display the colors you want.
I hope that helps. Please let me know if you get stuck.
Kind regards,
Mynda
MF says
Hi Mynda,
Is it possible to disconnect a copied slicer to its precedent slicer? As I want an independent pivot table and a slicer for other purpose.
What i did it to create a different cache even though it is basically referring to the same source.
Wondering if there is a more direct way to to so?
Cheers,
Mynda Treacy says
Hi MF,
You can just right-click the Slicer and choose ‘Report Connections’ and then deselect the PivotTables you don’t want the Slicer to control. The Slicer must be connected to at least one PivotTable so you can just set up a dummy PivotTable with the Slicer field in the rows or columns area.
I hope that makes sense.
Mynda
MF says
Hi Mynda,
Thanks for your super fast response. Appreciate that! 🙂
Sorry that I may not make my point clear.
My problem is: I have many duplicated slicers and they are all connected. Even though I can disconnect the slicer to a pivot table, I cannot disconnect the slicer from the rest of the duplicated slicers. Whenever I select an item on the slicer, the same selection applies to the rest of the duplicated slicers. And I was trying to find a way to get around it. Any idea?
Hope I have made my point clear this time. ;p
Mynda Treacy says
Hi MF,
Sorry, I misunderstood. Since you’ve cloned your Slicers you cannot disconnect them as they are part of the same hierarchy. You’ll have to delete the cloned ones and insert new ones, as opposed to copying and pasting them.
Kind regards,
Mynda
MF says
Hi Mynda,
Thanks for your advice. I think that’s the way to achieve that for the moment.
Cheers,
MF 🙂
Michael Rempel says
Mynda,
Thanks for the great post. However, when I try to set up two simple pivot tables that have similar headers, I add slicers to one pivot table, right-click to go to Report Connections. The dialog box appears but only contains the PivotTable for the slicer that was created. Any thoughts on why both don’t appear? Thanks,
Mynda Treacy says
Thanks, Michael.
Only PivotTables which share the same Pivot cache can be controlled by the one Slicer. So if only one PivotTable appears in the Report Connections it means they have different caches.
Hope that helps.
Kind regards,
Mynda
Michael Rempel says
That was it! Thanks!
RAVI XAVIER says
Very new to me but useful trick thank you.
Mynda Treacy says
Glad you liked it, Ravi 🙂
sam says
Excellent!
Any chance of getting a copy of the actual raw data file?
Mynda Treacy says
Hi Sam,
You can download it by clicking the Excel icon in the bottom right of the Excel Web App interactive workbook at the top of the post.
Kind regards,
Mynda
Edwin says
Great little trick with pivot. Tried it today at work and absolutely loved it
Mynda Treacy says
Thanks, Edwin. Glad you liked it.
Mynda
Jef says
Way cool! Excellent format and layout ideas. Thanks for sharing.
Mynda Treacy says
Thanks, Jef 🙂 Glad you liked it.
Mynda
Vinutha R says
i did’t find slicer………..m using excel-2007
Mynda Treacy says
Hi Vinutha,
Excel 2007 doesn’t have Slicers. Only from Excel 2010 onwards. Sorry.
Mynda
Bud Morgan says
Nice Tweak Mynda. My slicer appears much cleaner without the borders. Is there a way to establish my custom slicer style, in any instance of Excel that I open?
Regards,
Bud in Connecticut USA
Catalin Bombea says
Hi Bud,
You can Right click on your Custom Slicer Style, and select Set As Default, that’s all you have to do, but Custom styles are saved at the workbook level. This means that while your custom style is saved and travels with your workbook, any new workbook will not have your styles included. The workaround for this limitation is save your workbook as an Excel template. To do so, choose to save the workbook with your custom slicers as an Excel Template (*.xltx) file. Once you’ve got that saved, you can open your template file to start a new workbook with your custom style included.
Cheers,
Catalin
Maxime Manuel says
Precious, precious, precious!
Thank you!
Mynda Treacy says
Thanks, Maxime 🙂
Angel De Jesus Mendivelso Diaz says
Agradezco todos los aportes, aunque no soy bueno en el Inglés he aprendido grandes cosas de Excel que es mi pasión, Especialmente los DASHBOARDS, los cuales espero muy pronto poder aprender a realizarlos.
Saludos desde Colombia.
Gracias,
Mynda Treacy says
Gracias, Angel. Es agradable saber que puedo ayudar.
Saludos cordiales,
Mynda