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 for copying the filter dropdown list under various conditions.
So, let’s dive into the main article.

Download Workbook


5 Ways to Copy Filter Drop-Down List in Excel

Here, we have the following dataset containing 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 enabled Filter for this dataset and the following methods will demonstrate the ways to copy the filter dropdown list.

how to copy filter drop down list in Excel

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.

how to copy filter drop down list in Excel

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.

Advanced Filter

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

how to copy filter drop down list in Excel

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.

Advanced Filter

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

how to copy filter drop down list in Excel

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

Advanced Filter

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.

Advanced Filter

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

how to copy filter drop down list in Excel

Read More: Create Excel Filter Using Drop-Down List Based on Cell Value


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.

how to copy filter drop down list in Excel

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

UNIQUE function

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

UNIQUE function

Then, a table Table2 will be created.

how to copy filter drop down list in Excel

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

UNIQUE function

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

UNIQUE function

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

Read More: Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)


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.

how to copy filter drop down list in Excel

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.

Remove Duplicates option

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

Remove Duplicates option

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.

how to copy filter drop down list in Excel

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

Remove Duplicates option

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

Remove Duplicates option

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.

how to copy filter drop down list in Excel

Read More: How to Remove Duplicates from Drop Down List in Excel (4 Methods)


Similar Readings:


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.

how to copy filter drop down list in Excel

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

how to copy filter drop down list in Excel

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.

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.

how to copy filter drop down list in Excel

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

FILTER function

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

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

FILTER function

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

FILTER function

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.

how to copy filter drop down list in Excel

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

FILTER function

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

FILTER function

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.

how to copy filter drop down list in Excel

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.

FILTER function

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.

FILTER function

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.

FILTER function

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

FILTER function

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

FILTER function

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

how to copy filter drop down list in Excel

Read More: VBA to Select Value from Drop Down List in Excel (2 Methods)


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 function, INDEX function, MATCH function we will always get that list in the Filtered List column.

how to copy filter drop down list in Excel

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

how to copy filter drop down list in Excel

➤ Press ENTER and drag down the Fill Handle tool.

SUBTOTAL, INDEX and MATCH functions

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

SUBTOTAL, INDEX and MATCH functions

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.

how to copy filter drop down list in Excel

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.

SUBTOTAL, INDEX and MATCH functions

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

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

SUBTOTAL, INDEX and MATCH functions

➤ 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 which 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

SUBTOTAL, INDEX and MATCH functions

➤ Press ENTER and drag down the Fill Handle tool.

SUBTOTAL, INDEX and MATCH functions

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

how to copy filter drop down list in Excel

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.

SUBTOTAL, INDEX and MATCH functions

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.

SUBTOTAL, INDEX and MATCH functions

Related Content: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)


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.

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo