Create an Advanced Filter in Excel

Let’s consider below data to showcase how to use the advanced filter in excel.

Name	School	Place	Class	Section
Aishani	DPS	Durgapur	2	1
Sudipto	DPS	Durgapur	3	2
Sarala	Carmel	Kolkata	4	4
Esha	Carmel	Kolkata	5	1
Puku	Xaviers	Hyderabad	6	5
Logan	Scotish Church	Chennai	6	3
Monty	JU	Mumbai	7	2

1. Create the criteria in another tab, with the exact column names, and their values that you want to filter. In my case, here is the filter:

Name	School	Place	Class	Section
	DPS

This will select all rows where School is DPS

2. Create another tab to get the filtered data. Let’s call that tab as ‘Output’

3. Click on any cell in the ‘Output’ tab

4. Click on Data > Advanced Filter

5. Click on the Up Arrow button of the ‘List range’

6. Select the complete Source where you have the original data

7. Select the complete Criteria table in the ‘Criteria range’

8. Click the radio button ‘Copy to another location’

9. Click on the Up Arrow button in the ‘Copy to’

10. Select any cell in the ‘Output’ tab

That’s it. Now, you got the filtered data in the ‘Output’ tab.

Name	School	Place	Class	Section
Aishani	DPS	Durgapur	2	1
Sudipto	DPS	Durgapur	3	2

Below are the possible filters you can use as the ‘Criteria’

1. Select all rows with School=DPS and Place=Durgapur

Name	School	Place	Class	Section
	DPS	Durgapur

2. Select all rows with School=(DPS OR JU) AND Place=Durgapur

Name	School	Place	Class	Section
	DPS	Durgapur
	JU

3. Select all rows where Name contains Aish

Name	School	Place	Class	Section
*Aish*

4. Select all rows where either Name contains ‘Aish’ or School is ‘JU’

Name	School	Place	Class	Section
*Aish*
	JU