How to Use Custom Filters in Excel 2013

By Greg Harvey from Excel 2013 For Dummies

In addition to filtering a data list to records that contain a particular field entry in Excel 2013, you can create custom AutoFilters that enable you to filter the list to records that meet less-exacting criteria (such as last names starting with the letter M) or ranges of values (such as salaries between $25,000 and $75,000 a year).

To create a custom filter for a field, you click the field’s AutoFilter button and then highlight Text Filters, Number Filters, or Date Filters (depending on the type of field) on the drop-down list and then click the Custom Filter option at the bottom of the continuation list. When you select the Custom Filter option, Excel displays a Custom AutoFilter dialog box.

You can also open the Custom AutoFilter dialog box by clicking the initial operator (Equals, Does Not Equal, Greater Than, and so on) on the field’s Text Filters, Number Filters, or Date Filters submenus.

In this dialog box, you select the operator that you want to use in the first drop-down list box. Then enter the value (text or numbers) that should be met, exceeded, fallen below, or not found in the records of the database in the text box to the right.

Operator	Example	What It Locates in the Database
Equals	Salary equals 35000	Records where the value in the Salary field is equal to $35,000
Does not equal	State does not equal NY	Records where the entry in the State field is not NY (New York)
Is greater than	Zip is greater than 42500	Records where the number in the Zip field comes after 42500
Is greater than or equal to	Zip is greater than or equal to 42500	Records where the number in the Zip field is equal to 42500 or comes after it
Is less than	Salary is less than 25000	Records where the value in the Salary field is less than $25,000 a year
Is less than or equal to	Salary is less than or equal to 25000	Records where the value in the Salary field is equal to $25,000 or less than $25,000
Begins with	Begins with d	Records with specified fields have entries that start with the letter d
Does not begin with	Does not begin with d	Records with specified fields have entries that do not start with the letter d
Ends with	Ends with ey	Records whose specified fields have entries that end with the letters ey
Does not end with	Does not end with ey	Records with specified fields have entries that do not end with the letters ey
Contains	Contains Harvey	Records with specified fields have entries that contain the name Harvey
Does not contain	Does not contain Harvey	Records with specified fields have entries that don’t contain the name Harvey

If you want to filter records in which only a particular field entry matches, exceeds, falls below, or simply is not the same as the one you enter in the text box, you then click OK or press Enter to apply this filter to the database. You can use the Custom AutoFilter dialog box to filter the database to records with field entries that fall within a range of values.

To set up a range of values, you select the “is greater than” or “is greater than or equal to” operator for the top operator and then enter or select the lowest (or first) value in the range. Then, make sure that the And option is selected, select “is less than” or “is less than or equal to” as the bottom operator, and enter the highest value in the range.

You can see how to filter the records in the Employee Data List so that only those records where Salary amounts are between $25,000 and $75,000 are displayed. You set up this range of values as the filter by selecting “is greater than or equal to” as the operator and 25,000 as the lower value of the range.

Then, with the And option selected, you select “is less than or equal to” as the operator and 75,000 as the upper value of the range. You can see the results of applying this filter to the Employee Data List.

To set up an either/or condition in the Custom AutoFilter dialog box, you normally choose between the “equals” and “does not equal” operators and then enter or select the first value that must be met or must not be equaled. Then you select the Or option and select whichever operator is appropriate and enter or select the second value that must be met or must not be equaled.

For example, if you want to filter the data list so that only records for the Accounting or Human Resources departments in the Employee Data List appear, you select “equals” as the first operator and then select or enter Accounting as the first entry.

Next, you click the Or option, select “equals” as the second operator, and then select or enter Human Resources as the second entry. When you then filter the database by clicking OK or pressing Enter, Excel displays only those records with either Accounting or Human Resources as the entry in the Dept field.