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
Looks slick, eh? Read on.
Slicers as selection mechanism – step by step tutorial
Just follow below steps to create your own slicer selection tool.
- Enter running numbers (say 1 to 10) in a range, with the header numbers
- Select the range and create a pivot table from it.
- Select numbers from field list and add it as a slicer. We get this.
- Select the slicer, go to Options ribbon and set up,
- Slicer columns as 10
- Height & width as small numbers (0.3 cm or 0.1 inch)
- Disable slicer header. Right click on the slicer and go to slicer settings. Uncheck Display Header option.
- At this stage, your slicer selection tool is almost ready. I say almost because it will still have a border and default style.
- 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.
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.
- A very comprehensive introduction to slicers
- Using slicer as scenario manager
- Slicer in play – tax burden over the years
- Slicers in play – narrating story of change
- Slicers in play – dynamic dashboard in Excel
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.
18 Responses to “Use slicers to create a cool selection mechanism [quick tip]”
What happened to the AMA?
It was over. You can find the thread here.
https://www.reddit.com/r/india/comments/44aeo6/hi_i_am_purna_duggirala_chandoo_excel_blogger/?sort=new
I just removed the blog post from public view as it is no longer relevant.
Excellent, was worried it had been cancelled. Will have a read now.
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?
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.
... 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.
Did you look at the example?
I did. The usage of a dynamic table to show the status of a slicer is not 'data input'. This result is not entered into a data table that can then be ingested, pivoted, mined, and using in reporting.
Just because you don't understand how it can be used, doesn't mean it cannot.
Those slicers can produce numeric outputs with VBA, all he's showing you is an example, the rest you can just surmise yourself...
I understand the VBA. I understand the example. The article header and description state and describe "use slicers as selection mechanism"... which is not accurate. The article doesn't mention VBA at all.
But, thank you for your condescending reply - so useful!
Hi Chris,
Thanks for your comments. Please refer to the download workbook in this page to understand one way to harvest Slicer values.
http://chandoo.org/wp/wp-content/uploads/2016/02/slicer-selection-example.xlsx
For more, also read the introduction to slicers page.
http://chandoo.org/wp/2015/06/24/introduction-to-slicers/
I did not spell out everything as these concepts were discussed in earlier blog posts. Also, please note that you do not need VBA to access slicer selection values. You can use either CUBE formulas or helper pivots.
hi,
Very Nice Article...
Thank you for sharing...
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.
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?
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.
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
@Mehul
You didn't attach anything as you can't here
Can you post the question at the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file and you will get a more targeted response
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