Put your foot down and speed up your workbooks. Excel advanced filters are fantastic.
Free Microsoft Office Tutorials
This lesson is going to focus on Excel advanced filters in Microsoft Excel 2003. Advanced filters are a very effective and powerful way of filtering data. I could not begin to explain to you just how effective advanced filters can be. You can do just about anything with huge sets of data.
Don’t be put off by the title Advanced filters. When you understand how they operate, advanced filters are quite easy to use.
Watch this brief tutorial
How do Excel advanced filters work?
Have a look at the illustration below and you will see that in Excel 2003 you locate the advanced filters tab by clicking Data menu tab then click Filter then Advanced Filter.
You can see by this illustration that the Excel advanced filters dialogue box consists of three ranges.
- The first is your List Range. This is your data set including your headers.
- The second is your Criteria Range. This is the criteria by which you filter the data set, it must also include the header.
- The third is the area to Copy to. You can omit this and filter in place if you wish.
It is extremely important to note that the header in the data set and the criteria and the copy to range must be formatted exactly the same and have the same text.
It is possible to have multiple criteria both horizontally and vertically.
You can use operators within that criteria.
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
These are the most common operators and when you are proficient with these there are even others you can add to make your filters more effective.
It is possible to have your list filtered to another sheet. If you wish this to happen you need to initiate the advanced filter from that sheet.
Watch this Excel 2010 Advanced filter tutorial you will see that the process is exactly the same. Advance filter 2010
Excel advanced filters for Microsoft Excel 2003.