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:
Enter your email address below to download the sample workbook.
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.
- PivotTables
- Slicers
- Sparklines
- PivotCharts
- 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:
Some sure useful tips and instructions – thank you sooooo much!
I’m in the process of building a product selection tool – we have many products with some complex parameters but thanks to pivot tables and slicers I have been able to present a simple tool to help customers find what they need.
i’m fine tuning some of the data at the moment and I’m wondering if there is such a thing as a “Clear All Filters” button I can add so that the individual slicers don’t need to be reset?
I’m using excel in Office 365.
Cheers,
Eddie
Great to hear, Eddie! There is a clear button in the ‘Sort & Filter’ group on the Data tab of the ribbon which clears all filters on Tables and PivotTable. You can add this to the Quick Access toolbar or assign the command to a macro button in your worksheet. Hope that points you in the right direction.
I would have gone to Pivot table options and turned off Autofit column widths on update. That would look better
Hi Michael,
I did for the main PivotTable that’s visible in the report (see animated image). The PivotTable in columns Y and Z should really be on another sheet ‘hidden out of view’ and therefore the column width changing doesn’t matter. I only put it on the same sheet for the purpose of the tutorial.
Cheers,
Mynda
Whoops got it!
No worries, Michael. You weren’t the first to think the second PivotTable was part of the report. I should have made it clearer.
Mynda
The key, at least for me, is the second pivot table. I know this because it just occurred to me that a second PT could have saved me enough time to earn that PhD in philosophy I’ve always wanted 🙂
Awesome as always, Mynda. I wish your courses were qualified to earn CPE credit for licensing of US CPAs.
Thanks always for sharing your brain,
Jeff
Aw, thanks, Jeff 🙂
We’re planning on working on CPE accreditation next year, but it’s a long road!
Mynda
What a great idea to combine all these functions – very clever. A nice clear and clean report.
Cheers, Kim 🙂 Glad you like it.
Mynda
Excellent. You alway share excellent examples and they are very easy to follow. I plan to share a few of these features to my students.
Can’t wait to test the link on Excel Slicer Trick…… that is how I will spend the next couple of hours. 🙂
Side note:
Do you have any examples, tutorials on INDEX and MATCH?
Thanks, Karen. Enjoy playing around with it. I’m sure there are many variations of how you could use Slicers and PivotTables.
You can read this INDEX and MATCH tutorial: https://www.myonlinetraininghub.com/excel-index-and-match-functions
Mynda
Wow! What a great use of a number of handy little tricks! Learned a lot from this post.
Cheers, GP. Glad you enjoyed it 🙂
Used this on our ytd financials. Category included Compensations, PP&E, Supplies, Travel etc. and the categories within them included Salaries, Wages, airline travel, lodging, Leases, machine maintenace, etc.
VP loved it. But, now to make it more valuable, I would like to incorporate 2014. Will this mess it all up?
thank you so very much for this. 🙂
Hi Julie,
Wonderful to know you have already used this tip.
You can incorporate prior year data, but you’ll obviously have to play around with the formatting. It shouldn’t be too hard to put the Years in separate columns and have them as separate series in the chart.
Let me know if you get stuck.
Kind regards,
Mynda
Hi Mynda
As always an excellent article.
However, being a “nitpicker”, I would format Product_Pivot Report Layout as “show in Tabular form”, then it would show Product, rather than “Row Labels” as the heading.
Equally, I would sort the PT Descending by Product Sales.
Then, to ensure that the Order in the Chart, matched the order of the PT, I would format the Vertical Axis as Categories in Reverse Order, and set the Horizontal Axis to cross at Maximum category.
Look forward to seeing you in Seattle again in just over two weeks!!
Regards
Roger
Hi Roger,
As a PivotTable expert you’re allowed to nitpick 🙂
Actually the Product_Pivot is supposed to be out of sight so the formatting and ‘row labels’ heading didn’t matter to me, but a great tip for those who might want to show the PivotTable as well as the PivotChart. Likewise the sorting.
See you soon.
Mynda
Hi Mynda
Just after I posted, I realised that you meant the Second PT to no be visible (hence the columns it was in), so the formatting of that would not be relevant.
However, my comments did address the later concerns of Michael.
Love that! Makes data look good and simple to understand. Win win
Cheers, Jason 🙂
Hi Mynda,
The information is illuminating as always.
I noticed the information in the chart is sorted in descending order while the information in the pivot table (columns Y-Z) is sorted in ascending order. This is distracting to the viewer who is trying to understand the information presented to them in the spreadsheet.
Is there anyway to make the chart and pivot table sort the same as each other?
Thanks,
Michael
Hi Michael,
The PivotTable in columns Y-Z would normally be on another sheet out of site as it’s sole purpose is to feed the PivotChart. I only put it on the same sheet for the purpose of the tutorial.
That said, you can align the sort order of the chart and PivotTable by editing the Chart Axis: right-click > format axis > Axis Options > Values in reverse order.
Kind regards,
Mynda
Hi Mynda,
This was so cool. I like that your explanations and visuals are so clear. I’m already dreaming of ways I can use these tips to apply to my work. I’m sure it will take some practice. Now if I can just free up a bit of my workload to play around with it. 🙂 Thanks!
Tricia
Thanks, Tricia. Glad you’ll find it useful…if you can find the time 🙂
Nice stuff. Thanks for sharing!
Thanks, Nate 🙂
How can I minus(-) from cell A1 and automatically add(+) in B1? If A1=10 and B1=12 then I enter 7 in A1 then B1 will show 15.
Hi Shovan,
You can’t do that without vb programming, excel never keeps the old values to be added to the new values, any formula will refer only to the current value of cells. You have to rethink the way you input data, for example, you can enter the values in A1, then A2, A3, and so on, and B1 will be : =SUM(A1:A10). This way, B1 will collect all values from column A.
Cheers,
Catalin
Very nice!
Cheers, Oz 🙂 Glad you like it.
Mynda
What Excel version is this for?
Hi Little Fish,
Excel 2010/2013/2016. Unfortunately if you have Excel 2003/2007 you don’t have Slicers.
Mynda