• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Use Excel Slicer Selection in Formulas

You are here: Home / Excel Formulas / Use Excel Slicer Selection in Formulas
slicer selection in formula
December 15, 2015 by Mynda Treacy

Slicers are a great tool for incorporating interactivity into your reports but we don’t always want to analyse our data with PivotTables and PivotCharts. Nor is our data always in the perfect format for PivotTables.

So, let’s look at how we can use the Excel Slicer Selection in formulas which will enable us to create interactive reports that use regular charts with Slicers, like the one below:

Use Excel Slicer selection in formula

Note: Slicers are available in Excel 2010 onwards.

Download the Workbook

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.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

The Data

For this example my data is spread over two sheets, each in an Excel Table. The Tables have Names; Actual and Budget. You’ll see these names in the Structured References in my formula later.

data

Insert a Quasi PivotTable

Since Slicers are connected to PivotTables the first step is to insert what I call a quasi PivotTable, which I've linked to my "Actual" data Table. This is a PivotTable that only has row labels for the items I want displayed in my Slicer.

For example, I want a Slicer for my Category field so I’ve created this quasi PivotTable in cells L4:L12:

insert quasi pivottable

Notice how it has no Values field, only the Category field in the Rows area.

Tip: You can put the field in the columns or filters area instead. It doesn’t really matter. I like to use the Rows area so that if multiple items are selected in the Slicer then they will fill down a column as opposed to across a row, or displaying the text ‘Multiple Items’ in the Filters area.

Insert a Slicer

Next I need to insert a Slicer for the PivotTable Category field.

It’s dead easy; with a cell selected in your PivotTable go to the Insert tab > Slicer. In the dialog box (image below), select the field you want to insert a Slicer for:

insert Excel Slicer

Note: if you already have a Slicer inserted, you can connect it to the quasi PivotTable by right-clicking the Slicer > Connections > check the box for the quasi PivotTable.

I’ll also change my Slicer caption to say "Select One" to give some guidance to the user. To modify the caption: right-click the Slicer > Slicer Settings, type in a new caption and click OK:

Change Excel Slicer caption

Tip: The reason I only want one item selected in the Slicer is because I’m going to use the SUMIFS function to summarise my data and I’ll be using the selection in the Slicer as one of the criteria. Remember, SUMIFS treats each criteria as AND, so it cannot handle more than one category. For that you would have to use the SUMPRODUCT Function.

When you select an item in the Slicer the row labels are filtered to only display the selected item(s), which can be seen in cell L5 below:

Excel Slicer selection

You can see in the name box in the image above that I’ve given cell L5 the Name "slicer_selection", which I will use in my SUMIFS formula.

Use Excel Slicer Selection in Formulas

The table that feeds my chart is in cells H4:J16:

Use Excel Slicer selection in formulas

The SUMIFS formula in cell I5 is:

=SUMIFS(Actual[Actual],Actual[Category],slicer_selection, Actual[Month],">="&Report!H5, Actual[Month],"<="&EOMONTH(H5,0))

Notice how the third argument references “slicer_selection” which is the name I gave cell L5. It’s as simple as that, I’m just referencing the row label in the PivotTable that displays the item selected in the Slicer. The SUMIFS formula automatically updates to reflect any changes in the Slicer selection.

Here is the English translation for my SUMIFS formula:

SUM the Actual column of the Actual Table IF the Category in the Actual Table Category column is the same as the Category in the slicer_selection cell, AND the date in the Month column of the Actual Table is greater than or equal to the date in cell H5, AND the date in the Month column of the Actual Table is less than or equal to the end of the month (EOMONTH) date in cell H5.

The limitation is that SUMIFS can only handle one selection in the Slicer. If more than one item is selected then only the first item is reflected in the chart.

Dynamic Chart Label – The Icing on Top

Notice how the title in my chart reflects the selection in the Slicer?

Excel chart

I’ve linked my chart title to an IF formula in cell N4:

Use Excel Slicer selection in formulas dynamic label

It picks up the Category selected in the Slicer from the PivotTable cell L5 called "slicer_selection" and concatenates the text “Actual vs Budget Sales”, and if more than one Category has been selected it displays this message in the chart title:

“Please Choose Only One Category”

Benefits of Using Slicer Selection in Formulas

Obviously Slicers were designed to work with PivotTables and PivotCharts, and if you have Excel 2013 onwards then you can also use Slicers with Excel Tables.

