How to Create a Custom AutoFilter in Excel 2019

By Greg Harvey

You can click Excel’s Custom Filter option on a field’s Text Filters, Date Filters, or Number Filters continuation menu to open the Custom AutoFilter dialog box, where you can specify your own filtering criteria by using conditions with the AND OR logical operators (called AND OR conditions for short). When you click the Custom Filter option, Excel 2019 opens the Custom AutoFilter dialog box.

Custom AutoFilter Excel 2019
Using Custom AutoFilter to filter out records except for those within a range of salaries.

 

Here, you select the type of operator to use in evaluating the first and second conditions in the top and bottom drop-down list boxes and the values to be evaluated in the first and second conditions in the associated combo boxes. You also specify the type of relationship between the two conditions with the And or option button. (The And option button is selected by default.)

When selecting the operator for the first and second condition in the leftmost drop-down list boxes at the top and bottom of the Custom AutoFilter dialog box, you have the following choices, depending on the types of entries in the selected field:

  • Equals: Matches records where the entry in the field is identical to the text, date, or number you enter in the associated combo box.
  • Does Not Equal: Matches records where the entry in the field is anything other than the text, date, or number you enter in the associated combo box.
  • Is After: Matches records where the entry in the date field comes after the date you enter or select in the associated combo box.
  • Is After or Equal To: Matches records where the entry in the date field comes after or is the same as the date you enter or select in the associated combo box.
  • Is Before: Matches records where the entry in the date field precedes the date you enter or select in the associated combo box.
  • Is Before or Equal To: Matches records where the entry in the date field precedes or is the same as the date you enter or select in the associated combo box.
  • Is Greater Than: Matches records where the entry in the field follows the text in the alphabet, comes after the date, or is larger than the number you enter in the associated combo box.
  • Is Greater Than or Equal To: Matches records where the entry in the field follows the text in the alphabet or is identical, the date comes after or is identical, or the number is larger than or equal to the one you enter in the associated combo box.
  • Is Less Than: Matches records where the entry in the field comes before the text in the alphabet, comes before the date, or is less than the number you enter in the associated combo box.
  • Is Less Than or Equal To: Matches records where the entry in the field comes before the text in the alphabet or is identical, the date comes before or is identical, or the number is less than or equal to the one you enter in the associated combo box.
  • Begins With: Matches records where the entry in the field starts with the text, the part of the date, or the number you enter in the associated combo box.
  • Does Not Begin With: Matches records where the entry in the field starts with anything other than the text, the part of the date, or the number you enter in the associated combo box.
  • Ends With: Matches records where the entry in the field ends with the text, the part of the date, or the number you enter in the associated combo box.
  • Does Not End With: Matches records where the entry in the field ends with anything other than the text, the part of the date, or the number you enter in the associated combo box.
  • Contains: Matches records where the entry in the field contains the text, the part of the date, or the number you enter in the associated combo box.
  • Does Not Contain: Matches records where the entry in the field contains anything other than the text, the part of the date, or the number you enter in the associated combo box.

Note that you can use the Begins With, Ends With, and Contains operators and their negative counterparts when filtering a text field — you can also use the question mark (?) and asterisk (*) wildcard characters when entering the values for use with these operators. (The question mark wildcard stands for individual characters, and the asterisk stands for one or more characters.) You use the other logical operators when dealing with numeric and date fields.

When specifying the values to evaluate in the associated combo boxes on the right side of the Custom AutoFilter dialog box, you can type in the text, number, or date, or you can select an existing field entry by clicking the box’s drop-down list button and then clicking the entry on the drop-down menu. In date fields, you can select the dates directly from the date drop-down palette opened by clicking the box’s Date Picker button (the one with the calendar icon).

The image above illustrates setting up filtering criteria in the Custom AutoFilter dialog box that selects records whose Salary values fall within two separate ranges of values. This example uses an OR condition to filter out all records where the salaries fall below $35,000 or are greater than $75,000 by entering the following complex condition:

Salary Is Greater Than 75000 OR Is Less Than 35000