This is a Chart-Pro’s tip!
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
- 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..
- Select only the Products column (including the headers)
- Create a Pivot from the Insert Tab
- 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
- Drop the Products in rows
- You’ll see all products appearing in the Pivot Table
- Right click on Products in the Pivot
- And then choose Add as Slicer
- 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
- Link the VLOOKUP formula to the Pivot Table value
- Copy the formula to the rest of the cells
- Delete the drop down
- 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..
- Check out the check button chart
- Use Camera tool to make dynamic charts
- Infographic Charts – Part 1
- Inforgraphic Charts – Part 2
- Target Charts in Excel