Excel list: Filtering a list using an OR function


You want to filter a table with customer orders with several criteria that are linked by OR functions. Excel 2010 offers the Advanced Filter for these kinds of tasks.

This learning module “ Excel list”will show you how to apply the advanced filter to filter a table using the OR function.


Excel list: When applying the advanced filter using disjunctions, it is also necessary to create a criteria table outside the table you want to filter. Define the column headings in the top row of this table that you want to filter by, e.g. “Customer” and “ORDER DATE”. Enter the criteria you want to filter for in the rows below. Ensure that the criteria are entered in different rows so that Excel recognizes the OR function.

Then apply the advanced filter by clicking the  button in the Sort & Filter group on the Data tab.


When applying the advanced filter, it is important that the active cell is inside the table you want to filter.

The Advanced Filter dialog box automatically shows the table you want to filter (here A1:J201) and asks you to enter the criteria range. You also define other options in this section:

Excel allows you to display the filtered data either in the range of the original table or to copy it to a range outside the original table and save it.

Define the range to be filtered here. Excel usually recognizes the range automatically. It requires, however, that the active cell be in the range of the original table when applying the advanced filter. If not, click in the range to be filtered or enter it manually.

To define the criteria range click in the Criteria range: field first and then in the criteria table, which is outside the original table.

This field only becomes active when you have selected the Copy to another location option above. Define the range of cells to which you want to copy the resulting table with the Copy to: function.

Select this option to hide any duplicates.

Confirm your settings in the dialog box by clicking  . The advanced filter is applied immediately.

To remove the advanced filter click the  button in the Sort & Filter group on the Data tab.


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