Nazmus Sakib
Duplicate Values appear often in our workbook. If we want to make drop-down list based on a column that contains duplicates, all the values will appear on that list, including the duplicates. This article will demonstrate how to remove duplicates from drop-down list in Excel with 4 convenient step-by-step approaches.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Remove Duplicates from Drop-Down List in Excel with These 4 Easy Approaches
Today we will use the following sample dataset to learn how to remove duplicates from drop-down list in Excel. The sample dataset contains a list of car models sold and their manufacturers.
1. Applying Remove Duplicates Option
In our first approach, we will simply use Remove Duplicates option from Excel Ribbon to remove duplicates from drop-down list.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells C5:C16 and copy by pressing Ctrl+C together.
- Next, paste it into cell F5 by pressing Ctrl+V concurrently.
- Afterward, navigate to Data tab and click Remove Duplicates from Data Tools group.
- A dialog box may occur on the screen momentarily.
- Later, check My data has headers box and click OK.
- Afterward, a dialog box will show how many duplicates were found and how many unique values are present. Click OK.
- Consequently, we will see only 4 unique manufacturers remain on the list.
- Next, select cell G6 and click Data Validation from Data tab.
- A dialog box will pop up shortly.
- Later, select List from Allow drop-down menu.
- Afterward, select cells F6:F9 as the Source and click OK. Or you can manually write =$F$6# in the Source box.
- Voila! We have created drop-down list that only contains unique values.
Read More: 3 Ways to Create List from Range in Excel
2. Utilizing Pivot Table
Now, we will utilize one of the most widely used tool, Pivot Table in order to eliminate duplicates from created drop-down list.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cells B5:D16. Then navigate to Insert tab and click PivotTable.
- Momentarily, the dialog box of the PivotTable will open. Click OK.
- Next, from PivotTable Fields, drag the Manufacturer to Rows in the new worksheet.
- After that, you will notice only the unique values are present in the B column.
- Later, select cell D6 and move to Data tab to click Data Validation.
- From the dialog box, select List from Allow drop-down menu.
- Next, select cells B4:B7 as the Source. Click OK.
- Done! Eventually, we created a duplicate free drop-down list using Pivot Table. Click on drop-down icon to check the list.
Read More: How to Create Drop-Down List in Multiple Columns in Excel
3. Coalescing FILTER, UNIQUE and SORT Functions
In our next approach, we will use the combination of UNIQUE, SORT, and FILTER functions to create a duplicate free drop-down list.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell F6 and type in the following formula, and press Enter:
=SORT(FILTER(UNIQUE(C6:C16),UNIQUE(C6:C16)<>0))
- As a result, we have created a dynamic list and you will notice a thin blue border around the list. It means even if we add or remove manufacturers from our prepared dataset, this list will automatically be updated.
- Next, select cell G6 to establish drop-down list.
- Then, navigate to Data tab and click Data Validation.
- From Data Validation dialog box, select List from Allow drop-down box and select cells F6:F9 as the Source. Click OK.
- There you go! We created drop-down list without duplicates.
🔨 Formula Breakdown
SORT(FILTER(UNIQUE(C6:C16),UNIQUE(C6:C16)<>0))
👉 UNIQUE(C6:C16)<>0 returns an array of TRUE and FALSE. Based on this boolean array, FILTER function filtrates the unique values from cell C6:C16 with the help of UNIQUE function.
👉 SORT function sorts the array returned by FILTER function alphabetically.
4. Using Keyboard Shortcuts
Finally, we will apply keyboard shortcuts to open the Data Validation dialog box to create a unique drop-down list.
⬇️⬇️ STEPS ⬇️⬇️
- To start with, select cells C5:C16. Then, copy by pressing Ctrl+C concurrently.
- Later, paste the copied values in cell F5 by pressing Ctrl+V together.
- After that, move to Data tab and click Remove Duplicates from Data Tools group.
- A Remove Duplicates dialog box may occur on the screen immediately.
- Next, check My data has headers box and click OK.
- Afterward, a warning box will show how many duplicates were found and how many unique values are present. Click OK.
- Now, press Alt and you can see the keyboard shortcuts appear on the Excel Ribbon. Then press A to navigate to Data tab.
- After that, press V twice to open the dialog box. So, in short, you have to press Alt+A+V+V sequentially.
- Next, click on List from Allow drop-down menu and write =$F$6# in Source box. Now, click OK.
- Thus, we get drop-down list of unique manufacturers.
📝 Takeaways from This Article
📌 Firstly, we learned how to remove duplicates from drop-down list using the Remove Duplicates option from Excel Ribbon.
📌 Secondly, we demonstrated how to utilize a Pivot Table to remove duplicates from drop-down list.
📌 Next, we merged SORT, FILTER, and UNIQUE functions to remove duplicates from drop-down list.
📌 Lastly, we removed duplicates and used keyboard shortcuts to open Data Validation dialog box to establish drop-down list.
Conclusion
That concludes the discussion for today. These are some convenient methods to remove duplicates from drop-down list in Excel. If you want to provide any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
