Use slicers to create a cool selection mechanism [quick tip]

Posted on February 8th, 2016 in Learn Excel , Pivot Tables & Charts - 17 comments

Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

First see a quick demo

using-slicers-as-selection-mechanism-demo

Looks slick, eh? Read on.

Slicers as selection mechanism – step by step tutorial

Just follow below steps to create your own slicer selection tool.

  1. Enter running numbers (say 1 to 10) in a range, with the header numbers
  2. Select the range and create a pivot table from it.
  3. Select numbers from field list and add it as a slicer. We get this.selection-mechanism-slicer-initial
  4. Select the slicer, go to Options ribbon and set up,
    1. Slicer columns as 10
    2. Height & width as small numbers (0.3 cm or 0.1 inch)slicer-settings-for-selection-mechanism
  5.  Disable slicer header.  Right click on the slicer and go to slicer settings. Uncheck Display Header option.disable-slicer-header
  6. At this stage, your slicer selection tool is almost ready. I say almost because it will still have a border and default style.default-slicer-selection-tool
  7. Duplicate any of the slicer styles and set up your own style by disabling slicer border and changing the colors. And your slicer selection tool is ready.

using-slicers-as-selection-mechanism-demo

Download the example workbook

Click here to download  the slicer selection example workbook. Examine the settings and styles. Play with it to learn more.

Slicer like a ninja, check out below tutorials

Slicers are a must have in any advanced Excel user’s tool kit. Check out below tutorials to learn awesome ways to use them.

Share your slicer inspiration…

Ever since learning about slicers in Excel 2010, I have been using them in all my dashboards and training programs. I like the simplicity and possibilities they offer.

What about you? How do you use slicers in your line of work? Please share your proudest slicer moments, tips and tricks in the comments section.


Do you want to be awesome in Excel?

Free Excel tips book - joining bonus - Chandoo.org newsletter

Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 Responses to “Use slicers to create a cool selection mechanism [quick tip]”

  1. Harry says:

    What happened to the AMA?

  2. Chris says:

    How does actual provide data? A slicer isn't a data input device, and while this is visually appealing... I don't see how this would allow the user in a dashboard or anywhere else provide actual input. Am I missing something?

    • Harry says:

      It doesn't provide data, it provides a visual input for data.

      People shy away from standard drab data input devices, making inputs accessible such as the above helps maintain a level of interest in the data and the answers provided.

      • Chris says:

        ... however, slicers do not provide input. They are purely a filtering device for data that has already been input. Unless I'm unaware somehow, it is not possible to use a slicer for data input... making this pretty, but not actually functional.

  3. Priya says:

    hi,
    Very Nice Article...
    Thank you for sharing...

  4. David says:

    I like it Chandoo, good one. And concerning data input - hook up a named range to the databodyrange the slicer is driving and fetch that value to input in a table using a bit of VBA. Nifty.

  5. George says:

    I have this table with one cell data by date grouped by month, Jan, Feb etc. This group of dated have been sorted by months so that January is the top and December is on the bottom. But when I go to create a slicer for this table using month as a check it populates with the months being sorted alphabetically.
    any idea on how to correct this so that I have the months in my slicer as intended?

    • MDavis says:

      George,
      I am a relative nuby to this site, so apologies if I misunderstand your issue. I just saw sort non-alphabetic and thought I would offer.
      I see a couple of possible solutions.
      One would be to name your months with their number order included ('1 January', '2 February' and so on) if that won't be distracting or mess up your tools esthetics.
      Another could be to create/apply the month sorting mechanism. My current version of Excel has a place you can pick or add a custom list by choosing the data to sort and right-click, hover over sort to pick 'custom sort...' from the fly out, and then pick the order you want from the sort order using the drop down. You can create your own list as well, but month order is already there.

  6. Mehul Soni says:

    i create clinic management in excel but some where problem in date wise list, am/pm wise list patient name list. so sir please help me
    i attach

Leave a Reply