How to do advanced filter

Dear Holta

Thank you for attending Excel Intermediate course. I hope you enjoyed the course and have already started applying the newly learnt features at you work.

Advanced filter is used when the Auto filters (the small arrows that appear next to the main column headings) cannot get you the desired results.

The most important thing to keep in mind when performing Advanced Filters is the Criteria range. The best place for the criteria range is at the top of the sheet. So if you don’t have any space at the top of the sheet then please insert few blank rows.

Step 1: For the criteria range Copy and paste the column headings from the main table which will contain the criteria. This is better than typing the names of the headings manually because if there is any single spelling mistake and both the headings don’t match the result will be incorrect.

Step 2: Type the criteria in the criteria range. E.g. in the training we used the following criteria:

Dept. Earnings ($)
Accounts >100000
Sales >80000

Step 3: Click on any cell inside the main table.

Step 4: Choose Data > Filter > Advanced Filter… (The entire data will be selected automatically.)

Step 5: Click on the criteria range and select the cells where you put the criteria including the headings.

Step 6: If you wish to filter the data and get the result in the same place where the existing data is then leave the option to In Place otherwise choose Copy to another location option and then click on the copy to box and select the cell where you want the results to start from.

Step 7: Press OK

I hope this has answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007