Excel filter: YOUR OBJECTIVE
You recorded your customers’ orders in an Excel worksheet. Now you want to display the data rows with orders for article numbers that begin with the letters HD. However, you don’t want to display any orders with the article number HDG1. To do this, you have to apply an Autofilter that uses the AND operator to combine two filter criteria.
In this learning module “ Excel filter ”, you will be shown how to employ the AutoFilter to filter a table by applying to a column two filter criteria connected by the AND operator.
Excel filter: How to filter a column using two criteria To use the AND operator to associate two filter criteria in the same column, you must first activate the AutoFilter. To do so, click the button in the Sort & Filter group, on the Data tab.
Next click the filter arrow in the appropriate column.
Depending on the nature of a column’s contents, the entries in the selection list may vary and show, for example, Text Filters, Date Filters or Numeric Filters.
The following screenshot shows the filter functions for the Article Number column, a text column in the example table. For date or number columns, other possibilities would be displayed. In order to filter a column using two filter criteria connected by an AND operator, click the Custom Filter… option.
Since you want to filter using the first two letters of an article number, select begins with in the dialog box’s first comparison field and, as shown, enter HD in the associated input box. Now, for the second criterion, select does not equal and enter HD1G in its input box.
If you do that, the Custom AutoFilter dialog box will already have this option selected when it opens.
After clicking OK, only orders with an article number that begins with HD but is not HD1G will be displayed.