Excel filter: Filtering a column using two criteria (AND operator)


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. 


To specify that both criteria must be met before a data row can be displayed, connect them by activating the operator. Finish up by clicking  .


To save a couple of mouse clicks, you can also select the Begins With… option in the Text Filters menu. 

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.


Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231