1.

Start Excel and open the file named Practice 9 from your Excel 2003 Practice folder.

You will see a data list with name, age, height, and weight fields.

You will construct an advanced filter to retrieve the records where the name is Steve, or the age is greater than 50 and the height is less than 69. The filtered records will be copied to a new range.

2.

 First, you must set up your criteria range. You want to show the records where the name is Steve, or the age is greater than 50 and the height is less than 69. To accomplish this, click on cell F1 and enter Name as a heading. Next click on cell F2 and enter the name Steve. This will create a name condition to select records with the name Steve.

In cell G1 type the heading Age and in cell G3 type >50. Using cell G3 puts the age condition one row lower than the name condition. This means that there will be a logical or joining the two conditions. Finally, type Height in cell H1 and enter <69 in cell H3 on the same row as the age condition. This means that the age and height criteria will be joined with a logical and.

Your worksheet should now look like this. The next step is to apply the advanced filter.

3.

Click on a cell inside the data list, and choose Data->filter->Advanced filter from the menu bar. Your data list should be outlined with a flashing blue and yellow border and the advanced filter dialogue box will appear.

Click the copy to another location radio button, and make sure that the List range is correct ($A$1:$D$27). Press the Tab key to move the curser to the Criteria range entry box, and then select the range containing your criteria (F1:F3) with your mouse.  

To select a Copy to range, press Tab or click the mouse to move your curser to the Copy to entry box.

 Click cell J1 which will be the upper left cell in the destination range for the filtered records. 

Your advanced filter is now prepared, when you are ready click OK.

4.

You should now see the results of your filter. Only the records with age grater than 50 and height less than 69, or records with the name Steve will be shown.

5.

You can determine what fields are displayed in your filtered records by preparing your extract range (the place you will copy the records to) in advance.

Click cell F8 and enter Name. Click cell G8 and enter Weight. Click on a cell in your data list and choose Data->Filter->Advanced Filter to invoke the advanced filter dialogue box.

Click the Copy to another location radio button. The list range and the criteria range should already be what you want, but check to make sure. Use the Tab key to move your curser to the Copy to data entry box, and select cells F8:G8 (the extract range headings that you just prepared plus 3 additional rows for the records). When you are ready click OK.

6.

You will notice that only the fields that you specified in your extract range are visible. Before, when you did not prepare a specific extract range with headings, all of the fields in the filtered records were copied.

Save your work and exit Excel.