fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

18 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

  7. Joe says:

    This is a nice technique, more visually aligned to what users see these days on mobile devices, etc.

    Regarding harvesting the selection(s)...Users can select more than one item in a slicer and I see two scenarios:

    1. The application "wants" a single selection (like radio buttons). In this case one could test for number of values selected and if > 1 one, take some action such as using the max selected, treat it like "no selection", etc.

    2. The application "wants/allows" multiple selections (like check boxes). In this case, how to know which ones are selected? Only idea I have is to use prime numbers (2,3,5...) instead of integers (1,2,3...) and use product in the pivot table. Combinations of various selections will result in unique products.

    Any thoughts? Am I making this too complicated?
    Regards,
    Joe

Leave a Reply