Excel Advanced Filter

So you’ve mastered the FILTER tool (aka, ‘Filter-in-place’). Are you ready to step up to ADVANCED FILTERS?

Although the Filter-in-place tool is easy to use, it does not handle multiple column INCLUSIVE criteria very well.

Using the ADVANCED FILTER you can:

• Copy filtered records to another part of the spreadsheet, leaving the original database undisturbed.
• Generate a BOOLEAN OR (aka, inclusive) condition across multiple fields.

What does that that last part mean? Let’s take the following scenario.

The last time Russia played host to the Winter Games was in 1994.  In preparing for the 2014 Olympics , you are tasked to create of separate list that includes:

• ALL RUSSIAN MEDAL WINNERS (any year), and
• 1994 GOLD MEDALISTS, in the WINTER OLYMPICS, of any nationality.

An Olympic feat (pun intended)? Not for ADVANCED FILTER!

(cue the Olympic music) 

Performing the ADVANCED FILTER requires a little preparation. You will need to prepare a CRITERIA RANGE to ask the question, and an OUTPUT RANGE to display the result. These ranges must be be in a separate area of the same worksheet  preferably to the right of the original.

Click here to download the 2014 Olympic Data file, then execute the instructions below.

Preparing the Criteria and Output Ranges

1. Select and copy the column headings in cells A1:G1.
2. Paste the headings, starting in cell J1 and again in cell J6.
3. In cell L2 type ‘RUS’
4. On the following row, in the appropriate cells, type ‘1994‘ ( J3), ‘Gold‘ (O3) and ‘Winter‘ (P3).

Performing the Advanced Filter

1. Place cursor on one of the cells from the original database (e.g. B4).
2. On the the Data tab of the Ribbon, in the Sort and Filter group, click Advanced. The Advanced Filter dialog will appear.
3. For Action select Copy to another location.
4. Review the List Range. If necessary correct the reference to include the source data.
5. In the Criteria Range field type or select cells J1:P3.
6. In the Copy To field, type or select the Output Range header, cells J6:P6.
7. Click OK.

Voilà! The records that satisfy these criteria are copied to the Output range, and you get a virtual laurel wreath!

Closing Ceremony Notes:

• Our criteria range included the headings and two additional rows. Each OR (inclusive) condition is entered on a separate row. Although our criteria uses data from 4 columns, there are only two separate, inclusive criteria; (1) RUSSIAN OR (2) GOLD MEDALIST in YEAR 1994. Were additional inclusive criteria necessary (e.g., Gender)  the criteria range in Step 5 would need to be increased, accordingly.
• The copied data is not linked to the original. Edits to the original source data do not affect the copied output. Should you edit the source data you need only re-run the Advance Filter to synchronize data.
• Do not put anything below the Output Range headers that you intend to keep; Excel erases all the rows beneath prior creating the output.