Excel AutoFilter Makes Spreadsheets Useful

Two of the Excel filters I rely on when working with spreadsheets are AutoFilter and Custom AutoFilters. These are features Microsoft added with Excel 97, but few use it. Using these filters, I can turn an ordinary Excel spreadsheet into something more useful and versatile.

For example, let’s say your boss owns a wine shop and he just got a wine shipment. Your task is to restock the shelves. You also know that someone from marketing will call in the next couple of days asking about these wines as well. The monthly newsletter always profiles one winery and includes recipes for specific wine types.

Taking a proactive approach, you figure you can add a couple of columns to the Excel spreadsheet and use it for many purposes. The first task is to stock the shelves. The wines are mainly sorted by type of wine and bottle size since the 1.5-liter bottles don’t fit the standard shelves. You add two columns to the Excel sheet for “1.5L” size bottles and “Type”.

Excel file before autofilter

What is AutoFilter?

AutoFilter is an easy way to turn the values in Excel column into specific filters based on the cell contents. As example, by adding AutoFilter to the list above, I could filter the “Winery” column to only display items from Beauregard. All the other wineries stay on the Excel list, but don’t display.

To turn on autofilter,

  1. Click any cell within your range.
  2. From the Data tab, click Filter.
  3. Excel data tab

Once you’ve enabled this feature, your columns display with a drop-down arrow to the right. If you click the arrow control, you’ll see all the values for that particular column. To turn off the feature, you deselect Autofilter from the same submenu.

Excel file with autofilter

In the example above, I can see all the entries that show in the YEAR column. Various years are omitted such as 1998 since that value isn’t represented on the spreadsheet.

What’s appealing about this filter feature is that the displayed list is dynamic. If I shift to the 1.5L column, you’ll see I have two more entries: (Blanks) and (NonBlanks). Excel includes these values since this column includes blank cells. You did not see that choice for “YEAR” since there were no blank cells. And since I haven’t added anything to this column, no other values show.

Autofilter drop down selection

What’s equally useful is these filters also adjust based on other autofilters. For example, if I filter the “Winery” column for “Ridge Vineyards” as shown below, my autofilter list for “YEAR” only displays Ridge values. Although the shipment contained items from the year 2000, none were from Ridge.

Excel spreadsheet with second level filter

Excel Custom AutoFilters

As handy as these filters are, there are times when you need to filter based on specific criteria within a cell. Going back to our first task of sorting bottles sizes, we can use a custom filter to find these items. This works because the distributor puts 1.5L in the “Description” line.

To set a simple custom autofilter,

  1. Enable autofilter for your spreadsheet using the steps in the section above.
  2. Click the down control arrow in the column you wish to filter.
  3. From the drop down list select (Custom…) The Custom AutoFilter dialog opens. Your column name should show above the first list box.
  4. In the first list box, click the drop down arrow and select your criteria.
  5. In the list box to the right, either type a value or select one from the list.
  6. Click OK.

In the example below, I’ve elected to filter for rows containing “1.5L” anywhere in the description. Once my filtered records appear, I’ll add an “X” in 1.5L column.

Excel custom autofilter dialog

Excel is versatile in the filter settings. You’re not limited to just items containing a specific value. You could also use:

  • equals
  • does not equal
  • is greater than
  • is greater than or equal to
  • is less than
  • is less than or equal to
  • begins with
  • does not begin with
  • ends with
  • does not end with
  • contains
  • does not contain

Sometimes you need a more complicated filter. For example, I may want to select a row if two conditions are met. I might also be interested in selecting a cell if one or another condition is met.

Using our wine store example, I might want to filter wines that have Cabernet in the description, but not Cabernet franc. The procedure is similar to above, but I added another condition and used the “And” radio button. My second condition also uses the “does not contain” option.

Excel autofilter with and/or filter

Once I’ve filtered for a specific wine term(s), I can quickly enter the value in the “Type” column for the resulting set. I can repeat this process for each wine type such as Zinfandel, Syrah, Chardonnay and so on. You may meet some examples that don’t fit your filters. In my spreadsheet, some descriptions don’t show the type of wine such as Monte Bello.

Essentially what Excel’s autofilters allow you to do is to slice and dice your information. About the only limitation is if you have more than 1000 unique items in a list. It won’t help you empty the wine boxes, but it can help you decide where items should go. If you find some Monte Bello, save it for a special occasion. One when you’re far away from a computer.