How to Apply the Advanced Filter to Copy to Another Location in Excel

Advanced Filter is one of the most useful and advantageous features of Excel that’s used pretty often to copy one range of cells from one location to another, with or without applying a filter. In this article, I’ll show you can use the Excel Advanced Filter to copy a range of cells to another location through step-by-step analysis.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

An Introduction to the Excel Advanced Filter

An Advanced Filter is a tool available in the Data tab in Excel under the section called Sort & Filter.

It can apply a total of 3 operations on any data set in Excel.

• It can copy a data set from one location to another.
• It can filter the data set by applying a criterion.
• Also, it can remove the duplicate values from the data set and keep only the unique values.

To perform all these operations successfully, you have to insert a total of 5 parameters into the filter.

• First of all, you have to input the data set (List Range) into the filter.
• Next, you have to choose one option between keeping the data set in its original location or copying it to a new destination.
• If you want to copy it to another location, you have to specify the location.
• Then you have to insert a criteria range to filter the data set (If you want).
• Finally, you have to check or uncheck a checkbox to decide whether you want to keep the unique values or all the values.

Therefore, a complete Advanced Filter Userform allows you to input all these 5 parameters. It looks like this:

How to Use Advanced Filter to Copy to Another Location in Excel (Step-by-Step Analysis)

We’ve read the basic introduction of the Excel Advanced Filter. Now we’ll learn how we use the Advanced Filter to copy a data set to another location through step-by-step analysis.

Here we’ve got a data set with the book names, authors, book types, and prices of some books of a bookshop called Martin Bookstore.

Here we’ll use the Advanced Filter to copy the novels of Charles Dickens and the science fiction of H. G. Wells.

⧭ Step 1: Inserting the List Range (Data Set) That’ll be Copied to Another Location:

The first step to using the Advanced Filter is to insert the data set (list range) that you want to copy. You can first select the data set and then open the Advanced Filter, or open the Advanced Filter first and then enter the list range there.

Here our list range is the data set B3:E13 (Including the Headers).

We moved to the button Data > Advanced Filter in the Excel toolbar to open the Advanced Filter and then inserted the list range B3:E13 there.

Read More: Advanced Filter with Criteria Range in Excel (18 Applications)

⧭ Step 2: Checking the Copy to Another Location Checkbox

Then we’ve to check the Copy to another location checkbox if you want to copy the data set to another location.

Read More: Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)

⧭ Step 3: Applying the Criteria Range (If Any) to Filter the List Range (Data Set)

The Criteria Range is a range of cells that is applied to filter a data set. It’s a range consisting of the Headers and the specific values of the data set that you want to copy.

For example, to copy the novels of Charles Dickens and the science functions of H. G. Wells, the Criteria Range will be:

Here, the Criteria Range is C16:D18. We have inserted it in the Advanced Filter.

Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)

⧭ Step 4: Entering the Destination Range to Copy the List Range to Another Location

Next, we have to enter the destination range in the Copy to text box. It’s a single row containing the Headers of the column that we want to copy.

We want to copy the Book Names, Authors, and Book Types of the novels of Charles Dickens and the science fiction of H. G. Wells.

So, our Copy to range will look like this:

Its range is H3:J3. We’ve entered it in the Advanced Filter.

Read More: How to Use Advanced Filter to Copy Data to Another Sheet in Excel

⧭ Step 5: Deciding Whether to Keep Only the Unique Values or Not

Finally, we don’t want to keep only the unique records. So. we have kept the Unique records only box unchecked.

Read More: How to Use Advanced Filter for Unique Records Only in Excel

⧭ Step 6: The Final Output! Copying to Another Location with Advanced Filter

After inserting all the inputs carefully, click OK. You’ll get your filtered data copied to the destination location.

Read More: Dynamic Advanced Filter Excel (VBA & Macro)

More Example to Copy Data Set with Advanced Filter in Excel

Here we’ve another data set with the names of some students and their grades in Physics, Chemistry, and Mathematics in the exam.

We want to copy the names of the students who got both A or both B in Physics and Chemistry.

So, our Criteria Range will be:

And the Copy to range will be:

We have inserted these in the Advanced Filter.

Then clicked OK. It copied our filtered range to the destination.

Things to Remember

We’ve learned to use the Advanced Filter manually in this article. To know how to use the Advanced Filter with VBA, read this article.

Conclusion

So, these are the ways to use the Advanced Filter to copy a data set to another location in a worksheet in Excel. Do you have any problems? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)
• How to Use Advanced Filter If Criteria Range Contains Text in Excel
• Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]
• How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
• Excel VBA Examples: Use Advanced Filter with Criteria (6 Criteria)