Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Using Slicers with dynamic array formulas in Excel

Using Slicers with Formulas - Featurd Image

A few years back, I created a YouTube video about using slicers with formulas in Excel. It was a pretty popular video. That method used a dummy PivotTable, to act as the criteria for the formulas to calculate on. Today, I want to bring you a cleaner option for using slicers with formulas. This method uses only an Excel Table and dynamic array functions.

Slicers are an excellent tool for adding interactivity. When a user clicks on a slicer button, the results change to include only those selected items. Slicers are compatible with PivotTables, PivotCharts, Cube formulas, and Tables…but not standard formulas. So, let’s see how we can use a Table in a new way to get around this limitation.

This approach is deceptively simple. But like most things in Excel, the skill is knowing how to connect features together.

The example data

The data used in the example is as follows:

Source Data

This is a Table called List, with a single column, also called List.

Visible row functions

In Excel, there are special functions that only calculate on visible rows. I believe there are only two SUBTOTAL and AGGREGATE.

We will not go into SUBTOTAL or AGGREGATE in detail in this post. But if you want to know more, here are some useful references:

For our example, we will use the SUBTOTAL function.

The screenshot below shows a Table with the COUNTA, visible cells only, version of the SUBTOTAL function at the bottom. The total counts the number of visible rows. If we filter the Table, the total changes. Notice that the total at the bottom changes from 8 when unfiltered, to 3 when we have selected 3 items.

SUBTOTAL with COUNTA as the Total

The formula in Cell B11 is:

=SUBTOTAL(103,[List])

The first argument of 103 tells SUBTOTAL to count cells while ignoring hidden rows; [List] is the Table name.

Instead of using SUBTOTAL for the total, let’s add a column called Include and use SUBTOTAL on each line. This method returns 1 for each visible row, or 0 for a hidden row. Of course, we can’t see the hidden rows, so we can’t see the result of the formula, but the hidden rows are 0 (I promise).

SUBTOTAL with COUNTA on each row

The formula in Cell C3 is:

=SUBTOTAL(103,[@List])

As we are using an Excel Table, the formulas copy down into each row automatically.



Create the Slicer

Let’s create the Slicer to use with our data.

  1. Select a cell inside the Table
  2. Click Insert > Slicer from the ribbon
  3. The Insert Slicers dialog box will open
  4. Check the required column
  5. Click OK
    Insert Slicer List

Clicking the Slicer will filter the Table.

Connecting the Slicer to the formula

The next step is to get the plumbing to work between the Table and a formula.

We will use the FILTER function to return only the visible rows from the Table, which is only those with 1 in the Include column.

FILTER Connected to a Slicer

The formula in Cell C5 is:

=FILTER(List[List],List[Include]=1)

This formula returns an array of all items in the List column where the corresponding value in the Include column is equal to 1 (i.e., only the visible rows).

To learn more about the FILTER function, check out this post: FILTER function in Excel

Now, start clicking on the Slicer; as the selection changes, so does the returned array.

Using a LAMBDA (advanced)

Are you thinking: “Do we really need to use that extra column in the Table? Can’t we just create one formula to handle this?”.

The answer is, Yes, we can.

Notes:

  • I must give thanks to Sergei Baklan for helping with this formula. I created something that worked but was hideous to read. Sergei was able to create a much nicer function; therefore, I have used Sergei’s formula below.
  • At the time of writing, this solution uses functions only available on the Beta channel. Therefore, this may not work on your version of Excel.

LAMBDA Function

The formula in Cell E5 is:

=DROP(REDUCE("",List[List],LAMBDA(a,v,IF(SUBTOTAL(103,v),VSTACK(a,v),a))),1)

This post isn’t about LAMBDA functions, so we won’t go through the formula line by line. Effectively the formula applies the SUBTOTAL to each row in the Table. All the rows which return 1 (i.e., those which are visible) are stacked into a single array, and returned to the worksheet.

To use for your scenario, just replace List[List] with the Table and column name you are using. The remainder of the function will just do its thing and spill the required results.

Conclusion

Excel does not give us a simple way to use Slicers with formulas. With this approach, we can extract the selections with a dynamic array function, and then use those values to drive other calculations.

Check out my previous YouTube video for other formula techniques that can be used with this approach.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Using Slicers with dynamic array formulas in Excel

Leave a Reply

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