3 Ways to Filter Data in Excel

hi I'm Jamie and welcome to teachers  Tech today I want to show you how to   filter your data in Microsoft Excel we'll  start right at the simplest way and then   we'll work up to the advanced filtering  and then I want to show you how to use a   function to filter your data in Microsoft  Excel so let's get started with this today   there's a practice file Down Below in  the description you can follow along with me if you're a beginner in Microsoft Excel  filtering is an easy thing to get started

with so here's the data that we're going to start  with our basic filtering I'm just going to click   somewhere within this data that I have here  this is just a range no table in here now I'm   going to go up to the ribbon and make sure you're  under the Home tab and then go over and find sort   and filter I'm just going to drop down on this  and I'm going to click filter so if I go ahead   and click this now notice at the headers I have  these little drop downs here this is where I can   apply my filters let's say if I drop down on year  I can go at this point and pick a certain year so   if I say I want 2018 I'm going to go the fast  way would be to uncheck them all and then pick   2018 I could pick multiple years as well so if I  wanted 2012 I hit okay notice that it comes back   um as the two selections from those two years  I can go back at this time and decide you know   what I want another year hit okay now I have a  few more but since I've done the one filter I   can apply another filter on here too maybe I'm  only interested in studio uh 3 in that so I can   go through and pick a studio so if I hit okay now  I can go ahead and I filtered it down to my year   that I want and Studio 3 so you can really go  through a lot of data using your filtering now   if I want to clear filters I can just go back  up to the top here and hit clear and they're   all back so just a quick way to get rid of them  now let's move a step forward and go to an   advanced filter to use the advanced filter I want  you to go up to the data tab here and take a look

in the ribbon under sort and filter uh notice that  we have the same options that I showed you under   the Home tab or right clicking there's always a  number of different ways you can get to these I   want to go to the advanced here but I want to do  a little bit of stuff first I actually want to uh   go in this cell and I'm going to type year and  in some uh years that I want   to filter so it's going to be and this is kind of  random I'm going to say 2019 we're going to say uh   2015 and I'll just add one more here and we'll go  2012 just like that and you could pick any three   that you want I'm just going to click inside this  information here it could be in any cell and then   I'm going to go to the advanced so this's right  here so I'm going to select it notice that uh   since I was in it and sometimes you don't have  to be within it and it will just find the data   here and so list range right here and now criteria  range is what I chose here what I wrote the thing   is though you need to make sure that whatever the  header that you place over here needs to match one   of the headers over here so you can see I have  year here and I'm just going to   go and hit the criteria range and it's going to  be right here and hit enter and I'm back to here   you can also go uh multiple columns you just have  to make sure that again your header is going to   match the range from something within here too  so I'm just going to hit okay and now it's giv   me back those years so 2015 I can see 2019 and  a 2012 uh if you want to clear your filter if   you take a look up top uh we can go up and just  clear it and it puts it back so I could go and   enter some more information so if I was turning  this to a 21 here uh then I could go back and do   the same thing that I did before and go to advance  and uh choose it based on those what I like next   is I'm going to show you the function filter uh  that can do I find I like it how you can kind of   dynamically change things from one cell reference  so on this spreadsheet let's go ahead and set up

uh the space we need to use this function this  filter function and I'm going to go ahead and   select just the headings here and I'm just  going to go contrl C and I'm going to go contrl   V place them over here and I'll just highlight  all these columns and double click in between   to set the width quickly that way and I also want  to filter by year again so I'm going to type year   here and I'm just going to put a year that I want  to F filter by I'm going to change this but I'm   going to just type 2019 now I'm going to put the  filter function right in this cell here and to   to do it I could do the equals and start typing  filter but I'm going to go up to insert uh the   function here so insert function and if you don't  see it on your list you can type filter hit go uh   this is a Microsoft 365 subscription function so  if you're not seeing it that could be why if you   don't have that so I'm just going to go ahead and  select this filter so what do I need what's the   array well the array is going to be and I don't  want to include the headings is going to be   this area right here so that's my array what am I  going to include so this is the what I'm going so   I'm going to click into the include this is going  to be where it's searching like down here so I'm   highlighting everything in here but I also have  to add a step here so I'm going to be adding the   cell reference so I'm going to put an equal sign  in and I could type the year 2019 but I want to   be able to have it sell reference so if I change  that cell uh then it will change automatically   so I'm just going to click on this right here and  put it in and then this last part here if it it's   blank I just want it to return kind of blank so  I'm going to go and just put a couple quotation   marks there and I'm going to hit okay so and I'm  going to I can adjust this uh more here so you can   see this one and then I could do some formatting  on these uh as well if I wanted to add the dollar   signs but take a look 2019 so it's pulling from  here what I like about this method if I was going   to look for a different year I could just type  it in here so 2022 and now it's bringing me back   2022 this way you know it if you set any of these  up here versus the way when we're just filtering   if we were looking if you had a list of thousands  and you had to uh go and deselect and select all   the ones you want where you can just go ahead and  type it in here using the function so just some   tips about filtering kind of from the simplest  to the advanced filter to even using a function