To remove duplicate choices from an Excel dropdown, clean the list that feeds it: select that source list, then choose Data > Remove Duplicates and confirm the column. In Microsoft 365, you can instead generate a live, duplicate-free list with UNIQUE, then use that result as the dropdown source.
What “remove duplicates” means for a dropdown
An Excel dropdown is usually a Data Validation list. The choices may be typed directly into the validation settings, or they may come from cells in the workbook. If the same choice appears more than once in those source cells, the dropdown can show it more than once too. Removing duplicates means cleaning the source or creating a cleaned list; it does not mean deleting repeated entries from the cells where people have already used the dropdown.
For example, suppose a source list contains Apple, Pear, Apple, and Banana. A dropdown based on all four cells can offer Apple twice. Cleaning the source leaves one instance of each choice. The dropdown’s existing validation rule generally continues to point at its source range, so cleaning that range updates the choices without requiring you to rebuild the dropdown.
First identify where the choices come from. Select a cell with the dropdown and open Data > Data Validation. On the Settings tab, check that Allow is set to List and inspect the Source box. A cell reference indicates a worksheet range; comma-separated text indicates that the options were entered directly. The distinction matters because Remove Duplicates works on worksheet cells, not on the text inside the validation Source box.
Remove duplicates from a source range
Use this approach when the list is stored in cells and you want to remove repeated values from that list itself. On Excel for Microsoft 365 for Windows:
- Find the range that supplies the dropdown. Make a copy first if the source contains information you may need to restore.
- Select the cells in the list. If the data has a header, include it and select My data has headers in the dialog. Otherwise, select only the values.
- Choose Data > Remove Duplicates. In the dialog, select the column that contains the dropdown choices. If you selected just that one column, it will be the only column listed.
- Choose OK. Excel keeps one instance of each value and reports how many duplicate values were removed and how many unique values remain.
- Open a cell with the dropdown and check the available choices. Also confirm that the validation Source still covers the cells containing the cleaned list.
Excel keeps the first occurrence it finds and removes later matching entries. If the source is a multi-column table, select all related columns and choose the column or columns that define a duplicate record. Selecting only the names column could remove rows while leaving related details behind or pair records incorrectly. For a simple dropdown list, cleaning a copied, single-column list is often safer than applying the command to a larger table.
On Excel for Mac, the command is also available from the Data tab as Remove Duplicates; dialog wording and ribbon layout can vary slightly by release. The underlying operation is similar. If you remove the wrong values, Undo is Ctrl+Z on Windows or Command+Z on Mac, provided you have not performed another action that changes the undo history.
Build a live unique list with UNIQUE
If you want the dropdown options to update when the source data changes, a formula can create a separate, deduplicated list. This avoids deleting repeated entries from the original data. UNIQUE and SORT are available in Microsoft 365 and Excel 2021 or later. FILTER is also available in those versions and can exclude blank cells from the output.
With source choices in A2:A8, enter this formula in D2 on the same worksheet or on a helper sheet:
=SORT(UNIQUE(FILTER(A2:A8,LEN(A2:A8)>0)))
For example, if A2:A8 contains Apple, Pear, Apple, Banana, and three blank cells, the formula spills three sorted results in D2:D4: Apple, Banana, and Pear. The original source remains unchanged. Leave the cells beneath D2 clear so Excel can display the full spilled result; if something blocks the output, Excel shows a spill error instead.
This approach is useful when new records are regularly added, but the formula’s source range must include those future entries. A fixed reference to A2:A8 will not automatically include a new value entered in A9. You can use a suitably sized source range or organize incoming data so the formula refers to an expanding source. Check the actual range before relying on the dropdown, especially if a workbook is maintained by several people.
Use the unique results as the dropdown source
Once the formula produces a clean list, point Data Validation at it. If the formula is in D2 on the same worksheet as the dropdown, select the dropdown cell or cells and choose Data > Data Validation. Set Allow to List, then enter =$D$2# in Source. The # refers to the complete spilled range beginning at D2, so the validation list can follow the changing number of results. Confirm that In-cell dropdown is selected if you want the arrow to appear.
If the helper formula is on another worksheet, Excel’s list validation may not accept a direct reference to that sheet in the Source box. Create a workbook-level name instead. With the helper formula in D2 on a sheet named Catalog, open Formulas > Name Manager, choose New, name the range DropdownOptions, and set Refers to to =Catalog!$D$2#. Then set the validation Source to =DropdownOptions. The name points to the spilled results, and the dropdown can use that name from another sheet. If the sheet name contains spaces, enclose it in apostrophes in the reference.
Keep the helper list somewhere users will not accidentally overwrite it, but do not hide it so thoroughly that workbook maintainers cannot find it. A short note beside the formula can explain that it supplies the dropdown. If the workbook is shared, tell other editors not to type over the spill range.
Choose between deleting duplicates and a formula
Use Remove Duplicates when you want a one-time cleanup and do not need to preserve repeated entries in the source. It is direct, works in older Excel versions, and leaves the dropdown connected to the cleaned range. Its trade-off is that it changes the underlying data; it does not keep the list current automatically when more repeated values are added later.
Use UNIQUE when you want to preserve raw entries or expect the source to change. The formula creates a separate view of the distinct choices and recalculates as its referenced cells change. The trade-offs are version compatibility and setup: dynamic-array functions require a supported Excel version, the spill area must remain clear, and the validation rule must point to the formula’s full result.
- Choose Remove Duplicates for a quick, permanent cleanup of a simple list.
- Choose UNIQUE for a reusable list that should respond to edits in its source.
- Keep the original data intact when repeated rows have meaning, such as transaction records or survey responses.
- Use a separate helper list if other columns or formulas depend on the original source.
Common problems and how to fix them
The dropdown still shows repeats. Check the validation Source again. It may refer to a different range, or it may point to a larger range that includes old values below the cleaned list. Update the source to cover only the intended options. If you used a formula, confirm that the validation rule points to the spill range or the workbook-level name rather than just the first formula cell.
Values look identical but are treated differently. Leading or trailing spaces can make entries appear the same while their cell contents differ. For example, “North” and “North ” are not the same text. Extra spaces may come from pasted data or imports. Inspect and clean the source values before deduplicating; otherwise, the same apparent choice may survive in more than one form. Differences in punctuation or spelling, such as “E-mail” and “Email,” are separate choices too.
The Remove Duplicates dialog gives an unexpected result. If Excel thinks the first row is a header, or treats a header as a value, the My data has headers setting may be wrong. Review the selection and dialog options before confirming. In a table with several columns, duplicate detection depends on which columns are selected: selecting every column removes only rows that match across all those columns, while selecting just one column treats repeated values in that column as duplicates.
The UNIQUE formula returns an error or the dropdown is empty. UNIQUE and the spill operator require a compatible Excel version. In older versions, use Remove Duplicates on a copy of the source list or another compatible workflow. In a supported version, check for an obstructed spill area, an invalid source reference, or a formula that finds no usable values. Also verify that Data Validation is set to List and that the Source points to the correct cells or name.
New choices do not appear. A formula cannot include values outside the range it references. If the source list grows beyond its stated range, adjust the formula’s range or set up a source that expands with the data. With a manually cleaned list, new entries must be added to the validation source range as well as the list itself.
Protect the original list and existing entries
Before changing a source range, consider whether duplicates are actually mistakes. In a customer table, repeated customer names may represent separate orders and should not be removed from the records. Create a separate one-column list for the dropdown instead. A copy of the workbook or source range gives you a way back if you select the wrong columns or choose the wrong header setting.
Cleaning the options does not automatically rewrite values already selected in other cells. If a cell already contains a repeated or retired option, removing that item from the source list does not necessarily clear the cell. Review existing entries separately if the allowed choices have changed. Data Validation can help prevent future invalid selections, but it is not a substitute for checking data already in the workbook.
Frequently Asked Questions
Can I remove duplicates directly from a dropdown?
Not from the open dropdown menu itself. Find its Data Validation source and remove duplicates from the source cells, or create a separate unique list with UNIQUE and point the validation rule to that list.
Does Remove Duplicates delete the first matching value?
No. Excel keeps the first occurrence in the selected data and removes later duplicates. Make a backup if the original order or repeated rows may matter.
Will UNIQUE update my Excel dropdown automatically?
It can, if the formula’s source range includes the changes and the validation rule refers to the complete spilled result. A fixed source range will not include new data entered beyond its boundaries automatically.
Why does my dropdown show a blank option?
The source range may contain blank cells, or the formula may include blanks in its output. Use a helper formula that filters out empty cells, and check that the validation source does not include extra blank rows.
Can I keep duplicates in my data but hide them in the dropdown?
Yes. Leave the original records untouched and create a separate unique helper list with UNIQUE. Use that helper list, rather than the raw data, as the validation source.
Related
- Combine Two Cells Into One in Excel
- Consolidate Lines in Excel: Methods and Examples
- Consolidate Excel Workbooks Into One: Best Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create a Reusable Task Template in OutlookTo create a reusable task template, use classic Outlook for Windows: make a task with the fields you…
- Block a Sender in Outlook Mail: Steps, Limits and TipsTo block a sender in Outlook, select a message from that person, open the message actions or Junk…
- Lower Excel Calculation Time: How to Speed Up WorkbooksTo lower Excel calculation time, first identify which formulas or workbook features are causing delays, then reduce unnecessary…
- How to Sum a Row in Google Sheets: Simple FormulaTo sum a row in Google Sheets, put the total in an empty cell and enter =SUM(B2:F2). This…
- How to Get a Color Code in ExcelTo get a cell’s fill or font color as a HEX code in Excel for Microsoft 365 on…
- How to Double Space in Word: Quick Step-by-Step GuideTo double-space text in Microsoft Word, select the text, then choose Home > Line and Paragraph Spacing >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.