How to remove duplicates from a drop down list

How To Remove Duplicates From A Drop Down List

Why do duplicates appear in a dropdown list?

Duplicates appear in a dropdown list when the source range contains repeated values. Data validation lists display every entry in the source range without filtering. A source column with multiple identical records creates redundant options in the dropdown menu.

How to remove duplicates using the Remove Duplicates feature

To remove duplicates from a dropdown list source using the Remove Duplicates feature, copy your data to a separate location first to preserve the original dataset.

Copying data to preserve the original

1. Select the source column containing your dropdown list values.
2. Press Ctrl+C to copy.
3. Navigate to a dedicated sheet (e.g., "Lists" or "Reference").
4. Select the destination cell and press Ctrl+V to paste.

Applying Remove Duplicates

1. Select the pasted data range on the Lists sheet.
2. Navigate to Data > Remove Duplicates.
3. Confirm the column selection in the dialog box.
4. Check "My data has headers" if the first row contains a header.
5. Click OK.

Excel displays a message indicating how many duplicate values were removed and how many unique values remain. The selected range now contains distinct values only.

Updating the dropdown source

1. Select the cell or range containing your dropdown list.
2. Navigate to Data > Data Validation.
3. Select "List" from the Allow dropdown.
4. Update the Source field to reference the deduplicated range on the Lists sheet (e.g., =Lists!$A$2:$A$15).
5. Click OK.

The dropdown list now displays unique values without duplicates.

How to remove duplicates using the UNIQUE function

To remove duplicates from a dropdown list source using the UNIQUE function in Excel 365/2021, create a dynamic formula that extracts distinct values automatically.

Setting up the UNIQUE formula

Enter this formula in a helper cell (e.g., A2) on a dedicated "Lists" sheet:

=UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>""))

Replace "SourceTable[ColumnName]" with your actual Table and column reference or a standard range like Sheet1!B$2: B$1000. The formula spills automatically, displaying all unique values starting from the formula cell.

Handling blanks and errors

The FILTER function nested within UNIQUE excludes blank cells using the criteria <>"". Add an IFERROR wrapper for source data containing errors:

=IFERROR(UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>"")),"")

Sorting the unique values

Wrap the formula with SORT for alphabetical or numerical order:

=SORT(UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>"")))

Referencing the spill range in data validation

1. Select the cell or range where you want the dropdown list.
2. Navigate to Data > Data Validation.
3. Select "List" from the Allow dropdown.
4. Enter the spill reference in the Source field: =Lists!$A$2#
5. Click OK.

The hash symbol (#) after the cell reference indicates the entire spill range. The dropdown updates dynamically as source data changes.

Creating a dynamic named range

1. Navigate to Formulas > Name Manager > New.
2. Enter a name such as "UniqueList" in the Name field.
3. Enter the spill reference in the Refers to field: =Lists!$A$2#
4. Click OK.

Use the named range in Data Validation by entering =UniqueList in the Source field.

What are the differences between Remove Duplicates and UNIQUE function?