How do I use And and Or operators with Excel's Advanced Filter?

Unit Price and Units In Stock

An Advanced Filter and And
Now, suppose you wanted to know which products with a price of $20 or more have 10 or less units currently in stock. This filtering task has two requirements - two criteria - and you want to satisfy them both. In other words, the product must be $20 or more and have 10 or fewer units in stock. An AutoFilter just can't do that, so let's try an Advanced Filter.

The criteria range, in this case, requires only two columns: Unit Price and Units In Stock. You could copy just those column headings to an out of the way place. I recommend copying all of the headers - you might need them for another filter.

This gallery is also available as a TechRepublic blog post. Image created by Susan Harkins for TechRepublic.

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.