Advanced Dynamic Filtering with CHECKBOXES in Excel

Hi, in this video I'm gonna show you how to perform advanced dynamic filter with

checkboxes in Excel. The checkboxes is located in the Insert tab of the ribbon

and it allows us to display and edit true and false values. So I'm gonna see

how we can use them in our formula to perform advanced calculation. Enough of

talking, let's get started. Now I'm gonna be playing around with this sample sales

data set and of course the data is officially stored in an Excel table. I

can double check in the table design contextual ribbon tab and I can see data

as the table name. And of course we want to perform or go through these criteria

We want to filter this data set to display the records for the account

manager named Tina John and Conor Rolibet with customer type segments that

equal to small business and home office with total sales that is greater than or

equal to 3,000. I'm gonna show you how we perform dynamic selection of column

based on filtered results in the past and then I'm gonna show you the modern

way to do that with the new checkboxes. So let's say I'm gonna come to cell I5

and type in the filter. Now the first argument of the filter is the array so

I'm gonna select the entire data set from the top here excluding the headings

put in a comma. Now for the include argument since we want to perform the

records for account manager that equal to Tina John and Conor Rolibet, I'm gonna

open two brackets. So for the first one I'm gonna come to column C and check the

account manager are you equal to Tina John in I2. Close the brackets. Now this

is gonna be a logical operation so I'm gonna use the plus math operator open

the second bracket again account manager the main site are you equal to Conor

Rolibet in cell J2. Close the two brackets for now. Close the filter. Let's

see what this is delivering. When I hit control enter, there we go. So we can see

this return all the columns and of course you see the account manager equal

to Conor Rolibet and Tina John. Lovely. Let's undo this customer type that equal

to small business and home office. So double click in the top cell and of

course I'm gonna get rid of the closing brackets and I'm gonna use the

multiplication. This is gonna be and logical operation. So two opening brackets again I want to check the customer type in column B are you equal

to small business in K2. Close the brackets and then for the R I'm gonna

use the plus sign open that in the bracket and again the customer type in

column D are you equal to the home office. So close the two brackets and

then I can close the filter for now and let's double click or click control

enter and there we go. So this return the account manager for Tina John and Conor

Rolibet and of course for the customer type we have the small business and home

office. This is fine. Now let's undo the last criteria which is the total that is

greater than or equal to 3,000. So double click in the top cell again I'm gonna

delete the closing brackets and again multiplication sign to perform the and

open the bracket and then we'll check the total in column G. Are you greater

than or equal to 3,000. Close the bracket for the total column and then close the

bracket for the filter. Control enter. There we go. You can see these are all the

records that satisfied the criteria we specified and of course you can see we

have 11 count of transaction. Now this actually return all the columns but we

want to return the specific three columns the customer type, product name

and the total. So I'm gonna come to the top cell. Now this is what I do in the

past. I'm gonna nest another filter outside and it's gonna be the input value for the

array argument of the outermost filter and then I'm gonna put in a comma. Now

for the include I'm gonna use the count ifs or the count if. Now for the count ifs

I'm gonna provide the criteria range one. It's gonna be the unique columns that I

want to return. Put in a comma. Now for the criteria one, it's gonna be the

original column from the data set. So you can close off the count ifs and then I

can check what this is delivering. It's gonna return 0 1 0 1 and so on and so

forth. 0 simply means hey don't give me that column. 1 simply means hey return the

column in your final result. So I can close off the outermost filter and

control enter. There we go. So we can see the customer type, the product name and

the total. Now the amazing part of this calculation is that when I change this

to account manager, I want to change the product name to account manager. There

we go. It's dynamically updated. This is cool. Now we want to see how we can use the new

checkboxes to control the filter. So I'm gonna come to the top cell here. This

very include arguments. So I'm gonna grab this and replace with the checkboxes in

row number three. That's cool. When I hit enter, it's gonna return a calc because of

course no particular checkbox is already checked. But when I check this product

name, there we go. We have the product names. Now I'm gonna uncheck that and we have the calc

So we can actually treat the calc error nicely. So I'm gonna come to the last

argument of the filter, the if empty. So I can actually use the double quote to give

me empty string. So when I control enter again, we've got this return to nothing. I'm

gonna delete this or clear all this labels or the headings. So let's clear

all and this is where the formula is. Double click. Now that's what I'm gonna

do. I'm gonna use this calculation in my let formula. So let's continue in

formula bar. So I'm gonna come to the formula bar and alt enter and I'm gonna press

control shift u to expand the formula bar. So I think let's just indent this to

make it a little bit readable. Control or alt enter. I'm gonna come here. Let's just

press alt enter. I think this is more better. So I'm gonna use the let function

which allows us to assign calculations to names. So for the first argument, the

name one, I'm gonna call this one filtered results. Just a label, a name and

it's gonna govern the name value of this calculation. Lovely. Let's just check

around. I'm gonna put in a comma. Now for the temporary calculation, you can

actually call the name one which is filtered result. I can close up the

bracket and I hit enter. This returns nothing again because no checkbox has

been checked. When I check account manager, of course this returns the account manager's name

based on this criteria. I'm gonna clear the checkbox. I'm gonna come to the cell

Okay, the cell. Now this is what I'm gonna do. I'm gonna come back to the let and

this is gonna be the name one. I'm gonna press alt enter. So let's create a new name one

I'm gonna call this one put in a comma. Now put in another comma. So carefully

come inside this name value one. In this case, I'm gonna use the filter function

because I want to return all these original headers and for the include

argument, I want to return all the checkboxes in row number three. That's

fine and then don't forget to close off the filter and there we go. Now let's just

check around. Let me check this. Let's just evaluate this header name. So

I'm gonna delete this and type in the header. Call it. When I hit control enter, of course this

returns an error. So what I'm gonna do is I'm just gonna come back to the same

filter that we just created. Put in a comma. So if empty, we want to return an

empty string. Control enter. That's lovely. Okay so I'm gonna double click. Now let's

continue in the formula bar. I'm gonna press alt enter to move the formula down

So I'm gonna get rid of these. Delete. Backspace. Okay I'm gonna press alt enter

again to jump to the next line. Now this is what I intend to do. I actually want to

stack them vertically using the VStack function. So for the name three, I'm

gonna call this one calc. Put in a comma. For the name value three, I'm gonna use

the VStack function which vertically stacks array into an array. So press the

start key. So for the first argument, I want to actually stack this header. So I'm

gonna call that header. Put in a comma. And then for the array two, I'm gonna call

the filtered result. I can close up the brackets and then I can put in a comma

Now for the final calculation, I'm gonna just call this calc name that we defined

So calc and close the brackets. Fold the legs. Now this is the moment of truth

When I hit control enter, this will return nothing. Fine. Now let's see use the check

boxes to control the results. So when I check account manager first, I can see

hey we have the list of account managers name and the heading. Lovely. So let's say I

want to check the total. So just click on the total in column G and there we go. So we

can see the total column and the account manager. Now let's include the customer

type. Now the customer type is actually coming before the account manager. So

when I check this customer type, there we go. You're gonna see the customer type first

then the account manager, and then we have the total. Let's say I want to include the

product with the check box. There we go. We have the product name. Let's say I want to

include the unit. That's it. Lovely. And I can even uncheck. So when I uncheck

product, there we go. It's gone. When I uncheck the account manager, it's gone. I can

even add the other date. There we go. So you can see the other date, customer type

unit, total. So this is basically how we can use the amazing check boxes to

control the columns we want to return based on our field tag result. I hope you

enjoyed this video. If you do, like it, share with your friends, and comment. Thank you

and bye for now. Cheers