How to Copy Filter Drop-Down List in Excel (5 Ways)

If you are trying to copy the filter drop-down list in Excel effectively, then this article is for you. Here, we will discuss different 5 ways to copy the filter dropdown list under various conditions.
So, let’s dive into the main article.

How to Copy Filter Drop-Down List in Excel: 5 Ways

Here, the following dataset contains the list of salespersons’ names and sales values according to the products. We will try to filter it under various criteria so that we have added Filter for this dataset and the following methods will demonstrate the ways to copy the filter dropdown list.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Using Advanced Filter Option to Copy Filter Drop-Down List in Excel

Here, we have the products listed in the Product column, but the products are not sorted properly and some duplicate products like Blackberries, Broccoli remain there.

So, when we click on the filter dropdown symbol we are getting the list sorted from A to Z and moreover, there is no duplicate value. Our task is here to copy this dropdown list to the Filtered List column and we will do it here using the Advanced Filter option.

Steps:
➤ Go to the Data Tab >> Sort & Filter Group >> Advanced Option.

Then, the Advanced Filter wizard will open up.
➤ Check the options Copy to another location and Unique records only.
➤ Select the products as a List range and the destination range where you want to have the outputs in the Copy to box and finally press OK.

As you can see, we have got the list with unique products in the Filtered List column but it has not been sorted yet.

➤ To do the sorting procedure, select the dataset and go to the Data Tab >> Sort & Filter Group >> Sort Option.

Then, the Sort wizard will pop up.
➤ Select the followings
Sort by → Filtered List
Sort On → Cell Values
Order → A to Z
➤ Click on the My data has headers option and press OK.

Then, the Filtered List will be sorted and we will get the filter dropdown list copied in the Filtered List column.

Method-2: Using UNIQUE Function to Copy Filter Drop-Down List in Excel

Here, we can see the filter dropdown list of the Product column and we will copy the list to the Filtered List column by using the UNIQUE function and the SORT function.

Steps:
Firstly, we will convert the range into a table.
➤ Go to the Insert Tab >> Table Option.

Then, the Create Table wizard will appear.
➤ Select the range and click on the My table has headers option, and finally, press OK.

Then, a table Table2 will be created.

➤ Now, write the following function in cell E4 to get the unique values from the Product column.

=SORT(UNIQUE(Table2[Product],FALSE,FALSE))

Here, Table2[Product] is the range of the Product column of Table2, first FALSE is for Return unique rows and the second one is for Return every distinct item. Then UNIQUE will give us the list with unique products and then it will be sorted out by the SORT function.

After pressing ENTER, we will get the filter dropdown list of the Product column in the Filtered List column.

The UNIQUE function is only available for Microsoft Excel 365 version.

Method-3: Using Remove Duplicates Option to Copy Filter Drop Down List

To copy the filter dropdown list of the Product column in the Filtered List column, here we will be using the Remove Duplicates option to remove the duplicate values from the products, and then, the Sort option will arrange the list the same as the dropdown list of the Product column.

Steps:
We have to copy the Product list from the Product column to the Filtered List column.
➤ Select the range of the Product column and press CTRL+C.

➤ Press CTRL+V to paste the list in the Filtered List column.

Now, it is time to get the unique values by removing the duplicates.
➤ Select the data range and then go to the Data Tab >> Data Tools Group >> Remove Duplicates Option.

After that, the Remove Duplicates dialog box will appear.
➤ Check the Filtered List option and press OK.

Then you will get a message box saying that it has removed 2 duplicate values and press OK here.

After sorting the texts from A to Z like Method-1 we will get the filter dropdown list of the Product column in the Filtered List column.

Method-4: Using FILTER Function to Copy Filter Drop Down List

Suppose, we have filtered the following dataset on the basis of the Product column and here we want to show up only the corresponding values for the products Blackberries and Broccoli.

After filtering down we have the following salespersons’ names in order from A to Z in the filter dropdown list of the Salesperson column.

And, the following sales values from lowest to highest in the filter dropdown list of the Sales column. Our task is to copy these two lists using the FILTER function.

4.1: Using the FILTER Function

➤ Use the following formula in cell B14

=FILTER(B7:D11,B7:B11=B7," ")

Here, B7:D11 is the range, then FILTER will search for the value Blackberries of cell B7 in the range B7:B11=B7, and for empty cells, it will return a blank.

