See all How-To Articles

How to Use Custom AutoFilter in Excel & Google Sheets

This tutorial demonstrates how to use a custom AutoFilter in Excel and Google Sheets.

 

custom autofilter text 4

 

Custom Text AutoFilter

Using a custom text AutoFilter, you can filter a list of data by a specific condition you set (for example, if a cell contains, begins with, or ends with a text or similar). Say you have the following set of data with Product in Column B, and Price in Column C.

 

custom autofilter initial data

 

Now you want to filter only rows that contain the word phone in Column B.

  1. First, turn on the filter. Click on any cell in the data range (B1:C12) and in the Ribbon, go to Home > Sort & Filter > Filter.

 

custom autofilter text 1

 

  1. Click on the filter button next to Product (cell B1), go to Text Filters, and choose Custom Filter…

 

custom autofilter text 2

 

  1. In the Custom AutoFilter window, choose contains from the drop-down list, enter the text you want to filter (phone), and click OK.

 

custom autofilter text 3

 

As a result, only Rows 10 and 11 are displayed: Microphone and Mobile phone in Column B, while all other rows are hidden.

 

custom autofilter text 4

 

Custom Number AutoFilter

Similar to the previous example, you can also create a custom number AutoFilter. For that purpose, you can use the same data range. In this example, let’s filter only products with prices less than $100.

  1. Click on the filter button next to Price (cell C1), go to Number Filters, and choose Custom Filter…

 

custom autofilter number 1

 

  1. In the Custom AutoFilter window, choose is less than from the drop-down, enter the condition (100), and click OK.

 

custom autofilter number 2

 

As a result, Rows 2, 4, 5, 10, and 12 are displayed, because these products have a price of less than $100.

 

custom autofilter number 3

 

Note: You can learn more about filtering rows with specific text.

Custom Filters in Google Sheets

 

custom autofilter new 7

 

In Google Sheets, you can use the standard filter options to filter data, and also create a formula for filtering data. Like Excel, Google Sheets has more options to filter by text:

  • text does not contain,
  • text starts/ends with,
  • text is exactly, and
  • custom formula.

Say you have the following set of data with Product in Column A and Prices in Column B.

 

custom autofilter new 8

 

To filter only rows that contain the word Wireless follow these steps:

  1. First, turn on the filter. Select the data range (A1:B6) and in the Toolbar, click on the Filter button.

 

custom autofilter new 2

 

  1. Click on the filter button next to Product (cell A1). In the drop-down menu click on Filter by Condition and choose Text contains.

 

custom autofilter new 3

 

  1. In the box enter the text you want to filter (Wireless) and click OK.

 

custom autofilter new 5

 

As a result, Rows 3 and 6 are displayed: Wireless Keyboard and Wireless Mouse in Column A, while all other rows are hidden.

 

custom autofilter new 6