How to use Table slicers for advanced interactivity in Excel

Having looked at how to use slicers with PIVOTBY and FILTER in a previous post. Let’s take this a step further and discover how we can use Table slicers for more advanced user interactivity.

Table of Contents

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0198 Table slicers for user interactivity.zip

Watch the video

Table slicers for advanced interactivity in Excel | Excel Off The Grid

Watch the video on YouTube

Example

The data for our example is straightforward. The Data table has 3 columns: Date, Item, and Value.

Data for Example

From this Table, we created a Date slicer and placed it on another sheet.

Getting the table slicer selections

With standard PivotTables the slicer selection determines what is displayed in the PivotTable. If we select March 2023, the values displayed are for March 2023.

However, if we can use formulas to extract the slicer selected values, then we can do almost anything we like with them. We are not restricted to displaying only the selected items.

Let’s see how we can get the list of slicer selections from the Date column of the Data table.

Slicer selected items function

The formula in cell E6 is:

=LET(
list,UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,"")))),
FILTER(list,list<>"")
)

This is quite complex, so let’s break down this formula to understand what it does.

SUBTOTAL(3,r)
  • 3 tells SUBTOTAL to perform a COUNTA operation (it counts numbers, text and boolean).
    • Rows that are visible, calculate as 1
    • Rows that are hidden by the filter, calculate as 0
  • r is a placeholder to represent each row in a range. We will see more of r in the coming sections.

Next, let’s consider the IF function.

IF(SUBTOTAL(3,r),r,"")

In our scenario, SUBTOTAL will be calculated on each cell in a column.

  • If the row is visible, SUBTOTAL returns 1 (which is equal to TRUE). Therefore, IF returns r (the cell value).
  • If the row is hidden, SUBTOTAL returns 0 (which is equal to FALSE). Therefore, IF returns a blank text string ( “” ).

The next section is a BYROW/LAMBDA combination:

BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,"")))

The BYROW/LAMBDA combination performs a calculation for each cell in the Data[Date] column.

As it calculates, each cell in that column is represented by r. And, each r is which is passed into the IF/SUBTOTAL functions.

It starts with the first cell in the Data[Date] column which has a value of 31-Jan-2023. If the row is visible the formula above returns 31-Jan-2023, if not it returns “”.

This continues for each row, and the results are joined together to return a list of values.

This result could contain duplicate values; we need to remove these.

UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,""))))

UNIQUE returns one instance of each visible value.

Assuming 31-Jan-2023 and 31-Mar-2023 are selected, the value returned by UNIQUE will be {31-Jan-2023;””;31-Mar-2023}.

Finally, lets remove the blank text string. We use the FILTER function for this.

=LET(
list,UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,"")))),
FILTER(list,list<>"")
)

We don’t want to include the UNIQUE/BYROW/LAMBDA/IF/SUBTOTAL combination twice. For efficiency we use LET to create a variable called list. Then, we use FILTER to remove any empty text strings from list.

The result of this formula is a list of the items selected by the slicer.

Calculate selected items

WARNING

In scenarios where you may have empty text strings in the Table column, change the “” to a value that will never appear in your column.

Changing and applying table slicer selections

Now we have the list of selected values, we can use them as we wish. Let’s suggest we want to include the value to date based on the maximum date selected.

FILTER with total to date

The formula in cell G6 is:

=FILTER(Data,Data[Date]<=MAX(E6#))

Even though 31-Jan-2023 and 31-Mar-2023 are selected; the formula returns all values which are less than or equal to 31-Mar-2023.

If we had multiple years in our data, we could calculate year-to-date as follows:

=FILTER(Data,(Data[Date]<=MAX(E6#))*(Data[Date]>=DATE(YEAR(MAX(E6#)),1,1)))

As you can imagine, there are almost an infinite number of scenarios we could use here.

Disconnected slicers

If we have multiple slicers connected to the same Table, they all influence each other. For complete freedom, we can use separate Tables with a slicer attached to each.

In the example file, we also have a separate Table called DisconnectedTable that only includes a Date column.

Disconnected Table

We created a slicer for this Table and placed it on another sheet.

Disconnected Table Slicer with FILTER

The formula in cell E6 is:

=LET(
list,UNIQUE(BYROW(DisconnectedTable[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,"")))),
FILTER(list,list<>"")
)

This is exactly the same formula as before, but it references the DisconnectedTable[Date] column.

For the FILTER in G6 we can use exactly the same function as created in the section above.

The formula in G6 remains as:

=FILTER(Data,Data[Date]<=MAX(E6#))

If we want to include the items selected in the slicer, we would use the method from this post: How to FILTER by a list in Excel (including multiple lists). For that scenario, the formula would be:

=FILTER(Data,COUNTIFS(E6#,Data[Date]))

We can use the slicer values to control almost any formula. Hopefully, you can see how flexible this approach is.

Slicer selections LAMBDA

To make this easier to apply, we can create a reusable function.

Copy the following into a named range, and call it fxSlicerSelection

=LAMBDA(tableColumn,LET(list,UNIQUE(BYROW(tableColumn,LAMBDA(r,IF(SUBTOTAL(3,r),r,"")))),FILTER(list,list<>"")))
fxSlicerSelection LAMBDA

Now we can use fxSlicerSelection as a standard formula.

Using fxSlicerSelections for table slicers

The formula in cell G6 is:

=FILTER(Data,Data[Date]<=MAX(fxSlicerSelection(DisconnectedTable[Date])))

We have used our custom fxSlicerSelection function directly in the FILTER function.

NOTE:

To move the fxSlicerSelection formula to another workbook, we only need to copy the cell to another workbook and the named range will move with it.

Conclusion

In this post, we have gone deep into Table slicers. We have seen how to:

  • Get slicer selections as a list – extract the selected items
  • Change filter context – adjust a user’s slicer selections
  • Create disconnected slicer tables – separate tables that exist purely for creating slicers
  • Build a reusable LAMBDA function – removes the need to write a complex formula each time we want to use this technique

In this post, we have only used the FILTER function, but we could use these in any dynamic array formula.

Related Posts


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

2 thoughts on “How to use Table slicers for advanced interactivity in Excel”

  1. Love your website! In this exercise I get an error because “r” is not accepted as part of the function/formula… I can’t figure out what I am doing wrong. Any thoughts?
    Using Microsoft Office Pro 2019. Thanks!

    Reply

Leave a Comment