After pressing ENTER, we will get the salespersons’ names and the sale values for the product Blackberries.

Similarly, for extracting the values for the product Broccoli use the following formula in cell B16.

=FILTER(B7:D11,B7:B11=B8," ")

➤ Press ENTER and you will get the salespersons’ names in the Filtered List1 column and the sales values in the Filtered List2 column.

The FILTER function is only available for Microsoft Excel 365 version.

4.2: Copying the Values and Sorting them

Now, we will sort them like the list in the filter dropdown as we showed earlier but here it can not be done as this is the array formula.
So, we have to copy the lists by pressing CTRL+C.

➤ Then, select the cell where you want to paste them and right-click here, and select the option Paste Values.

In this way, we will get the values of the Filtered List1 and Filtered List2 in the following dataset.

The final task is to sort the salespersons’ names from A to Z and the sales values from lowest to highest values.
➤ Select the range of the Filtered List1 column and go to the Data Tab >> Sort & Filter Group >> Sort Option.

After that, the Sort dialog box will pop up.
➤ Select the followings
Sort by → Filtered List1
Sort On → Cell Values
Order → A to Z
➤ Click on the My data has headers option and press OK.

The values of the Filtered List1 will be sorted now and now we will work with the sales values of the Filtered List2 column.
➤ Select the range of the Filtered List2 column and go to the Data Tab >> Sort & Filter Group >> Sort Option.

Later, the Sort dialog box will appear.
➤ Select the followings
Sort by → Filtered List2
Sort On → Cell Values
Order → Smallest to Largest
➤ Click on the My data has headers option and press OK.

As we can see, we have now sorted the values of the Filtered List1 and Filtered List2 columns as we wanted.

Finally, we have copied the filter dropdown list of the Salesperson column to the Filtered List1 column,

and, the filter dropdown list of the Sales column in the Filtered List2 column.

Method-5: Combination of SUBTOTAL, INDEX and MATCH Functions

Here, we will filter the dataset based on some products of the Product column, and in this way, the filter drop-down list of the Salesperson column will be updated also and using the SUBTOTAL, INDEX, MATCH functions we will always get that list in the Filtered List column.

5.1: Getting the Updated Serial Numbers

Firstly, we will get the serial numbers in the Helper column which will be automatically updated after filtering.
➤ Apply the following formula in cell D4

=SUBTOTAL(3,C$4:C4)

Here, 3 is for the COUNTA function, C$4:C4 is the range that will be updated for each successive row like for Row 8 it will be C$4:C8 because we have fixed the first limit by putting a $ sign before the Row number 4.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, we will get the serial numbers in the Helper column.

Now, we will filter the table on the basis of the Product column and so we have checked the products Apple, Beet Greens, Blackberries, and Cherry from the dropdown list of this column.

Then, we will get the following filtered table and now we will copy the filter dropdown list of the SalesPerson column to the Filtered List column.

5.2: Using the INDEX and MATCH Functions to Extract the List

➤ Write down the serial numbers in the Serial No column.

➤ Type the following formula in cell D14.

=INDEX($C$4:$C$11,MATCH(C14,$D$4:$D$11,0))

Here, $C$4:$C$11 is the range of the SalesPerson column that we want to get, C14 is the serial number that will be matched with the numbers in the Helper column.

MATCH(C14,$D$4:$D$11,0) → returns the row index number of the value in cell C14 which is 1.
Output → 1

INDEX($C$4:$C$11,MATCH(C14,$D$4:$D$11,0)) becomes
INDEX($C$4:$C$11,1) → checks the corresponding value in the range $C$4:$C$11 for row index number 1
Output → Michael

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will get the salespersons’ names in the Filtered List column, and the final task is to sort them from A to Z.
➤ Select the dataset and go to the Data Tab >> Sort & Filter Group >> Sort Option.

Afterward, the Sort dialog box will open up.
➤ Select the followings
Sort by → Filtered List
Sort On → Cell Values
Order → A to Z
➤ Click on the My data has headers option and press OK.

Eventually, the list will be sorted and we will get the copy of the filter drop-down list of the SalesPerson column in the Filtered List column.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Practice Workbook

Copying Filter Dropdown List.xlsx

Conclusion

In this article, we tried to cover the ways to copy a filter drop-down list in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

<< Go Back to Excel Drop Down List Filter | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!