Excel Slicer Trick

Mynda Treacy

October 13, 2015

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:

Excel Slicer Trick

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.

By submitting your email address you agree that we can email you our Excel newsletter.

Excel Slicer Trick Techniques Used

  1. 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:

Slicer Buttons

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:

PivotTable expanded view

  1. 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:

Slicer reveals row labels

I used the formatting options of the Slicer to align the buttons with the row labels behind.

  1. 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:

Sparklines

  1. 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:

PivotChart

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.

  1. 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.

Conditional Formatting

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.

  1. PivotTables
  2. Slicers
  3. Sparklines
  4. PivotCharts
  5. Conditional Formatting PivotTable Values Area
  6. 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:

dashboard chart with symbols

Excel AGGREGATE Function

Combine Excel Worksheets with Power Query

33 thoughts on “Excel Slicer Trick”

  1. 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

    Reply

    • 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.

      Reply

    • 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

      Reply

  3. 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

    Reply

  5. 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?

    Reply

  7. 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. 🙂

    Reply

    • 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

      Reply

  8. 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

    Reply

    • 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

      Reply

      • 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.

        Reply

  10. 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

    Reply

    • 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

      Reply

  11. 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

    Reply

    • 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

      Reply

Leave a Comment

Current ye@r *