However, with this workaround we can use Slicers in formulas thus enabling us to summarise data spread over two or more tables of data, which would otherwise require Power Pivot and or Power Query.

This method also enables us to use regular charts which are more flexible than PivotCharts.

Final word: In the Slicer settings you may have noticed a "name to use in formulas" (see image below). This is only for use with Power Pivot models and requires the CUBERANKEDMEMBER function or VBA, but that’s a post for another day.

Use Excel Slicer selection in cube formulas

slicer selection in formula

More Slicers Posts

Symbols in Excel Slicers

Symbols in Excel Slicers is a fun way to make your reports more interesting. Here are some tricks to getting wingdings and symbols to display in Slicers.
Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts can open up a huge range of possibilities and the best part is you can use Slicers to control regular charts too.
Excel Slicer Trick

Excel Slicer Trick

In this Excel Slicer Trick I show you how to use a Slicer to display a drilled down view of your data in a PivotChart. Download the file and follow along.
Excel Slicers for Fiscal Years

Excel Slicers for Fiscal Years

Inseting Excel Slicers for Fiscal Years is easy but requires a bit of DIY date classification.
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

Creating Excel Slicers for rolling periods is easy with this IF formula. Download the workbook and watch the video.
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

How to create a single Excel Slicer for Year and Month fields. Includes written instructions, video and workbook download.
Excel Slicers

I’ll Have a Slicer That!

How to insert and use Excel Slicers. Includes video and step by step written instructions. Use Excel Slicers to create interactive Charts and PivotTables.
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: slicers
Previous Post:Manual Chart TableCreating Excel Charts from Multiple PivotTables
Next Post:Excel ROWS and COLUMNS FunctionsExcel ROWS and COLUMNS Functions

Reader Interactions

