Advanced Filter Command

The old Advanced Filter command is still present in Excel 2007. Microsoft should give this feature a new name. It is remarkably powerful and does much more than filtering.

It is admittedly one of the more confusing commands in Excel, particularly because there are eight different ways you can use it, and each method requires slightly different steps.

You can use the Advanced Filter command to filter records in place like the filter command, or you can use it to copy matching records to a new location. If you choose to copy records to a new location, you can copy all the input columns in order, or you can specify a subset of columns and/or a new sequence of columns.

You can ask Excel to only give you a unique list of items in the output range. You can build a simple filter for one column. You can combine any number of filters for multiple columns. You can build incredibly complex filters, using any formula imaginable. Or you can use no criteria at all. Using no criteria is common when you are using Advanced Filter to extract unique values or when you want to use Advanced Filter to reorder the sequence of columns.

To use Advanced Filter on a dataset, follow these steps:

1. If you are using criteria, copy one or more headings from your dataset to a blank section of the worksheet. Under each heading, list the value(s) that you want to be included.
2. If you are using an output range and want to reorder the columns or include a subset of the columns, copy the headings into the appropriate order in a blank section of the worksheet. If you want all the original columns in their original sequence, the output range can be any blank cell.
3. Select a cell in your data range.
4. Choose Data, Sort & Filter, Advanced.
5. If Excel nags you with the Large Operation dialog, click OK.
6. Verify that the list range contains your original dataset.
7. If you are using criteria, enter the criteria range.
8. If you want to copy the matching records to a new location, choose Copy to Another Location. This enables the reference box for Copy to. Fill in the output range.
9. If you want the output range to contain only unique values, click Unique Records Only. If your output range contained a single field, you get a list of the values in that field which match the criteria. If your output range contains two or more fields, you get every unique combination of those two or more fields.
10. Click OK to perform the filter.