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.
How to Remove Duplicates from Drop Down List in Excel: 4 Ways
Let’s assume we have an Excel large worksheet that contains 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.
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
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 duplicate value from the drop-down list.
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.
- After that, a window named Microsoft Excel will pop up showing 39 duplicate values found and removed; 3 unique values remain.
- 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. 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
- 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.
Read More: [Fixed!] Drop Down List Ignore Blank Not Working in Excel
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
- 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.
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.
- Hence, you will be able to create a drop-down list corresponding with the Product name which does not have any duplicate values.
Read More: Hide or Unhide Columns Based on Drop Down List Selection in Excel
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 functions. Now, write down the following functions in cell E5.
=SORT(FILTER(UNIQUE(C5:C46),UNIQUE(C5:C46)<>0))- 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.
- 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
- 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.
Things to Remember
👉 The FILTER Function is only available in Excel 365.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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
- How to Create Drop Down List in Multiple Columns in Excel
- Create a Searchable Drop Down List in Excel
- How to Add Blank Option to Drop Down List in Excel
- Creating a Drop Down Filter to Extract Data Based on Selection in Excel
- How to Select from Drop Down and Pull Data from Different Sheet in Excel
- How to Create a Form with Drop Down List in Excel
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- How to Make Multiple Selection from Drop Down List in Excel
- How to Autocomplete Data Validation Drop Down List in Excel
<< Go Back to Edit Drop-Down List in Excel |Â Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
