Visualize Excel salaries around world with these 66 Dashboards

49 Responses to “Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers”

1. Jan Karel Pieterse says:

   June 24, 2015 at 9:54 am

   Great article!
   If you want to learn a bit more about using slicers in VBA, head over here:
   http://jkp-ads.com/articles/slicers03.asp

   Reply
2. XLarium says:

   June 24, 2015 at 10:15 am

   Hi

   I downloaded cube-formula-slicer-selection.xlsx.
   Why is 'Report Connections' grayed out?

   Reply
3. Carlos Gonzalez says:

   June 24, 2015 at 2:39 pm

   Great article!! Thank you very much... This post is one of the most helpful for my job!

   Reply
4. TKSSKT says:

   June 24, 2015 at 5:04 pm

   Great Introduction. Thanks very much.

   Reply
5. Mando says:

   June 24, 2015 at 5:43 pm

   Wow! trying to use this on the reports that I have now. I really liked that Quantity and Amount Bar graph used on the pivot-multi tab, but for the life of me, I can't seem to replicate it from scratch. Help please?

   Reply
6. Excel Slicers – Introduction, what are they, how to use them, tips, advanced concepts, interactive charts & reports using Slicers & Pivot Tables | Chandoo.org – Learn Microsoft Excel Online | CareWare says:

   June 25, 2015 at 12:45 am

   […] http://chandoo.org/wp/2015/06/24/introduction-to-slicers/?utm_source=feedburner&utm_medium=email… […]

   Reply
7. Abhilash VK says:

   June 25, 2015 at 4:08 am

   This is awesome! I will favorite this page in my blog, http://www.exceltoxl.com

   Reply
8. MrXInDowntown says:

   June 25, 2015 at 11:46 pm

   Since I've known slicers about 2-3 yrs ago, I've pretty much used them in every damn report I do. Everyone that sees it for the first time is like "This is the best thing ever. Did you do that using excel or something else?" 😀 My bosses are so used it that when they see a report from someone else that doesn't have slicers they send it to me to redo it :).

   Reply
9. MrXInDowntown says:

   June 25, 2015 at 11:54 pm

   Couple of tips:-
   Tip 1:
   If for lack of space or say you want ability to search within a filter due to numerous values being present but still want it to connect to multiple pivot tables or charts then
   1. Setup a pivot table with just the report filter
   2. Create a slicer with the same field and tie that to all the pivot tables/charts that you want.
   3. Just place it some out of sight.
   Now you have a dropdown with all your values with search option plsu it is also connected to all your charts and pivot tables.

   TIP 2:
   In Excel 2013, slicers can be used with just plain tables as well. Not limited to pivot tables.

   Reply
10. Paulo says:

    June 26, 2015 at 2:37 am

    Congrats!

    Nice content : )

    Reply
11. indzara says:

    June 26, 2015 at 1:18 pm

    Very comprehensive. Explained in an extremely simple way. I have been using Slicers for a while, but still learnt new things from this post. Thanks for sharing. Best wishes.

    Reply
12. excel says:

    June 26, 2015 at 1:48 pm

    Awesome Explanation !!

    Reply
13. Raj says:

    July 5, 2015 at 11:24 pm

    I have joined this blog recently. Brilliant tools are available that I started using in my day to day work. Brilliant site. Thanks heaps.

    Reply
14. Slice Your Way through Tough Data Using Excel Slicers says:

    July 17, 2015 at 10:12 pm

    […] Read the full article here: Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interact… […]

    Reply
15. Kim says:

    September 23, 2015 at 12:50 pm

    Oh wow. I've only just started using Excel 2010 and had no idea this even existed. It makes dynamic charts so much easier!

    Reply
16. Clare says:

    October 28, 2015 at 9:53 pm

    You are my Hero! I am working with PowerPivot due to the huge amount of data I have and could not use my usual tricks to get the scatter chart title to change. For some reason the CUBE function wouldn't work (who knows why, I don't have time to dig into it now) but your "dummy" solution did.
    thankyouthankyouthankyou!
    Clare

    Reply
17. Stevie D says:

    December 15, 2015 at 3:37 pm

    On a normal PivotTable filter, you can choose whether to allow multiple items to be selected or not. Is that possible with slicers (in Excel 2010)? I've had a look through the options and not found a way to do it yet!

    Reply
    • Chandoo says:

      December 16, 2015 at 4:21 am

      Hi Stevie... this is not possible with slicers.

      Reply
      • Jo says:

        May 3, 2017 at 7:21 am

        Just hold down control when you're choosing them...can then either click another (without control) and it will show only the new one, or click the filter with the red 'x' to revert back to all options.

        Not a limitation that can be placed on the slicer but still a potential workaround depending on your needs.

        Reply
18. Rushabh Gala says:

    February 28, 2016 at 8:48 am

    Very comprehensive note on slicer. I haven't yet used ms excel 2010, but learnt Slicer tool very well

    Reply
