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

Dataset Overview

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:

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.

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.

To remove duplicates:
- Go to the Data tab.
- Navigate to Data Tools → Remove Duplicates.

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

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

Method 2 – Data Validation Command

From the Data tab, go to Data Tools → Data Validation → Data Validation.

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.

Method 3 – Create a Pivot Table

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

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.

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.

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

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.

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.

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.