Learn advanced filtering in Excel by tracking flying fish

The fish-throwers at Pike Place Market are one of Seattle’s most popular tourist destinations.  These guys can toss virtually any marine animal—salmon, crab, cod—and certainly they can catch!

Flying fish might wow visitors, but if you own the fish shop, one of your main concerns is likely how much you have to pay suppliers.

What is Advanced Filtering?

Many of you already know how to use Excel's AutoFilter feature to do basic data-sorting; for example, how to use it to select a single name from a long list of names. But what if you want to do more advanced filtering?  Let's say you want to only filter items that fall within a specific range or data that meets multiple criteria. For the sake of this scenario, say the manager of a fish shop wants to filter for suppliers that sell Copper River Salmon for less than $9 per pound and Sockeye Salmon for less that $11. Excel's Advanced Filter feature can help the manager find the data she needs.

We'll be using a sample workbook to track purchases from suppliers. It includes the following data:

• Date of purchase
• Supplier Name
• Seafood type purchased (e.g. Copper River Salmon, scallops)
• Pounds for a particular purchase
• Cost for the purchase
• Average cost per pound
  
  
  Sample Workbook

Get Your Data Ready for Advanced Filtering

It only takes two steps to set up your worksheet for advanced filtering.

1.  Insert a few blank rows above the data you plan to analyze. These new rows are where you will insert your advanced filtering criteria:

2.  Copy the column headers from your data to the first row of the advanced filtering criteria section you just created (see the red highlighted row in the image below). 

NOTE: Be sure to use the identical column header names, or your advanced filtering query will not work. 

Create Filter Criteria

Now we want to insert the advanced filtering criteria to extract the data we need. For this scenario, we want to filter for suppliers that sell Sockeye Salmon for less than $11 per pound and Copper River Salmon for less than $9 per pound. 

1.  Under the Seafood Type header in the advanced filter criteria section, enter Copper River Salmon on the first row and Sockeye Salmon on the row beneath.

2.  Under the Average Price Per Pound header in the advanced filter criteria section, enter <$9 on the first row and <$11 on the row beneath.

Filter Your Data

Now that our workbook is properly configured and we've selected the data we want to work with, we can perform the advanced filtering operation.

1.  Go to the Data tab in the ribbon and under the Sort and Filter section, click the Advanced button.

2.  On the Advanced Filter dialog box, click the highlighted button next to the List range field.

3.  Now we need to select the data we want to analyze, which in this case is all the data in the workbook. Select everything,including the column headers, and you'll see the range you selected in the Advanced Filter List Range box.

NOTE: Only select the actual data - do not select the rows for the advanced filtering criteria section you created above.

4.  Click the button at the right edge of the Advanced Filter - List Range box to return to the Advanced Filter dialog box.

5.  On the Advanced Filter dialog box, click the button next to the Criteria range field.

6.  In the worksheet, select the advanced filter criteria range section, including the column headers.








7.  Click the button at the right edge of the Advanced Filter - Criteria Rangeto finish selecting the advanced filtering criteria. This will once again return you to the Advanced Filter dialog box.

8.  Click OK on the Advanced Filter to apply the advanced filter.

All Done!!

You can see that the workbook now displays only data for suppliers that sell Copper River Salmon and Sockeye Salmon for less than $9 and $11, respectively. 

The manager can now easily see which suppliers to buy from. Better deals on Sockeye and Copper River Salmon mean lower prices for customers. Each fish they buy, the more fish fly. The more fish fly, the more customers she can lure (no pun intended) to the Market.

Let us know if this topic proved useful by leaving a comment. We'd like to hear from you.