It may be the case that you want to extract your filtered records to a new place in the worksheet, or even to a new worksheet altogether. Copying your filtered records to a new location leaves the view of your original data list unchanged.

To copy your filtered records to a new location, set up a criteria range as before, with duplicate column headings, and the conditions you want to specify. In addition, you can prepare a range for the filtered records to be copied to. To do this, you can choose some empty columns, and label them with the headings for the fields you want to display. You do not have to display an entire record, but just the fields of your choice.

Make sure the column headings for the range you are copying to, are exactly the same as the column headings in the original unfiltered list. If you do not put column headings in the copy to range, all of the fields specified in the list range will be copied. If you are copying entire records, you can leave the range you are copying to blank.

Click a cell in the data list, choose Data->Filter->Advanced Filter, and select the Copy to another location radio button in the advanced filter dialogue box.

Enter the list range and the criteria range as required, and then click in the area labelled Copy to, (you can also press the tab key to move to other options within a dialogue box).

Select the range that you that you have prepared for the copied records with your mouse, or type it in directly. If you don’t know how large a range to select, select the column letters above the fields you have chosen in the destination range.

If you are copying all fields, i.e. complete records, just click on a cell in the upper left of the range you are copying to. Click OK to copy the filtered records to the destination range.

The image that follows shows the fields Name and Age filtered from the main list, and copied to columns I and J, according to the condition that height is greater than 67.Because the column headings Name and Age have been specified in the destination range, only the name and age of people with a height greater than 67 will be shown.

You can also specify unique records only, by clicking the checkbox in the advanced filter dialogue box. Leave the criteria range blank, enter an appropriate destination range, and press OK.