• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

I’ll Have a Slicer That!

You are here: Home / Excel PivotTables / I’ll Have a Slicer That!
Excel Slicers
August 12, 2014 by Mynda Treacy

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:

  1. They can control the filtering of multiple PivotTables/Charts (but only one Table)
  2. 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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook for this video containing step by step instructions.

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.

Inserting an Excel Slicer in Excel 2010 and 2013

Tip: In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose ‘Add as Slicer’:

Inserting a Slicer in Excel 2013 PivotTables

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:

Connecting Slicers to multiple PivotTables

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:

Slicer Example

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):

Slicer Settings

  1. Change the Slicer name
  2. Turn the Slicer header off/on, or give it a different caption
  3. Choose how to sort the Slicer
  4. 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’):

Slicer Styles

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

Arrange Slicers

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):

Slicer buttons and 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:

Slicer scrollbar

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:

Create a 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:

format Slicer elements

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.

Duplicate a Slicer Style

This will open the Modify Slicer Style dialog box:

Modify a Slicer Style

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:

Format a Slicer Style

Then go to the Border tab and remove the border:

Remove border from a Slicer

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.

Related Tutorials

Sorting Excel Date Slicers

Excel Slicers

More Slicers Posts

Symbols in Excel Slicers

Symbols in Excel Slicers is a fun way to make your reports more interesting. Here are some tricks to getting wingdings and symbols to display in Slicers.
Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts can open up a huge range of possibilities and the best part is you can use Slicers to control regular charts too.
slicer selection in formula

Use Excel Slicer Selection in Formulas

How to use Excel Slicer selection in formulas to control charts and tables. Download the workbook and follow along.
Excel Slicer Trick

Excel Slicer Trick

In this Excel Slicer Trick I show you how to use a Slicer to display a drilled down view of your data in a PivotChart. Download the file and follow along.
Excel Slicers for Fiscal Years

Excel Slicers for Fiscal Years

Inseting Excel Slicers for Fiscal Years is easy but requires a bit of DIY date classification.
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

Creating Excel Slicers for rolling periods is easy with this IF formula. Download the workbook and watch the video.
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

How to create a single Excel Slicer for Year and Month fields. Includes written instructions, video and workbook download.
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: slicers
Previous Post:Excel Formula to Spread Income or Costs Over MonthsExcel Formula to Spread Income or Costs Over Months
Next Post:Excel Table Absolute Structured References

Reader Interactions

