Home // Video Tutorials // 6 - 10 (Advanced) // 6 // Excel Advanced Filter

Excel Advanced Filter

Using the Excel Advanced Filter to improve your filter and autofilter capabilities.

Excel Advanced Filter

The excel advanced filter tool allows you to run some sophisticated filters through a data base something like this.

In order to make it work what you need to do is have a separate section that specifies what you are looking for. The easiest way to do this is to take the headers of your data base, copy and paste them to a new section and then put your criteria in –so for example, here you’ll see I want Sales people 002, I want them to be bigger than 500 for product A, bigger than 500 for product B, for C and I want Salesperson 3, less than 500 for A, less than 500 for B but don’t worry about C. Now we can click under the Data tab, Sort and Filter the advanced button. What you’ll see Excel does – it tries to guess where the information is but says tell me where the list of information is. So we say highlight from…… to….. and then Excel will ask what the criteria is and in this case it seems to have guessed correctly so that’s fine. It could ask for unique records but for now just ignore that and I click ok. You’ll see what its done – its filtered the information only for items that meet the criteria. If you remember all of these need to be more than 500 – we had one situation where salesperson 3 was less than 500 for those 2 but this one didn’t matter. To get back to the full list, there’s a button called clear, click it, you’ll see everything comes back. Let’s do the same thing now but only look at the Unique Records. Look at this data base, you’ll see the items in yellow are exact duplicates of each other. You’ll see the numbers are exactly the same, the date, the sales person, etc. I am just going to remove this criterion. Click Advanced. It has asked for the list, give it again, the criteria is fine. Now I am going to say I only want to see the unique records. Now when I say ok you’ll see what its done is shown only the unique records. Again to get back to the full list. Click clear. Just to point out, when we click Advanced, you’ll see that there are 2 options here- you can either filter the list in place which does exactly what we saw. Only the relevant cells are highlighted. We could have clicked copy to another location- in which case we could have said take the information and put it into another spreadsheet or another group of cells.