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.