Most of the time in Excel an AutoFilter would enable you filter lists in the ways you need to,  But, in some cases you may need or want to exert more control of over the way filtering operates.  If this is the case then using Excel advanced filters is the way to go.

Let’s back up a bit here, if you want to use Advanced filtering then we need to know about Boolean logic expressions,  sounds complex but it really isn’t, and you probably know them already.

An example would be where you want to filter a shopping list so only those items less than $10 or those with a special offer discount of more than 20%, you will need to know how to write Boolean Logic that describe these conditions in which the price of an item is less than $10 or the special offer discount of more than 20%.  So just how do you express these conditions.

Figure 1 below, shows the available operators all you need to do is use then with a value from your condition.

figure 1.

Figure 2 shows my shopping list set up with items, prices and if there is a special offer discount on an item.

figure 2.

So let’s construct the Boolean expression by using an operator from the Table 1 and a value for the comparison.

Insert a few rows above your list.

This is where we will enter the filter criteria.  Set up your cells as below(figure 3)

figure 3.

Under Price enter <10
In the second row under Special Office discount enter>20

If you were to write the >20 in the same row as the <10 for the Price it would be querying Price<10 and Special Price Discount > 20 ( not what we want for this advanced filter)

Lets do the filtering!

• Select any cell in your data
• Data- Advanced Filter

Select the cells as below-

1. Use this to Filter the List in its current place or copy the results to another location
2. This is the place for the range of values
3. This is the place for the range of criteria
4. Tick this to filter unique values only

Click Ok and tada your list is filtered to your criteria specified.
If you want to download a sample workbook to play around with Advanced Filtering you can do so here.

Other Excel Tips You Might Like.

1. The SUPERCHARGED copy and paste function

2. Highlight cells that contain formulas

3.  Delete Values but keep formulas

4. Remove carriage returns from a CSV file