19. Arif says:

    March 4, 2016 at 6:39 am

    How should I apply Slicer in excel 2010 version, not able find options
    as directed, could you please tell me that step by step

    Reply
    • Hui... says:

      March 4, 2016 at 8:38 am

      @Arif

      In Excel 2010 slicers could only be applied to Pivot tables/Charts not Regular tables

      Reply
    • Hui... says:

      March 4, 2016 at 8:38 am

      @Arif

      In Excel 2010 slicers could only be applied to Pivot tables/Charts not Regular tables

      Reply
20. Mary Ellen says:

    March 29, 2016 at 5:07 pm

    I have a longitudinal line graph with the count of exams scored at each level(1-4). I need a longitudinal line graph that shows the percentage for each level. I made my pivot with the count in the field settings with a calculation of % of row total. This works great until you add a slicer fo that you can look at one level at a time. When I do this, it shows as 100% because it seems to lose the rest of the row calculations. How can I set it up to show the percent. I do not have the option of adding it to my data table. I am using straight Pivot, not PowerPivot.

    Reply
    • Hui... says:

      March 30, 2016 at 12:47 am

      @Mary

      I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
      Attach a sample file with an example of what you are after, even hand drawn

      Reply
21. Carla says:

    May 23, 2016 at 9:17 am

    Hi, thanks for these tips. Is it possible to link a slicer to *different data sets*? All my data sets have a "year_opened" and "month_opened" fields, and I'd like do a single filter and update everything at once. Is that possible?

    Reply
22. Rafael says:

    September 3, 2016 at 2:47 pm

    Hi,
    Can someone tell me how to format a date field in a slicer to tell July 2016 instead of 07/31/2016?

    Thanks in advance.

    Reply
23. blk says:

    September 27, 2016 at 12:04 pm

    Great post - easily explainable for non excel whiz.

    Reply
