You can copy data to another location using the traditional Copy and Paste features in Excel. Alternatively, you can also copy data to a location through Excel Advanced Filter feature. However, if you want to copy filtered data to another location directly by Advanced Filter feature, you need to create a criteria range to filter data before copying and pasting.
Read More: Copy and Paste to Skip the Duplicate Values in Excel
Criteria Range in Advanced Filter Feature
The criteria range in Excel is a range of cells that contains the criteria or conditions used to filter data using Advanced Filter. It should be a separate range of cells in the worksheet that contains the column headers and the corresponding values that you want to use for filtering the data.
In this example, you can create a separate criteria range that lists the criteria you want to use for filtering the data, such as “Product = P3” or “Sales > $1000“. The criteria range should include the column headers and the criteria for each column.
The Advanced Filter feature in Excel uses the criteria range to determine which rows of data should be included in the filtered output. Only the rows that meet all of the criteria in the criteria range will be copied to the selected location.
Copy Filtered Data to Another Location by Advanced Filter Feature with Criteria Range
You can use the Advanced Filter feature in Excel to copy filtered data from the source location to another location based on specific criteria.
Here are the steps to do it:
1. Create a range criteria to filter data, for example, cells E2:E3 with criteria header “SALES” in E2 and criteria “> 1000” in E3.
2. Select the data range that you want to copy. Here is the source range B2:C8.
3. Go to the “Data” tab in the ribbon and click on “Advanced” in the “Sort & Filter” group.
4. In the “Advanced Filter” dialog box:
a. Check on “Copy to another location” option.
b. In the “List Range” field, enter the entire range before filtering where you want to copy from. You can either type the range manually or select it by clicking on the worksheet. In this example, the list range is B2:C8.
c. In the “Criteria Range” field, enter the range that contains the criteria you want to use for filtering the data. The criteria range should include the column headers. In this example the criteria range is E2:E3.
d. In the “Copy To” field, enter the range where you want to copy the data in the destination location. You can either type the range manually or select it by clicking on the worksheet. In this example, the copy to range is B10:C16.
e. Click on the “OK” button to apply the filter and copy the data to the destination location.
After clicking OK, the data that meets the criteria you specified will be copied to the destination location properly, while the rest of the data will be filtered out.
