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

Get FREE Advanced Excel Exercises with Solutions!

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.


An Introduction to the Excel Advanced Filter

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

Advanced Filter to Copy a Data Set to Another Location in Excel

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:

Excel Advanced Filter to Copy Data Set to Another Location


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

We’ve read the basic introduction of the 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.

Inserting List Range to Use Advanced Filter to Copy a Data Set to Another Location in Excel

Read More: How to Create Dynamic Advanced Filter in Excel


⧭ 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.

Checking Check Box to Use Advanced Filter to Copy a Data Set to Another Location in Excel


⧭ 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.

Entering Criteria Range to Use Advanced Filter to Copy a Data Set to Another Location in Excel


⧭ 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.


⧭ 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.


⧭ 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.

Output to Use Advanced Filter to Copy a Data Set to Another Location in Excel


More Examples 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.

Data Set to Use Advanced Filter to Copy a Data Set to Another Location in Excel

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:

List Range to to Use Advanced Filter to Copy a Data Set to Another Location in Excel

And the Copy to range will be:

Copy To Range to to Use Advanced Filter to Copy a Data Set to Another Location in Excel

We have inserted these in the Advanced Filter.

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

Output to Use Advanced Filter to Copy Data Set to Another Location


Download Practice Workbook

Download this practice workbook to exercise while you are reading 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.


<< Go Back to Advanced Filter | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo