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

While working with a large Microsoft Excel dataset, sometimes we need to create a drop-down list and remove the duplicates from the drop-down list. This is an easy task. This will save you a lot of time and energy. Today, in this article, we’ll learn four quick and suitable ways to drop down a list and remove duplicates in Excel effectively with appropriate illustrations.


Download Practice Workbook

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


4 Suitable Ways to Remove Duplicates from Drop Down List in Excel

Let’s assume we have an Excel large worksheet that contains the information about various fruits and vegetables that a country has imported to three different countries in Europe. We have the Product Name, Product Category, Exported Amount, and Importer Country. We will create a drop-down list and remove the duplicates from that drop-down list by using keyboard shortcuts, Data Validation Command, Pivot Table and combine the SORT, FILTER, and UNIQUE functions in Excel. Here’s an overview of the dataset for today’s task.

excel drop down list remove duplicates


1. Apply the Keyboard Shortcuts to Remove Duplicates from Drop Down List in Excel

Applying the keyboard shortcuts is an easy task to remove duplicates from the drop-down list in Excel. We will create a drop-down list from our dataset by using keyboard shortcuts, and then remove duplicates from that drop-down list. Let’s follow the steps below to learn!

Step 1:

  • First of all, press the below keyboard buttons one after one,

Alt + A + V + V

Apply the Keyboard Shortcuts to Remove Duplicates from Drop Down List in Excel

Step 2:

  • After pressing those keyboard buttons, a Data Validation dialog box will appear in front of you. From the Data Validation dialog box, firstly, select the Settings Secondly, select the List from the Allow Box. Thirdly, select type =$E$5:$E$46 in the Source box. At last, press OK.

  • Hence, you will be able to create a drop-down list corresponding with the country Our drop-down list contains the duplicates value. We want to remove the duplicates value from the drop-down list.

Apply the Keyboard Shortcuts to Remove Duplicates from Drop Down List in Excel

Step 3:

  • To remove duplicates, from your Data tab, go to,

Data → Data Tools → Remove Duplicates

  • Further, a Remove Duplicates dialog box pops up. From the Remove Duplicates dialog box, firstly, select the Unselect All Secondly, check the Country option. At last, press OK.

Apply the Keyboard Shortcuts to Remove Duplicates from Drop Down List in Excel

  • After that, a window named Microsoft Excel will pop up showing 39 duplicate values found and removed; 3 unique values remain.

Apply the Keyboard Shortcuts to Remove Duplicates from Drop Down List in Excel

  • After completing the above process, you will be able to remove duplicates from the drop-down list that has been given in the below screenshot.

Read More: How to Remove Used Items from Drop Down List in Excel (2 Methods)


2. Use the Data Validation Command to Remove Duplicates from Drop Down List in Excel

After learning the keyboard shortcuts to remove duplicates from the drop-down list, now we will learn how to use the Data Validation Command to remove duplicates from the drop-down list. This will save you a lot of time and energy. Let’s follow the instructions below to learn!

Steps:

  • First, from your Data tab, go to,

Data → Data Tools → Data Validation → Data Validation

Use the Data Validation Command to Remove Duplicates from Drop Down List in Excel

  • After creating a Data Validation dialog box, simply repeat step 2 and step 3 that has been done in method 1. After that, you will get the output like our screenshot that has been given in below.

Use the Data Validation Command to Remove Duplicates from Drop Down List in Excel

Related Content: How to Select from Drop Down and Pull Data from Different Sheet in Excel


Similar Readings:


3. Create a Pivot Table to Remove Duplicates from Drop Down List in Excel

In this method, first, we will create a pivot table, then, make a drop-down list to remove duplicate values. We can do that easily from our dataset. Please follow the steps below to learn!

Step 1:

  • First of all, from your Insert ribbon, go to,

Insert → Tables → PivotTable → From Table/Range

Create a Pivot Table to Remove Duplicates from Drop Down List in Excel

  • After clicking on the From Table/Range option, a PivotTable from table or range dialog box will appear in front of you. From that dialog box, firstly, from our dataset select cells ’Pivot Table’$B$4:$B$46 in the Table/Range box, secondly, check the Existing Worksheet At last, press OK.

  • Hence, you will be able to create a Pivot Table. From our creating pivot table, check only the product option that has been given in the below screenshot. The Pivot Table also removes the duplicate values.

Create a Pivot Table to Remove Duplicates from Drop Down List in Excel

Step 2:

  • Further, add a heading named Drop Down List in cell H4 in our data table, and then, from your Data tab, go to,

Data → Data Tools → Data Validation → Data Validation

  • After pressing on the Data Validation option, a Data Validation dialog box will appear in front of you. From the Data Validation dialog box, firstly, select the Settings Secondly, select the List from the Allow Box. Thirdly, select type =$G$5:$G$18 in the Source box. At last, press OK.

Create a Pivot Table to Remove Duplicates from Drop Down List in Excel

  • Hence, you will be able to create a drop-down list corresponding with the Product name which does not have any duplicate values.

Create a Pivot Table to Remove Duplicates from Drop Down List in Excel

Read More: Create Excel Drop Down List from Table (5 Examples)


4. Combine the SORT, FILTER, and UNIQUE Functions to Remove Duplicates from Drop Down List in Excel

Now, we will merge the SORT, FILTER, and UNIQUE functions to remove duplicates from the drop-down list. This is an easy task. From our dataset, we will filter the unique Category of products by using those functions. Let’s follow the instructions below to learn!

Steps:

  • First, we’ll remove the duplicate values from column C which contains the Category of the products. To do that, we can use the SORT, FILTER, and UNIQUE Now, write down the following functions in cell E5.
=SORT(FILTER(UNIQUE(C5:C46),UNIQUE(C5:C46)<>0))
Formula Breakdown:
  • The UNIQUE function finds out the unique value from cells C5 to C46.
  • The FILTER function filters the value from cells C5 to C46 to get a unique value.
  • The SORT function will sort the data by Category.

Combine the SORT, FILTER, and UNIQUE Functions to Remove Duplicates from Drop Down List in Excel

  • After typing the functions in cell E5, simply press ENTER on your keyboard, and you will get the unique value of the Category of the products.

  • Now, add a heading named Drop Down List of Category in cell F4 in our data table, and then, from your Data tab, go to,

Data → Data Tools → Data Validation → Data Validation

Combine the SORT, FILTER, and UNIQUE Functions to Remove Duplicates from Drop Down List in Excel

  • After pressing on the Data Validation option, a Data Validation dialog box will appear in front of you. From the Data Validation dialog box, firstly, select the Settings Secondly, select the List from the Allow Box. Thirdly, select type =$E$5:$E$6 in the Source box. At last, press OK.

  • Finally, you will be able to create a drop-down list by removing the duplicate values.

Combine the SORT, FILTER, and UNIQUE Functions to Remove Duplicates from Drop Down List in Excel

Read More: Conditional Drop Down List in Excel (Create, Sort and Use)


Things to Remember

👉 The FILTER Function is only available in Excel 365.


Conclusion

I hope all of the suitable methods mentioned above to drop-down list remove duplicates will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo