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

Let’s assume you have a large Excel worksheet containing information about various fruits and vegetables imported by a country into three different European countries. The dataset includes columns for Product Name, Product Category, Exported Amount, and Importer Country. Our goal is to create a drop-down list and remove any duplicate values from it. We’ll explore four methods to achieve this:

excel drop down list remove duplicates


Method 1 – Keyboard Shortcuts:

  • Create a drop-down list from your dataset using the following keyboard shortcuts:
    • Press Alt + A + V + V sequentially.
    • This opens the Data Validation dialog box.

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

  • In the Data Validation dialog box:
    • Select Settings.
    • Choose List from the Allow dropdown.
    • Enter the range of your data (e.g., =$E$5:$E$46) in the Source box.
    • Click OK.

  • You now have a drop-down list corresponding to the countries, but it may contain duplicate values.

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

  • To remove duplicates:
    • Go to the Data tab.
    • Navigate to Data ToolsRemove Duplicates.

  • In the Remove Duplicates dialog box:
    • Click Unselect All.
    • Check the Country option.
    • Click OK.

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

  • A window will appear, indicating that 39 duplicate values were found and removed, leaving 3 unique values.

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

Read More: How to Remove Used Items from Drop Down List in Excel


Method 2 – Data Validation Command:

  • From the Data tab, go to Data ToolsData ValidationData Validation.

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

  • Create a Data Validation dialog box by repeating steps 2 and 3 from Method 1.
  • The resulting drop-down list will now be free of duplicates.

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

Read More: [Fixed!] Drop Down List Ignore Blank Not Working in Excel


Method 3 – Create a Pivot Table:

  • Go to the Insert ribbon and select Tables → PivotTable → From Table/Range.

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

  • In the PivotTable from table or range dialog box:
    • Choose the range of cells for your dataset (e.g., ‘Pivot Table’!$B$4:$B$46).
    • Check the Existing Worksheet option.
    • Click OK.

  • This creates a Pivot Table. Select only the Product option as shown in the screenshot. The Pivot Table automatically removes duplicate values.

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

  • Add a heading named Drop Down List in cell H4 of your data table.
  • Go to the Data tab and select Data Tools → Data Validation → Data Validation.

  • In the Data Validation dialog box:
    • Choose Settings.
    • Select List from the Allow dropdown.
    • Enter the range =$G$5:$G$18 in the Source box.
    • Click OK.

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

  • You now have a drop-down list corresponding to the Product Name without any duplicate values.

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

Read More: Hide or Unhide Columns Based on Drop Down List Selection in Excel


Method 4 – Combine the SORT, FILTER, and UNIQUE Functions:

  • Remove duplicate values from column C (which contains the product categories) using the following formula 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

  • Press ENTER to get the unique product category values.

  • Add a heading named Drop Down List of Category in cell F4 of your data table.
  • Go to the Data tab and select Data Tools → Data Validation → Data Validation.

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

  • In the Data Validation dialog box:
    • Choose Settings.
    • Select List from the Allow dropdown.
    • Enter the range =$E$5:$E$6 in the Source box.
    • Click OK.

  • You’ve successfully created a drop-down list by removing duplicate values.

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


Things to Remember

The FILTER function is available only in Excel 365.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo