If you use the advanced filtering capabilities of Excel, you are not limited to filtering “in place.” You can also do the equivalent of a database extraction, which is a two-step process. First, the list is filtered, and then the records that match your criteria are copied to a different area of the worksheet.
To instruct Excel to copy the results of a filtering, follow these steps:
- Select the area you want to filter.
- Display the Data tab of the ribbon.
- Click the Advanced Filter tool, in the Sort & Filter group. Excel displays the Advanced Filter dialog box (See Figure 1.) with the address of your original data table already filled in, in the List Range box.
- Set your filtering options as desired.
- Make sure the Copy to Another Location radio button is selected.
- Specify a copy destination in the Copy To field.
- Click on OK.
Figure 1. The Advanced Filter dialog box.
When you specify a destination for the copy (step 6), you have three options. First, if you specify a single cell as the destination, then Excel copies the results of the filtering, regardless of the number of records extracted. If you are working with a large list and the results of the filtering might be many, many rows, however, you might not want to do this. In this case, make the destination a row selection. Excel will then only copy that many rows. Thus, if the result of the filtering was 47 records, and your destination was a selection of 12 rows, only the first 12 records are copied. The final option is to select a range of cells. This limits the copy to the number of rows and columns specified by the range.