Comments

  1. George Amponsah-Tabi

    March 14, 2022 at 11:59 pm

    Hi Mynda,

    Is it possible to create an excel slicer and limit the number of slicer options to say 5. For example if there are 20 countries and you want the slicer to show only 5.

    Reply
    • Catalin Bombea

      March 15, 2022 at 2:16 am

      Hi George,
      You can add a new column, with a formula to set the Country names as “Other”, if that country is not in the list of 5 you need, use the new column as a slicer.

      Reply
  2. Daniel Hidalgo

    January 27, 2022 at 8:16 am

    Hi Mynda!
    You are the best by far at explaining Excel.
    I have seen numerous tutorials but your work is outstanding.
    Keep it up!
    Greetings from Mexico!

    Reply
    • Mynda Treacy

      January 27, 2022 at 9:02 am

      Thanks so much, Daniel!

      Reply
  3. Aleks Fuzaylov

    October 24, 2021 at 3:59 pm

    Hello,
    love you lectures, could not download the working file, link might not working.
    Thank you very much!

    Reply
    • Mynda Treacy

      October 25, 2021 at 10:32 am

      Thanks for letting us know, Aleks. I’ve fixed it now.

      Reply
  4. Laura Jones

    February 11, 2021 at 3:51 am

    Hello
    I always find your tutorials so helpful.
    I am having a nightmare time with a pivotable profit and loss I set up. The slicers are not interacting with each other as they should. I have 3 layers (3 slicers) – Region, Location and then DC so theoretically you should be able to select the Region which should then show you on the Location slicer which locations are in that Region and then select a Location which then shows you on the DC slicer which DC’s are in the Location.
    I have created a similar pivot table before and the slicers work as expected but I cannot for the life of me get them to work on this pivotable profit and loss which I have invested a lot of time into.
    Any suggestions why the slicers wont work?

    Reply
    • Mynda Treacy

      February 11, 2021 at 10:20 am

      Hi Laura,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further. If necessary, anonmyise the data before sharing your file on the forum.

      Mynda

      Reply
  5. Ted

    December 22, 2020 at 1:27 am

    Hello Mynda,

    Please help me this case: I have data like:

    Item Sales Jan Pur-Jan Inv-Jan Sales-Feb Pur-Feb Inv-Feb
    Hat 5 7 2 4 8 6
    Shoes 3 8 5 2 3 6

    Is there any way I can add slicer to choose to view Sales – Purchase – Inventory?

    Ted

    Reply
    • Mynda Treacy

      December 22, 2020 at 2:56 pm

      Hi Ted,

      Not with your data structured like that. You need to put it in a tabular layout so you can build a PivotTable and use Slicers. You can use Power Query to unpivot the data.

      Mynda

      Reply
      • Ted

        January 4, 2021 at 3:15 pm

        Hi Mynda,

        Sorry to not explain clearly.

        You indicated om tabular layout rule: “Each Column contains a type of data e.g. date, order number, quantity, amount, salesperson, region etc.”

        My data column is Sale-Jan, Inv-Jan, Sale-Feb, Inv-Feb and so on. so they are different type of data.

        My data row is Shoes, hat, etc

        So in Pivot table, I want to see things by months. For example, I choose hat & sale, so pivot will indicate sale of hat by months. However, I need to choose Sale-Jan, Sale-Feb; not choose Jan only. Inv will be the same, I need to choose Jan-Inv, Feb-Inv. This will be problem if I have 15 months, mean I need to untick 15 months of Sale and tick 15 months of Inv if I want to switch.

        It is difficult to explain with words and my English. If you are not clear, please let me know your email. I will send an example

        Regards,
        Ted

        Reply
        • Catalin Bombea

          January 4, 2021 at 3:42 pm

          Hi Ted,
          Can you please upload a sample file on our forum? Will be much easier to help you on your specific data structure.
          Create a new topic after sign-up and upload your sample.
          See you there
          Regards,
          Catalin

          Reply
  6. David

    August 21, 2020 at 1:19 pm

    I cannot work out nor find how to set a slicer to work on a count field in my pivot table.

    I have tried filtering pivot table data but not permitted, and count field is not an option for inserting slicer.

    I have a list of companies, a count distinct column and a count column.
    The count distinct tells me how many companies have downloaded my docs, and the count column tells me how many times that have downloaded the docs. (The docs change). I also have max and min date for each company so can see when they downloaded/most recently downloaded etc.

    I want to only see companies that have downloaded once (ie filter out/not display companies with more than 1 in the count colum.

    Thanks
    David

    Reply
    • Mynda Treacy

      August 21, 2020 at 2:04 pm

      Hi David,

      If you just want to filter for counts > 1 then you should use the filter buttons on the PivotTable to do this. It’s not a job for a Slicer. Hope that points you in the right direction.

      Mynda

      Reply
  7. Benert Xavier

    June 23, 2020 at 1:12 pm

    hi, I would like to change a slicer which is in horizontal to vertical

    Reply
    • Mynda Treacy

      June 23, 2020 at 2:25 pm

      Hi Benert,

      Simply change the number of columns to 1 on the Slicer tab of the ribbon.

      Mynda

      Reply
  8. Dan

    October 25, 2019 at 10:13 pm

    You’re a real G for doing this. I’m in tears. Thank you Mynda. We stan an excel queen.

    Reply
    • Mynda Treacy

      October 26, 2019 at 11:59 am

      🙂 Glad you found it helpful, Dan!

      Reply
  9. zhi he

    October 8, 2019 at 8:15 am

    I’m still using Excel 2007. How do I solve the Slicer problem?

    Reply
    • Mynda Treacy

      October 8, 2019 at 8:39 am

      You need to use form controls and VBA to create a similar effect to Slicers. I cover this in my Excel Dashboard course.

      Mynda

      Reply
  10. Steve

    July 17, 2019 at 6:55 am

    Yesterday morning I awoke to all the Power Pivot dashboards I built for my company having all slicers contain a red x in the upper right hand corner REGARDLESS of whether or not the slicer was actually filtered. It’s like being in a plane with all instruments are giving off warnings regardless of the actual situation. This is literally an unmitigated disaster as all dashboards are much harder to understand now. Scrambling to find a fix to what is by an order of magnitude the most shocking and disastrous bug I’ve ever encountered. I’ve contacted the Excel development team but this is requires and urgent fix. I’ve found that others have recently encountered this as well but with no fix. Does anyone have any idea how to fix this?

    Reply
    • Mynda Treacy

      July 17, 2019 at 8:27 am

      Hi Steve,

      I’m not aware of this bug. You didn’t say what version of Excel you’re using so it’s difficult for me to investigate further or escalate with Microsoft.

      Mynda

      Reply
  11. Joan Kizito N

    March 27, 2019 at 6:20 pm

    Thank You Treacy & Team. I am surely learning !

    Reply
  12. Steve

    November 18, 2018 at 2:52 pm

    Is there a convenient way to set a slicer to “Size but don’t move with cells”? Not having this feature has plagued my dashboards for years.

    Reply
    • Catalin Bombea

      November 18, 2018 at 2:59 pm

      Hi Steve,
      Usually, you have more than 1 slicer, all you have to do is to select them all, then right click and choose Group.
      This way, all will be a part of the same group, and you can set that group properties to not move or size with cells.

      Reply
  13. Gem Cowles

    August 12, 2018 at 11:59 pm

    Hi there,

    This is absolutely incredible!! Best site I’ve seen – ever!!! A good idea would be to add this training to an app?! Apologies if I’ve missed this of its already available.

    This has helped me so much. I’ll def be sharing.

    Thank you so much!
    Gem (Belfast, NI)

    Reply
    • Mynda Treacy

      August 13, 2018 at 7:48 am

      Thanks for your kind words, Gem 🙂 Glad we can help.

      Reply
  14. KishanJung

    March 2, 2018 at 5:02 am

    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.

    Reply
    • Catalin Bombea

      March 2, 2018 at 1:45 pm

      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.

      Reply
  15. Linda Carroll

    February 22, 2018 at 2:19 am

    Excellent tutorial, can’t believe it’s free. Thank you. I will be recommending it.

    Reply
    • Mynda Treacy

      February 22, 2018 at 9:04 am

      Thanks, Linda! Glad you found it useful.

      Reply
  16. sean

    August 31, 2017 at 4:54 pm

    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.

    Reply
    • Catalin Bombea

      August 31, 2017 at 8:55 pm

      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.

      Reply
    • Philip Treacy

      September 1, 2017 at 8:27 am

      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

      Reply
  17. Lori

    August 30, 2017 at 9:39 pm

    Nice. Can’t wait to try

    Reply
  18. Brandon Beecher

    July 7, 2017 at 10:21 pm

    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

    Reply
    • Mynda Treacy

      July 8, 2017 at 7:40 am

      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

      Reply
  19. Marie McCooey

    February 1, 2017 at 12:33 pm

    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?

    Reply
    • Mynda Treacy

      February 1, 2017 at 12:59 pm

      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

      Reply
  20. Tom Doyle

    May 13, 2016 at 6:11 am

    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

    Reply
    • Mynda Treacy

      May 13, 2016 at 10:04 am

      Hi Tom,

      The interactive workbook is displaying for me. Perhaps you could try again or try a different browser.

      Mynda

      Reply
  21. Johan

    July 27, 2015 at 10:35 pm

    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

    Reply
    • Mynda Treacy

      July 28, 2015 at 9:24 am

      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

      Reply
  22. Valerie

    May 8, 2015 at 7:30 am

    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!

    Reply
    • Mynda Treacy

      May 8, 2015 at 8:22 am

      That’s wonderful, Valerie 🙂 I’m glad you found it so useful.

      Happy dashboarding!

      Mynda

      Reply
  23. JoAnn Paules

    May 6, 2015 at 8:42 pm

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

    Reply
    • Mynda Treacy

      May 6, 2015 at 8:44 pm

      Niiiiice! Very nice. 🙂

      Reply
  24. TC

    February 13, 2015 at 1:46 am

    So this does not work for an Excel Table. Is there a Options setting that needs to be changed?

    Reply
    • Mynda Treacy

      February 13, 2015 at 6:28 am

      Hi TC,

      Slicers only work with PivotTables in Excel 2010. In Excel 2013 they also work with Tables.

      Kind regards,

      Mynda

      Reply
  25. Wanda

    February 11, 2015 at 10:19 pm

    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?

    Reply
    • Mynda Treacy

      February 12, 2015 at 8:52 am

      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

      Reply
  26. Nanaji Rao

    February 11, 2015 at 8:10 pm

    Thanks

    Reply
  27. Xolani

    November 12, 2014 at 7:13 pm

    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)

    Reply
    • Mynda Treacy

      November 12, 2014 at 8:02 pm

      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

      Reply
  28. Michael

    October 20, 2014 at 11:06 am

    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…

    Reply
    • Mynda Treacy

      October 20, 2014 at 1:46 pm

      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

      Reply
  29. MF

    August 29, 2014 at 6:44 pm

    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,

    Reply
    • Mynda Treacy

      August 29, 2014 at 7:12 pm

      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

      Reply
      • MF

        August 29, 2014 at 7:23 pm

        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

        Reply
        • Mynda Treacy

          August 29, 2014 at 9:00 pm

          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

          Reply
          • MF

            September 1, 2014 at 12:00 pm

            Hi Mynda,
            Thanks for your advice. I think that’s the way to achieve that for the moment.
            Cheers,
            MF 🙂

  30. Michael Rempel

    August 26, 2014 at 1:15 am

    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,

    Reply
    • Mynda Treacy

      August 26, 2014 at 7:43 am

      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

      Reply
      • Michael Rempel

        August 26, 2014 at 8:32 am

        That was it! Thanks!

        Reply
  31. RAVI XAVIER

    August 25, 2014 at 3:24 am

    Very new to me but useful trick thank you.

    Reply
    • Mynda Treacy

      August 25, 2014 at 7:50 am

      Glad you liked it, Ravi 🙂

      Reply
  32. sam

    August 17, 2014 at 2:46 am

    Excellent!

    Any chance of getting a copy of the actual raw data file?

    Reply
    • Mynda Treacy

      August 17, 2014 at 8:26 pm

      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

      Reply
  33. Edwin

    August 15, 2014 at 6:41 pm

    Great little trick with pivot. Tried it today at work and absolutely loved it

    Reply
    • Mynda Treacy

      August 15, 2014 at 7:10 pm

      Thanks, Edwin. Glad you liked it.

      Mynda

      Reply
  34. Jef

    August 14, 2014 at 4:29 pm

    Way cool! Excellent format and layout ideas. Thanks for sharing.

    Reply
    • Mynda Treacy

      August 14, 2014 at 10:47 pm

      Thanks, Jef 🙂 Glad you liked it.

      Mynda

      Reply
  35. Vinutha R

    August 14, 2014 at 2:14 pm

    i did’t find slicer………..m using excel-2007

    Reply
    • Mynda Treacy

      August 14, 2014 at 2:27 pm

      Hi Vinutha,

      Excel 2007 doesn’t have Slicers. Only from Excel 2010 onwards. Sorry.

      Mynda

      Reply
  36. Bud Morgan

    August 14, 2014 at 12:07 am

    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

    Reply
    • Catalin Bombea

      August 14, 2014 at 12:34 am

      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

      Reply
  37. Maxime Manuel

    August 13, 2014 at 9:44 pm

    Precious, precious, precious!
    Thank you!

    Reply
    • Mynda Treacy

      August 14, 2014 at 9:20 am

      Thanks, Maxime 🙂

      Reply
  38. Angel De Jesus Mendivelso Diaz

    August 13, 2014 at 1:18 pm

    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,

    Reply
    • Mynda Treacy

      August 13, 2014 at 1:28 pm

      Gracias, Angel. Es agradable saber que puedo ayudar.

      Saludos cordiales,

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.