Excel Slicers as Selection Buttons

This is a Chart-Pro’s tip!

If you have been tired of using drop downs or selection buttons (form-x/active-x controls) in your dashboard/charts then it is time that you learn something really cool and fancy

You can instead use Slicers as Selection Buttons!


If you already know about slicers then you must be aware that slicers are applied in a pivot table or a normal table which can slice or dice the data

But you can also use them without making a “real” pivot table… well almost! I can’t explain any better if I don’t show you an example.. so let’s get right into it


Assume this Scenario

Excel Slicers as Selection Buttons 2

  • On the top we have a small data – product volume sales for 6 years
  • As a dummy calculation – We have one selected product (via a drop down) and its sales
  • And then a chart which shows the sales for the selected product


The Problem is – that unless you explicitly mention it somewhere, no user will self get to know that there is a products drop down placed to change between the products

Solution – Well if you are not a formatting freak.. this problem won’t even move the needle for you. I am, and I know a lot of people who are formatting geeks and keeping in mind that you’ll be one of us someday.

I propose to replace the drop down with a slicer for selecting the product. Why? Because slicers are more intuitive and classy


To do that

First Create a Dummy Pivot..

Excel Slicers as Selection Buttons 3

  1. Select only the Products column (including the headers)
  2. Create a Pivot from the Insert Tab
  3. Place the Pivot Table on the same sheet


In the Pivot..

You’ll just have one field because we just selected a single column.. which is exactly what we want

Excel Slicers as Selection Buttons 4

  1. Drop the Products in rows
  2. You’ll see all products appearing in the Pivot Table
  3. Right click on Products in the Pivot
  4. And then choose Add as Slicer
  5. You’ll get a slicer

Now when you click on the slicer.. you’ll see the pivot changing


Link the Pivot with the Dummy calculation formula

Excel Slicers as Selection Buttons 5

  1. Link the VLOOKUP formula to the Pivot Table value
  2. Copy the formula to the rest of the cells
  3. Delete the drop down
  4. Now work with the slicer and see your chart changing!

In case you have been wondering that my slicer looks different than what I created in the first place. It is because I formatted it to make it look pretty (coz I am a formatting fanatic). Read about Slicer formatting in detail


By now I am sure you are waiting to DOWNLOAD THE EXCEL FILE


Some more cool charts hacks..

  1. Check out the check button chart
  2. Use Camera tool to make dynamic charts
  3. Infographic Charts – Part 1
  4. Inforgraphic Charts – Part 2
  5. Target Charts in Excel


Topics that I write about...

Download Smart Ebooks on
Excel and Power BI