A Bit About Advanced Filter In #Excel

Here is how you can use Advanced Filter for AND or OR scenarios in Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Using Advanced Filter is quite simple and very useful. You can certainly use the standard filtering options in Excel to extract data based on certain criteria, but Advanced Filter goes a step or two further. Here are some examples.

Advanced Filter can be found in the Sort & Filter group on the Data tab:

af1

In our scenario we have a list of inventory items. We will perform the following filters:

1) Single-criteria filter
2) Double-criteria AND filter
3) Double-criteria OR filter

Single-criteria filter:

Here is my data range, next to which I’ve set up a header and the criteria. Make sure the header for the criteria is an exact match of the header from the data range:

af2

I’ve set my criteria as “Two piece hub”. Click on Advanced Filter and set your List Range, Criteria range, and Copy to as shown (I’ve chosen to have the results displayed on this worksheet starting in cell L1. You can locate it on a different worksheet or filter it in place where the existing data is located):

af3

And here is the result:

af4

Double-criteria AND filter

For two criteria, the process is quite similar, just expand the Criteria range:

af5

And it will result as follows:

af6

Double-criteria OR filter

Finally, if you want the filter to be based on OR criteria, just put the two or more items on different rows, like this, and expand your range to include them both:

af7

Excel has filtered the data to include those rows of data that included “Two piece hub” OR “Austin”:

af8

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

Trackbacks/Pingbacks

  1. A Quick Way To Filter In #Excel | Excel Bytes - April 17, 2014

    […] we looked at Advanced Filter (take a look at it here if you didn’t get a chance to view it), and today I want to show you a very quick way to […]

Leave a Reply