Excel advanced filter How to create advanced filter in Excel tutorial for beginners 2010 2013 2016

okay in this next video we're going to talk about how to do an advanced filter in Microsoft Excel in a previous uh video we were able to do basic filters with an advanced filter you can add more criteria and go a little bit further than the basic one so once again it would be really important that you have your field names in the first row of your data like we have here and then each row after that is a different record or a different transaction and each column is a different field so we call this a a table or a database or a list in Microsoft Excel those terms can be used interchangeably now um when you have an advanced filter you have to set up your criteria and the criteria is a different section of the spreadsheet and it has the same field names as your original data so what I'm going to do is I'm going to highlight the field name so I can just copy those I'm going to right click on that section and do a copy now the criteria can be on the same sheet as your original data or it can be in a different sheet I want to put this one in a different sheet so I'm going to pick on sheet two and then we'll just do a paste and it copy the headers over the headers really do have to be the same as the original data again the the criteria can be in the same sheet or in a different sheet so what's going to happen here is you can have as many rows of criteria as you wanted it to so let's say I want to see all the people whose uh office is Chicago and whose destination is Boston okay so that would be one row of criteria again I can have as many rows as I wanted to when something is on the same row then all of those has to all of those have to be true at the same time to be included in the filter let's say uh I want all the destinations that are New York work that are over or that that are commissionable sales so the commission is either a yes or a no so I want all the ones that from New York that are uh that have commission let's say I also want to see all of the ones from uh London that are over $300 in the amount column so I'm going to type in greater than 300 so I can use greater than I can use less than if you have the greater than sign and the equal sign next to each other then that that means greater than or equal two if you have less than and the equal sign next to each other that means less than or equal two or if you have something like this if you have um the less than sign and the greater than sign next to each other like that that means not equal to all right so you have different ways to put in your criteria here and then let's say say I want all of the ones whose office is Philadelphia so I'm just going to type in Philadelphia in the office column uh you can make these bigger if you wanted to but it doesn't really matter it'll still work either way so uh in this case I'm looking for all the ones whose office is Chicago and the destination is Boston both of those have to be true I also want to see the ones from New York that are uh a yes in the commission field I'm looking for the ones that are a destination the destination is London and that's over $300 and then I want all of the ones whose office is Philadelphia so we're setting up our criteria the next step is to actually go ahead and try out your Advanced queer uh filter once you have that set up so I'm going to go back to sheet one now and you just click on one cell that's not blank and then you're going to pick in the data menu notice how we have the filter but you're actually going to go into the advanced section so let me cck on Advanced okay there's two ways to do an advanced filter one is called a filter in place which is going to show the results right within the original data or we can copy the results to another location and keep the original data intact I I'll show you both of those so we're going to say filter the list in place now the list range is the actual original data and usually it guesses that properly in this case it did it's going to go from A1 down down the I 203 and you can see that's exactly where my data is many times it does guess that properly for you otherwise you can specify your own range but really it does pick that it does do a good job of picking the right range for you um I'm going to pick on the criteria range now the criteria is on sheet two so watch I'm just going to click on the criteria range come down to sheet two and then we're going to highlight uh the appropriate cells we're going to highlight from A1 over to I5 you don't want to include any blank columns and you don't want to include any blank rows because otherwise um you may get unpredictable results you certainly want to start where the field names are and then go down as many rows as as you needed to but don't include any blank rows and don't include any blank columns so now we have the list range we have the criteria range and I'm going to click on okay okay now let's see if it worked uh we we wanted the ones that were London that were over $300 so you can see that that worked we have the ones from Philadelphia and then let's see uh I believe I had the ones from New York that were yes in the commission field see all the New Yorks and they all have yes there uh so our criteria really did come out that's the whole advantage of the advanced filters that you can put in uh multiple lines of criteria now when you're done with that data you can click on clear and then you get all of your records back now notice what that did it it it it um shows you the results right within the original data here's a different way to do an advanced filter I'm going to pick on advanced again a data Advanced and then this time I'm going to say copy to another location that means it's going to take the results and put them somewhere else on the spreadsheet uh so that we can keep our original data intact the list range is still going to be A1 through i23 I want to pick on the criteria range and go down to sheet two and that's still going to be A1 through I5 but we have to specify that and then the copy two where are the results going to be copy to so I'm going to click on that uh item for some reason the copy to always has to be on the same sheet as the original data but what you can always do is you can copy and paste your results onto a different sheet or even a different workbook if you wanted to want to pick on copy two all you really have to do is pick on one cell and then it'll take as many rows and columns that it needs after that so I'm going to click on okay notice what it did it left the original data intact but if we come over here to column k then it actually has the results of the query remember we said show me all the ones from London that are over $300 all the ones that are new New York that are yeses and you can see they really put that into a different part of the spreadsheet from there uh we can work with it just like it is or we can copy and paste that to a different sheet uh or a different workbook all right so the idea of the advanced filter is you have a whole list of data it's very helpful that the first Strow contains your field names in fact for the advanced filter it's necessary and then on a separate section of the spread of the workbook it could be on the same sheet or it could be on a different sheet you're going to have the field names so each row is uh is going to be a logical and so it's going to be this and that and then every row after that is an or so I'm going to say Chicago and Boston or New York and yes or London and over $300 or 30 or Philadelphia so you know each row within itself is an and but each different row is an or and you can really start to build really complicated uh situations here so let me go back to sheet one once we have our criteria to run the advanced filter you pick on data and then Advanced the filter the list in place will show you the results right within the original data and the copy to another location will take the results and put them somewhere else so we can keep the original data uh intact and now you should have a pretty good idea of how to run an advanced filter