That’s a rubbish title, I know. I couldn’t think of a succinct way to describe what I’m about to show you.
I’ve combined a few techniques to create this interactive PivotTable and PivotChart using Slicers:
Probably the easiest way to understand how I’ve put this together is to download the file, which you can do here:
Excel Slicer Trick Techniques Used
- PivotTables – In columns B:O is a PivotTable (called Category_Pivot). I’ve actually hidden all but column O of the actual PivotTable. Clicking on the + symbol below the formula bar will reveal the full PivotTable:
Tip: I’ve used the Group tool on the Data tab to group the month columns together. I can then easily hide/unhide them with the +/- buttons, as opposed to right-click > Hide to hide columns.
You can see the separate columns in the expanded view below. These month columns feed the Sparklines in column P:
- Slicers - I’ve positioned the Slicer over the top of the PivotTable row labels, as you can see in the image below where I’ve moved the Slicer out of the way to reveal the row labels in column B:
I used the formatting options of the Slicer to align the buttons with the row labels behind.
- Sparklines – I’ve inserted Sparklines in an empty column (P) to the right of the PivotTable, that references the hidden month columns.
Tip: Set the handling of the hidden data in the Sparklines Design tab so that they still display when the columns are hidden:
- PivotCharts – I have another PivotTable hidden out of sight in columns Y and Z (called Product_Pivot). This is linked to the Slicer and feeds the PivotChart that displays the Product level of detail for the Category selected in the Slicer:
Tip: The Slicer does not control the first PivotTable (Category_Pivot) that you see in the animation, it only controls the Product_Pivot Table which feeds the PivotChart.
- Conditional Formatting – Lastly I’ve set up some Conditional Formatting rules to fill the cells in columns P and O in a light blue to match the selection in the Slicer.
The rules check if the value in the second PivotTable filter (cell Z5 above) matches the row label in the first PivotTable. If they match, it fills the cell colour in a light blue to match the Slicer button and chart bar colour.
Tip: Conditional Formatting in the PivotTable Values area is applied differently to Conditional Formatting of the Row labels.
Tutorials on Techniques used:
Visit the links below to learn more about the techniques used in this example.
- Conditional Formatting PivotTable Values Area
- Conditional Formatting PivotTable Row Labels
Want More Excel Superstar Skills?
When you have a broad range of Excel skills you can combine them to create innovative solutions like I've show above. And this is exactly what I aim to do in my Excel Dashboard course where I cover a range of topics which can be used individually, or combined to provide slick reporting solutions that will be the envy of your colleagues.
If you want to learn more cool techniques like this so you can impress your boss and stand out from the crowd, then please take a moment to check out my Excel Dashboard course.
You can also watch a short sample video from the course which shows a clever use of Symbols in charts, like you see here in the chart X axis labels:
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.