24. Artieboy says:

    March 2, 2017 at 11:19 pm

    Thanks for the slicers post. I'm knew to this feature so don't be to harsh on me 🙂

    In the example bar chart graph: "Quantity breakup by Customer Profession and & Product category" you get a different picture depending on which area is chosen "East, Middle, North, South, West". That part I get. But the graph itself doesn't specify which region you are in.

    Is it possible to put the filtered criteria into the Chart title. For example if I chose West, the title would read "Quantity breakup by Customer Profession and & Product category - West".

    Is that possible? Just curious. Thanks

    Reply
    • Jo says:

      May 3, 2017 at 7:31 am

      It is possible...I have this on a number of my reports.
      1) create a pivot table with just the column your slicer is set on
      2) assign the slicer to that pivot table
      3) create a string in cell B3 (or wherever):
      ="Quantity breakup by Customer Profession & Product Category- "&A3
      (assuming that A3 is the cell that the chosen region appears in)
      4) click (once) on the graph title, then in the formula bar type =B3
      As you change the slicers, B3 will update as will the chart title.

      Couple of tips:
      1) if you need to have a new line for the title, use CHAR(10) e.g.
      ="Quantity breakup by Customer Profession & Product Category"&CHAR(10)&A3
      (this will have the region on a new line)
      2) if multiple regions will be chosen, I've added in an IF statement
      =IF(COUNTA(A3:A10)>1,"Multiple Regions",A3)
      (I'm sure there are ways to concatenate the strings but for mine it could get up to 20 and that just gets ridiculous for the graph heading)

      Reply
25. Sumit says:

    March 8, 2017 at 1:18 am

    Just Wow

    Reply
26. Teri says:

    May 4, 2017 at 3:03 pm

    I am trying to create a duplicate dashboard using data in one workbook and creating a new workbook to place in a shared file for my coworkers. I have created a separate worksheet in the original workbook for the new pivot charts and slicers I want to use in the new workbook/dashboard. I don't want all of the source data in the new workbook, as it is very large. I am having trouble making new slicers work. They work in the original workbook, but when I copy them to the new workbook they don't work. Am I going about this the right way or is there an easier way?

    Reply
27. mikael says:

    May 19, 2017 at 6:39 am

    Very good post! Helped a lot. Keep up the good work!

    Reply
28. Anthony says:

    June 8, 2017 at 3:01 pm

    how can you prevent multiple selection in a slicer box? In short, in any slicer box, only one entry is allowed and not multiple entries.

    Reply
    • Jo says:

      June 9, 2017 at 2:22 am

      Fairly new to forum's, hoping I'm not breaking a rule here, but I found this forum which seems to provide a solution:
      https://wessexbi.wordpress.com/2014/03/17/just-one-slice-please/

      Reply
29. Sheikh Mishuk says:

    September 21, 2017 at 9:32 am

    I have 2 files. (1. .xlsx 2. .xlsm)
    1 file contains all the pivot tables and charts. its also macro enabled.
    2nd file contains the source data which is a .xlsx file.

    but I am unable to run slicer on my 1st file.
    can anybody help me out?

    Reply
30. Philip Hinton says:

    April 16, 2018 at 10:24 am

    chandoo.org: one of my favourite Excel sites for years.
    Slicers tutorial: excellent as usual.
    Animated gifs: sorry, but REALLY distracting!! Especially with two on the same screen. Is there any way they can be activated only when we click on them, or something?

    Reply
31. Virupaksha says:

    November 4, 2018 at 6:17 pm

    Hi Team,

    I have inserted a slicer to a pivot table with 4 fields...I need to add another field for the same slicer...help me with this..

    Reply
32. Candida says:

    November 15, 2018 at 5:57 pm

    First of all I would like to say terrific blog!
    I had a quick questio in whiich I'd like to ask if you don't
    mind. I was intereested to know how you center yourself and clear your head
    before writing. I've had a hard time clearing my mind in getting my ideas out there.
    I do enjoy writing however it just seems like the first 10 to 15 minutes are generally lost simply just tryying to figure out how
    to begin. Any recommendations oor tips? Many thanks!

    Reply
33. H says:

    June 5, 2019 at 10:37 pm

    Hi All

    Im trying to connect a slicer to 2 pivot tables with different sources

    Both data tables have been sorted and have duplicates

    ie

    Table 1

    Name Week FTe
    A 1 7.2
    A 2 7.3
    B 1 7.3
    B 2 7.3

    Table 2

    Name Month Fte
    A Jan 2.6
    A Feb 3.2
    A Mar 4.4
    B Jan 2.2
    B Feb 6.4
    B Mar 2.2

    etc

    I have created 2 pivot tables and have sorted it out the way i want with charts etc

    Now all i want is to connect the Name Slicer to be connected to both of those pivot tables but problem is they have duplicates and are from different tables/sources

    how can i connect/add this to a data model and connect to my name slicer?

    Im sure it maybe something simple but minds not with it

    So in short 1 to connect 1 slicer to 2 different pivots from different sources but not all pivots (There are dups in both) - as shown in the example

    Thank You

    Reply
    • Chandoo says:

      June 10, 2019 at 11:12 pm

      Hi H
      This is how you can do it. Create a third table with all slicer options (in this case it would be Name column) with one row per unique value. Now add this table to your source list. Then link all two tables via this third table thru Data ribbon > Manage relationships feature. Finally add a slicer on this third table column and link the slicer to both pivot charts.

      Please note that you need to construct the tables and charts after data model is created.

      See this page for more explanation on how to use relationships - https://chandoo.org/wp/introduction-to-excel-2013-data-model-relationships/

      Reply
34. Cyleste says:

    July 17, 2019 at 5:29 pm

    Hi,

    Using Cube Value with Slicers is great. I am new to cube value, but it is so powerful. I am stuck on an issue where I want to filter on a slicer for all values except 1 and the slicer has thousands of values. I get #N/A in the results, when trying to do this. Any ideas on how to do an exception calc or how to get around this with the multi select slicer functionality?

    Thanks in advance.

    Cyleste

    Reply
    • Chandoo says:

      July 17, 2019 at 8:48 pm

      @Cyleste... thanks for your comments and welcome to Chandoo.org. You can use DAX to calculate such things as Excel pivot tables alone cannot function like the way you want. You can use DAX formula EXCEPT() to achieve this. For example,
      =CALCULATE(SUM(data[sales]), EXCEPT(ALL(data[filter_column]), VALUES(data[filter_column]))) can tell you the sum of [sales] column in the data table by ignoring slicer selected values.

      Hope that helps.

      Reply
      • Cyleste says:

        July 18, 2019 at 3:03 pm

        Hi Chandoo,

        Thank you for your quick reply. I am not familiar with DAX but it sounds like I won't be able to apply the calculation you provided after converting the power pivot to excel formulas via OLAP.

        Cyleste

        Reply
35. José Manuel Agundis says:

    August 26, 2019 at 1:48 am

    Thanks Chandoo, I like yours tricks & always I use slicers. Regards from México.

    Reply
36. Girish says:

    March 26, 2020 at 7:57 am

    Hi Chandoo,

    I have a lot of text in the slices (Pivot table). The text is not completely visible. What should I do?

    Please Help

    Thanks

    Reply
    • Chandoo says:

      March 27, 2020 at 4:29 am

      Hi Girish,

      Slicers are useful only for items with short text, for ex: categories, product names etc. For longer values, you are better off using form controls for interaction - Here is an overview of form controls Form Controls – Adding Interactivity to Your Worksheets

      Reply
37. Chris Brown says:

    June 29, 2022 at 10:01 am

    Thanks so much for this, it's brilliant! I think it's almost there - I've actually followed the steps on the example linked in my post. I just can't get it to filter properly; it just returns 0 when I add a date into Cell O2. Should I be doing it differently?

    Reply
38. ??? says:

    February 6, 2024 at 4:18 pm

    slicers dont work with non-admin roles in OLAP Pivot Tables

    Reply

Leave a Reply