Hi, this is Wayne again with a topic “Using the Excel FILTER Function to Create Dynamic Filters”.
In this article, i will show you how to use the excel filter function, to create dynamic filters in your spreadsheets, and you can see the spreadsheet that i’m working with is a list of movies, the categories that those movies would fall into and some other information about. The movies you can get a copy of this spreadsheet in the description below this video. If you want to follow along now, i would like to set it up so that i can type in this box the genre or category of movie that i would like to watch. And then i would like a dynamic list to be produced of the movies that i could choose from now. There are some ways to do this: without using the excel filter function.
I could click and drag to highlight the column headings and on the home, tab home ribbon. I could go to sort and filter and i could create a filter and then i could filter out only the specific movie category or genre that i’m looking for, but that’s going to be a little bit more work in the end than just using the excel filter Function so i hold ctrl and tap to z, to undo that, and i’m just going to go over here underneath where i’ve typed genre to watch or category to watch and i’ll click and type equals filter. You can see that excel identifies the filter function and this is a fairly new function in excel. If you have microsoft 365, you should have access to it after typing, in filter i’ll just put in a left parenthesis. Next excel is looking for an array. So what is the range or array that’s important to me in this spreadsheet? Well, it’s this! It’S the data, so i don’t need to get the column headings, but everything else, i’m just going to click and drag to highlight all of it all the way down to row 43.. Now i could have simply typed in the cell references a2, colon d43, a2 through d43, but sometimes clicking and dragging is a little easier. Next, i put in a comma now excel is looking for the information to include and what this means here is.
What range do i want excel to examine to see if a particular word in this case, a particular genre or category, is listed, so i’m going to click and drag on the data in column b, so from b2 down all the way to b43? Now i’ll browse back up just using the scroll wheel on the mouse or you could use this scroll bar if you want to so now, it’s going to include b2 through b43, and now i can put in a logical test, i can say, are any of those Categories equal to – and i should put this in – quotes – let’s say family, so family movies, and then i could put another quotation mark and then i need to put in a comma next. Excel is expecting me to put something in here in case the results come up empty. What? If none of these movies have a category of family? Well then i want to print so i’ll.
Put in quotation marks the word none close quote and then my right parenthesis tap enter and it identified all of the family movies in that range or in that array. At this point, i’m going to hold ctrl and tap z to undo that, because i want you to see that i could have handled this a little differently. I could have said equals filter left parenthesis selected. The array that i want to examine. So all of this data here going back up i’ll put in a comma next, is include so this is what’s going to be examined to see if i have a movie of a particular genre, and now the test equals now here, instead of putting in the word Family, i could just click on a cell like g5 h3, or i could even just type it in in this case i’ll just type in g1, and what this means is that excel will look and see if anything in this list is equal to whatever is in G1, now i put another comma in now excel is expecting me to put something in case. There are no results produced, so i’ll put in quotes the word. None close quote right. Parenthesis tap enter now, there’s nothing in g1, so excel returns. The word none now i can see. I accidentally changed my spreadsheet data, i’m going to change it back so now watch what happens when i type something in g1. Let’S say i type drama i tap enter. It lists all the dramas in my movie list. What if i click and change that to sci-fi my dynamic filter changes and shows me different results? This is one of the things that makes it dynamic. I can type in horror comedy whatever i want. It gives me a nice list of the movies that fit that category or genre.
Now, if i really wanted to, i could, instead of just having people type in the genre that they’re looking for. I could click here where it says comedy. I could go to data here on the data tab in the data tools group there’s a symbol here, a button that represents data validation.
So i can click there here in the settings for data validation. I could change it from any value to list where’s the source of my list. Well, i click the button with the arrow and i can show it here the source of my list.
I click this button again to get back. Click. Ok, so now look what we have. I have a button with a drop-down list and then i can change the word.
That’S in cell g1 and whatever i pick it will list the movies that meet that criteria. Now, let’s see if we can add one more component to this dynamic filter, first, i’m going to right click on k to insert another column and i’m just going to click on rating. I didn’t put that in the right place, i’m going to scoot it over here and i moved it just by putting my mouse right on the edge of the cell and then dragging that’s an easy, quick way to move text or other data from one cell to The next would it be possible for me to use the filter function, to create a dynamic filter that not only filters for let’s say romance fantasy horror or other genres, but also filters based on rating. Let’S see if we can do that. So if you recall, i typed in my filter formula here in cell f2 and when i click on f2, that becomes apparent here in the formula bar. So here’s my formula and what i’d like to do here is say that i want to filter that same range.
Based on, what’s in this range b2 through b43, making sure that that’s equal to g1, which is romance in this case, so i want to do that and at the same time i want to filter based on rating. In order to do this correctly, i need to put my first include in parentheses, so i’ve done that now. How do i say – and i want to search this – to make sure it’s equal to what’s in g1, and i want to search this to see if it’s equal to whatever is in k1. So how do i write and well? It doesn’t make a lot of sense at first, but the answer is you put in an asterisk, so now i can put in another left parenthesis.
What am i trying to include this time? Excel’S? Giving me hints still you can see that include, is in bold well, i want to include c 2 through c 43, so that now has been added to my formula in the formula bar, and i want to test to see if the data in column c basically Is equal to whatever is in k1, i put in my right parenthesis: tap enter and my formula produces none, but that’s because i have nothing typed in k1, let’s type pg and see what happens alright, that produced two movies that are both romance movies and rated pg. What about horror? That’S rated pg-13, i tap enter, it did not produce any. What about r i tap enter and it does produce results. Next, let’s try comedy that’s rated pg, i tap enter and i get results. So this is a dynamic filter that i’ve created using the excel filter function. Now, of course, if i wanted to, i could click here and create a drop down list similar to this one so that the user doesn’t have to type in pg-13 or whatever it might be. But i hope you can see some of the power of the excel filter function, there’s so much that you can do with it. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, if you’d like to support my channel, you can do that through my patreon account, or by buying channel merch and you’ll, see links to that in the description below the video .