Two Excel filters I rely on when working with spreadsheets are AutoFilter and Custom AutoFilters. These are features Microsoft added way back with Excel 97, but few use it. Using these filters, you can turn an ordinary Excel spreadsheet into something more useful and versatile.
Why Use Filters
For example, let’s say your boss owns a wine shop and 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.
Taking a proactive approach, you figure you add two columns to the Excel spreadsheet. The first task is to stock the shelves. The wine description field hints at varietal and bottle size. These values are important for stocking, especially 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.”
What is AutoFilter?
AutoFilter is an easy way to turn the values in an Excel column into filters based on the column’s cells. For example, by adding AutoFilter to the list above, I could filter the “Winery” column to only display rows from Beauregard. All the other wineries stay on the Excel worksheet but don’t display. Unlike Excel Slicers, which work on Excel Tables or Pivot Tables, Autofilter works everywhere.
To turn on autofilter,
- Click any cell within your range.
- From the Data tab, click Filter. It’s in the Sort & Filter panel.
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 click Filter from the Sort & Filter panel again.
In the example above, I can see all the values that show in the YEAR column. Various years are omitted since the values aren’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 my options are quite different and include an item for blank cells.
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.
Excel Custom AutoFilters
As handy as these filters are, there are times when you need to filter based on specific criteria within a cell. For example, we can use a custom filter to filter by wine types. This works because the distributor gives hints in the “Description” line.
To set a simple custom autofilter,
- Enable autofilter for your spreadsheet using the steps in the section above.
- Click the down control arrow in the column you wish to filter.
- From the drop-down list, select Text Filters. The wording may change based on the column contents.
- From the side menu, select Custom Filter…
- The Custom AutoFilter dialog should appear.
- In the first list box, click the drop-down arrow and select your criteria.
- In the list box to the right, either type a value or select one from the list.
- Click OK.
In the example below, I’ve elected to filter for rows containing “pinot noir” anywhere in the description. Once my filtered rows appear, I’ll add “Pinot Noir” in the Type column.
Excel is versatile in the filter settings. You’re not limited to just items containing a specific value. The choices change base on the the column’s data type. You could also use:
- 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
- does not contain
Refining Filters with Radio Buttons
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. Excel allows you to refine your filter with the use of the AND radio button and OR radio button.
Using our wine store example, I might want to filter wines that have Pinot in the description, but not Pinot Gris. The procedure is similar to the above, but I added another condition and used the “And” radio button. My second condition also uses the “does not contain” option.
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 rows. 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.
If you ever need to see your Excel autofilter settings, we did a separate tutorial on the subject.
You Might Also Like These Excel Tutorials
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-03-07