MrExcel Publishing
Your One Stop for Excel Tips & Solutions

advanced filter

Posted by Carla Miller on November 17, 2001 3:51 PM

I am having a terrible time understanding what advanced filter is.
I have an assignment due and i have to use adv. filters. The directions start out as:
I will be seeting up this particular worksheet to do adv filters. Establish an output area of following fields:
topic, collection and circ.
All five filters will keept the same output area...
Please help me, and if possible use an example.

Posted by Katt on November 17, 2001 4:59 PM

Posted by Katt on November 17, 2001 5:05 PM

Sorry about the previous post.
Anyway. All an advanced filter does is pull specific records from a large data sheet and either show them to you, or copy them to another place, which I find tremendously useful.
Imagine you have a large spreadsheet with customer names and addresses, and you only want to look at people living in say Tarrant Co., TX.
Your list range will be all of the records and your criteria range will be all of the column headings from the list but underneath them, put the criteria that you want.
If you have a list with :
Name Address County State

You will want your criteria range to look like:
Name Address County State
Tarrant TX
The advanced filter will only return the records with Tarrant and TX in those columns.

Posted by Richard S on November 18, 2001 5:49 PM

In addition, you can get advanced filter to only extract the fields you want. In Katts example, if you only put Name in say cell Z1, and highlight that as your output range, it will only extract the names of people who live in that county. This is one of the things that makes it advanced, as autofilter shows all fields.