Comments

  1. John

    June 23, 2021 at 10:05 am

    Hi,
    I’m working with membership data in a table and have a column with a range of years, from 0 to 70 yrs, for volunteers.
    I’d like to group the years in increments of 10 :. 0 to 9, 10 to 19 etc. to be able to easily track (notice) how many volunteers are approaching milestone anniversaries – 10 yrs increment :. 10, 20, 30, 40, 50, 60, 70

    Is there a way to make this work with a slicer getting the data from a table (Pivot Table?

    Reply
    • Mynda Treacy

      June 23, 2021 at 10:19 am

      Hi John,

      Add a column to your source data that stores the group for each volunteer. Then you can use that field in your PivotTable and Slicer.

      Mynda

      Reply
  2. Kat Taylor

    February 19, 2020 at 12:20 pm

    Hi Mynda,

    Thank you for a very useful tutorial. I have one comment – it would be helpful to add an explanation for people who already have reports with a pivot table and slicers on how to attach these to the new quasi-pivot table (the step where you change the existing slicer’s pivot table connections to add the new quasi one).
    I’ve looked up this tutorial a number of times – each time i’m either creating a new report or improving an old one, and each time I’ve spent a while trying to remember how I’ve done it last time 😉

    Cheers,
    Kat

    Reply
    • Mynda Treacy

      February 19, 2020 at 2:31 pm

      Good idea, Kat. I’ve added a note 🙂

      Reply
  3. Bill

    September 14, 2019 at 12:57 am

    Can this be used in a custom print header?

    Reply
    • Catalin Bombea

      September 14, 2019 at 11:46 pm

      Hi Bill,
      Almost any problem has at least one solution, most of the times imagination is the only limit.
      The short answer is yes, but based on your description, I cannot describe a solution, there are many details missing to understand your process.
      If you need assistance, prepare a detailed description of what you want to achieve, prepare a sample file, and upload it to our forum, we will help you.
      Regards,
      Catalin

      Reply
  4. Peter Bartholomew

    September 6, 2019 at 8:22 pm

    Nice article. I hadn’t seen it until Ingeborg Hawighorst referred to it.
    I will now add the technique to my armoury!

    Being me and never knowing when enough is enough, I modified your workbook to accept multiple categories. I first produced an array of results but chose to sum them for the chart.

    Reply
    • Mynda Treacy

      September 6, 2019 at 8:24 pm

      🙂 thanks, Peter.

      Reply
  5. Angela K Knowles

    December 19, 2018 at 5:55 am

    I have a table with Sales and Purchase amounts and 2 slicers. One slicer to pick the Department in the Store and the other slicer to pick the year/Qtr. I need to accumulate the sale amount in another column. I also need to calculate the Margin. Do you know how to deal with the moving rows?

    Reply
    • Mynda Treacy

      December 19, 2018 at 7:55 am

      Hi Angela,

      It’s possible to do these calculations inside the PivotTable, but I’m not sure what you mean by ‘moving rows’. Can you please post your question and sample Excel file on our forum so we can see what you’re trying to do?

      Mynda

      Reply
  6. Marie

    February 1, 2018 at 2:52 am

    Maybe I’m missing something, but it seems that the addition of the line-chart, and the subsequent ability to use the Slicer to adjust the view of the line-chart, is missing in the above tutorial. I have simple data, with a list of regions and percentages for several months. You seem to move from the slicer selection formula, right to the dynamic chart label.

    I’ve created the slicer/pivot, but at this point, I don’t need a sum, I simply want the line chart to display data based on what Slicer button is selected.

    Reply
    • Mynda Treacy

      February 1, 2018 at 10:04 am

      Hi Marie,

      This tutorial is about using the Slicer selection in a formula, as opposed to how to create charts and Slicers, so I agree it’s not going to be much help to your scenario.

      When you say ‘I want the line chart to display data based on what Slicer button is selected’ this is exactly what the Slicer should do automatically. If your Slicer isn’t controlling your chart then it sounds like the Slicer isn’t connected to the PivotTable that feeds your chart. Try right-clicking the Slicer > Slicer or Report Connections > check that the PivotTable for the chart is selected in the list.

      If that’s not it, please post your question and Excel file in our forum where we can troubleshoot further for you.

      Mynda

      Reply
  7. Ron

    July 27, 2017 at 5:00 pm

    Hi,
    Great tutorial, which will help me solve my excel issues. It will help me huge, when the missing graphics will be shown. Are you maby able to fix them?

    Reply
    • Philip Treacy

      July 27, 2017 at 7:58 pm

      Thanks Ron.

      Sorry about the images, should be all displaying now.

      Reply
  8. Di

    June 28, 2017 at 10:21 pm

    Interesting! How do you handle it when you don’t pick any category but your filter is cleared out (All)?
    Thank you!

    Reply
    • Mynda Treacy

      June 29, 2017 at 7:35 am

      Hi Di,

      That’s similar to selecting multiple items in the Slicer i.e. more than one item is selected and the SUMIFS formula can’t handle multiple criteria for the same criteria range.

      In that case you can use the SUMPRODUCT function with an OR operator between each criteria to accommodate multiple items selected, or if you just want to handle one selection or no selections, then you could wrap your SUMIF/S in an IF function that checks to see if there are no selections made, in which case it would just SUM the range, otherwise use SUMIF/S to accommodate the selection in the Slicer.

      If you have a lot of items in your Slicer then trying to accommodate every option will make for a very long SUMPRODUCT formula. In that case you might be better off trying to work with PivotCharts, or build a dynamic named range that references the PivotTable range and use that as your chart source range for a regular chart.

      Mynda

      Reply
  9. Marcos Zattar

    March 14, 2017 at 8:16 pm

    This is great, Mynda, thanks!

    How do you manage to get the chart to change smoothly?

    Cheers,
    Marcos

    Reply
    • Mynda Treacy

      March 14, 2017 at 8:26 pm

      Hi Marcos,

      That’s a built in feature available in Excel 2013 onwards.

      Mynda

      Reply
  10. Jeff Baisch

    February 18, 2016 at 7:17 am

    I can now understand how to use this useful tool. After converting one of my reports that consists of 20 different tabs based on inventories down to 3 tabs I was very happy. However, I emailed the report out to all the recipients, come to find that Slicers are not usable on smartphones. I open the spreadsheet on my Android and get the message:

    “Slicers aren’t fully displayed or interactive. If you data was previously filtered, it’s shown in its filtered state.”

    Is there any way to use spreadsheets with slicers on smartphones?

    L.E.: We are using Excel 2010 to create the files.

    Thanks

    Jeff

    Reply
    • Catalin Bombea

      February 18, 2016 at 11:11 am

      Hi Jeff,
      Even if the slicers are created with Excel 2010, to view them and use them you have to use Excel 2010 or higher versions, or with Excel Online.
      You can upload them on OneDrive, and view them with a browser, with Excel Online, instead of using the software installed on your smartphone.
      Catalin

      Reply
      • Mynda Treacy

        February 18, 2016 at 1:29 pm

        It will depend on your phone. On my phone opening a file containing Slicers in Excel Online in Firefox doesn’t display the Slicer at all 🙁 My phone is quite old though!

        Reply
        • Catalin Bombea

          February 18, 2016 at 9:25 pm

          Mine still has keyboard, it’s not smart 🙂 , I cannot test on it…

          Reply
          • Mynda Treacy

            February 18, 2016 at 9:41 pm

            😀 now I don’t feel so bad about having a 4 year old phone!

  11. Godfred Owusu Adjei

    February 1, 2016 at 9:01 pm

    Nice tutorial,I will appreciate if you can share with me the video tutorials.
    Thanks

    Reply
    • Mynda Treacy

      February 1, 2016 at 9:23 pm

      Glad you liked it, Godfred.

      There’s no video for this tutorial.

      Mynda

      Reply
  12. Ken Witchel

    January 19, 2016 at 2:39 am

    Mynda, thank you for the very interesting post. I hope to implement the slicer instead of using a dropdown. It is possible to use the slicer selection to automatically expand when more data is added to the table? For example, if I created the original slicer to select Year when the table currently had data for the years 2010 to 2015. Now that I am adding 2016 data, could “2016” be automatically added to the slicer selections? Or would the original pivot table of Years first have to be refreshed?

    Reply
    • Mynda Treacy

      January 19, 2016 at 8:25 am

      Hi Ken,

      You have to refresh the PivotTable for the Slicer to pick up the new year, but then you’d want to refresh the PivotTable if you added new data to the source anyway.

      You can set the PivotTable to automatically refresh upon opening of the workbook, or with some VBA you can set the PivotTable to refresh upon change of worksheet selection which I teach in my Excel Dashboard course.

      Kind regards,

      Mynda

      Reply
  13. DIEN

    December 18, 2015 at 1:40 am

    Could you create more the option to choose multiple category? For example I want to show the chart which compare actual vs budget for 02 products.Or show the chart of top 3 actual vs budget

    Reply
    • Mynda Treacy

      December 18, 2015 at 11:26 am

      Hi Dien,

      It’d be easier to consolidate the data with Power Query or Power Pivot and insert a Pivot Chart than try and write a formula to handle a variable number of items selected in the Slicer.

      Mynda

      Reply
      • Michael May

        September 8, 2016 at 3:28 pm

        I have a PT on which I have multiple slicers that can be applied so would the Power Pivot be a better option in order to ensure that the unsliced total of the PT (when slicers are applied) always equals the total of the source data?

        Thanks

        Reply
        • Mynda Treacy

          September 8, 2016 at 5:54 pm

          Hi Michael,

          Power Pivot would be the only way to do this.

          Mynda

          Reply
  14. Paula

    December 18, 2015 at 1:16 am

    Great post – very creative! I tried this with a slicer on the table and don’t believe this works. When a table is sliced, it filters the list and the data stays in its original cell, so naming the first cell in the table list does not return the desired result.

    Reply
    • Mynda Treacy

      December 18, 2015 at 10:32 am

      Good point, Paula. We could probably write a formula to extract the visible cell, but it’s easier just to use the PivotTable method.

      Reply
  15. T N Ramanathan

    December 17, 2015 at 1:02 pm

    Excellent Artcle.

    Reply
    • Philip Treacy

      December 17, 2015 at 1:06 pm

      Thanks, glad you liked it 🙂

      Phil

      Reply
  16. Anika

    December 17, 2015 at 10:01 am

    Brilliant and very useful in a lot of situations. Thank you

    Reply
    • Mynda Treacy

      December 17, 2015 at 12:30 pm

      Thanks, Anika. Glad you’ll be able to make use of it.

      Mynda

      Reply
  17. Ellen

    December 17, 2015 at 4:00 am

    This is something that will help me tremendously! One question though, what if my data is not broken down by month but rather by day?

    Reply
    • Mynda Treacy

      December 17, 2015 at 5:37 am

      Hi Ellen,

      I’m glad you’ll find this useful. My SUMIFS formula assumes the data is broken down by day. I just happen to only have one day date for every month.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x