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.
Watch the video
The example data
The data used in the example is as follows:
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:
- SUBTOTAL – https://exceljet.net/excel-functions/excel-subtotal-function
- AGGREGATE – https://exceloffthegrid.com/aggregate-the-best-excel-function-youre-not-using/
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.
The formula in Cell B11 is:
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).
The formula in Cell C3 is:
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.
- Select a cell inside the Table
- Click Insert > Slicer from the ribbon
- The Insert Slicers dialog box will open
- Check the required column
- Click OK
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.
The formula in Cell C5 is:
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.
- 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.
The formula in Cell E5 is:
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.
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.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
4 thoughts on “Using Slicers with dynamic array formulas in Excel”
Easier with ..
That is a tidy little formula. Thanks for sharing 😀
The SUBTOTAL() or AGGREGATE() ideas is really useful. Just what I needed in trying to use the new (in preview) ability of charts to refer dynamically to Dynamic Arrays in order to incorporate TreeMap charts in a dashboard controlled by Silcers.
Great news, I’m glad I could help. I hadn’t thought of that application, but it’s that’s a great use of the new Charts + Dynamic Array functionality.