Advanced filter in Excel depends on the user defined criteria that can applied to many columns of data. Advanced Filter is used to perform complex filtering which is not possible using the basic auto filter function.

To access Advanced Filter, click on DATA tab in Excel ribbon and click on Advanced in sort and filter section as shown in the image below.

In Advanced Filter we need to specify two criteria which are List Range and Criteria Range. List Range is the range of cells which we want to filter and it also includes the headers of those specified columns. Criteria Range is the range where the filtering criteria are mentioned. This criteria range should specify the headers that are being used for filtering as mentioned in the list range.

Advanced Filter in Excel (filter in-place)

Let’s take an example, we have data of students with their respective marks in Maths, Physics and History. We are interested in filtering the students list who secured more than 65% in all 3 subjects or more than 85% in Maths. To do this we create a criteria range as shown in the image below.

Here the range D2:G3 is criteria range. The first row in the criteria range indicates that we are interested in filtering the data where the students achieved >=65 marks in all the three subjects. The second row in the criteria indicates that where we interested in getting the students who achieved >=85 marks in Maths irrespective of other subjects. Microsoft Excel does an OR of the criteria mentioned in the two rows. It returns the records that either satisfy the criteria in the 1^st row or the criteria in the 2^nd row

Now we have the data/list range and the criteria. Let’s see how we can apply advanced filter in excel now.

Step 1: Range Selection

Select the List Range D5:G14 and click on Advanced Filter in Data tab

Step 2: Filter the list

Now, we get a pop up window to enter the List Range and Criteria Range. If you are interested in filtering the data in the same location, select filter the list, in place. If you are interested in copying the data to another location then select the copy to another location. Enter the List range and Criteria Range including the headers and then click OK. If interested in extracting unique values then select that option as well.

Now the advanced filter got applied and the result is as shown in the image below.

Based on first criteria ‘Bill’, ‘Thomas’ and ‘William’ got filtered and based on second criteria ‘Michel’, ‘Daniel’ and ‘James’ got filtered.

Advanced Filter (copy to another sheet):

There is one drawback with advanced filter in Excel. It can be applied and copied to another location but in the same sheet. We can trick excel and copy the filtered data to another sheet. Let’s take the same example with the same criteria but interested in filtering the data to another sheet.

Step 1: Output Sheet

Go to the desired sheet (output sheet) and click on the cell (For example ‘D1’) and click Advanced Filtering

Step 2: Copying to Output Sheet

Select copy to another location and enter ‘list range‘ which is in Student Data sheet (‘Student Data’!$D$5:$G$14),enter ‘criteria range’ which is in Student Data sheet (‘Student Data’!$D$1:$G$3) and enter the (Output Sheet’!$D$1) in ‘copy to’ field and then click OK.

Now we get the filtered data in another sheet as shown in image below.

This is how we use advanced filter for filtering the data or copying the filtered data to another sheet based on the given criteria. Still have queries? Feel free to contact our Excel Expert here.