Excel Data Validation List: Remove Duplicates

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, create a unique list from the original entries with =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))), then use that spill range as the source for Data Validation. The formula removes repeated entries and blanks from the dropdown options; it does not delete or change the original data.

Why duplicates appear in a dropdown

A list validation rule displays the values in its source range. If the source contains the same choice more than once, Excel generally presents that choice more than once too. For example, a source containing “North,” “South,” “North” can produce a dropdown with two North entries. Data Validation does not automatically clean its source or decide which repeated values should be retained.

Duplicates often arrive through ordinary data entry, pasted records, or lists assembled from multiple worksheets. Some apparent duplicates are not exact matches: “North” and “North ” have different text because the second includes a trailing space. Differences in spelling, punctuation, or extra spaces can therefore keep values separate even when they look similar at a glance.

Removing repeats is useful when the dropdown is meant to show available choices rather than record frequency. Keep the original list intact if repeated records carry meaning—for example, a transaction log where each repeated product represents a separate sale. A cleaned helper list is usually safer than editing the underlying records.

Create a unique list with UNIQUE

For a dynamic solution, put the original choices in A2:A100 on a worksheet and use a spare cell, such as H2, for the helper list. This formula assumes the source entries are in A2:A100; it removes empty cells, returns one instance of each distinct value, and sorts the result alphabetically:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

In Microsoft 365, the result spills down from H2. When source values change, the helper list recalculates. Add new entries within the referenced range if you want them included; values entered below A100 are outside this particular formula. To make the source expandable, consider converting the source range to an Excel Table and using a structured reference in the formula. Dynamic array functions including UNIQUE, SORT, and FILTER are available in Microsoft 365 and Excel 2021 or later.

To check the result, enter a few repeated choices in A2:A100 and look at the list beginning at H2. Each distinct nonblank value should appear once. If the source has no nonblank values, FILTER may return a calculation error because there is nothing to return. You can either ensure there is at least one choice or handle the empty-source case separately, depending on how the workbook should behave.

Connect the unique list to Data Validation

A spill range grows and shrinks, so it is more flexible than selecting a fixed number of helper cells. Excel’s Data Validation Source box may not accept a spill reference entered directly in every context. A workbook-level defined name is a reliable way to connect the dropdown to the spill range.

  1. On the Formulas tab, choose Name Manager, then select New.
  2. Enter a clear name such as UniqueOptions. Do not include spaces in the name.
  3. If the helper formula is in H2 on a worksheet named Lists, set Refers to to =Lists!$H$2#. This reference assumes the spill formula is in Lists!H2. If the sheet name contains spaces, enclose it in single quotes, as in ='Lookup Data'!$H$2#.
  4. Select OK and close Name Manager.
  5. Select the cell or cells that need the dropdown. Choose Data > Data Validation. In the Settings tab, choose List under Allow, and enter =UniqueOptions in Source. Make sure In-cell dropdown is selected, then choose OK.

The # spill operator means “the entire spilled result beginning at this cell.” The defined name follows the current size of that result, so the validation list can update when the source changes. To apply the same dropdown to a column, select the intended input cells before opening Data Validation. On Windows, Ctrl-click can add separate cells to a selection; on Mac, use Command-click. Check the selection carefully before applying a rule, especially if it includes cells with existing values.

Remove duplicates without a formula

If you need a one-time cleanup or use an Excel version without UNIQUE, you can make a separate list with Advanced Filter. On Windows, copy the source to a spare area, choose Data > Advanced in the Sort & Filter group, select Copy to another location, identify the copied range, and check Unique records only. Use the resulting range as the validation source. Advanced Filter is also useful when you want a static snapshot rather than a list that changes automatically.

Another option is the Remove Duplicates command, but use it only on a copy if the repeated source records must be preserved. Select the copied list and choose Data > Remove Duplicates; confirm the relevant column, then accept the result. Excel keeps one occurrence and removes additional matching rows from the selected data. This command changes cells, unlike a validation rule, and it is not a live connection: adding another duplicate later does not automatically clean the list again.

For a small, stable list, manually typing each option once into a separate source range may be simplest. Avoid typing comma-separated choices directly into the Source box for lists that change often; that approach is difficult to maintain and has a character limit. A worksheet range is easier to review and update.

Understand what Excel counts as a duplicate

UNIQUE is designed to return distinct values, but the result depends on the actual cell contents. Leading or trailing spaces, nonbreaking spaces copied from a website, inconsistent punctuation, and text stored differently can make entries appear equivalent to a person while remaining different to Excel. If “Blue” and “Blue ” both appear, inspect the source cells and clean the source data rather than assuming the dropdown is at fault.

Text comparison in UNIQUE is not a case-sensitive way to distinguish choices: entries that differ only by capitalization should not be relied on as separate dropdown options. If capitalization carries meaning in your data, test the intended behavior in a small sample before building the final list. Also distinguish repeated text from repeated numbers or dates: values that look alike may be stored as different types or formats, such as a text date and a true Excel date.

Sorting is optional. To preserve the order in which values first occur, use a unique-list formula without SORT. With entries in A2:A100, this formula assumes that same source layout and filters out blanks while retaining the first-seen order:

=UNIQUE(FILTER(A2:A100,A2:A100<>""))

Choose alphabetical order when users need to scan a long list; preserve source order when it represents a meaningful sequence, such as workflow stages. Neither choice changes the original source cells.

Common problems and fixes

  • The dropdown still shows repeats: Confirm that the validation rule uses =UniqueOptions, not the original range. In Data > Data Validation, inspect the Source field for the selected cell.
  • The helper formula shows an error: Check that the output cells below H2 are empty. Any existing value in the spill area can cause a #SPILL! error. Also check for a source range with no nonblank entries, which can make FILTER return an error.
  • New entries do not appear: Make sure they are inside the range referenced by the formula. A formula ending at A100 will not include a value entered in A101. Expand the source reference or use an appropriately designed Table-based formula.
  • The name is not accepted: Names cannot contain spaces, and the Source field should refer to the defined name with a leading equals sign. Verify the spelling and check that the name refers to the correct sheet and spill cell.
  • Choices that look alike remain separate: Inspect for extra spaces, unusual characters, or inconsistent typing. Clean the source values before generating the unique list.
  • The rule works on one sheet but not another: A defined name is particularly useful for a source on a different worksheet. A direct range reference to another sheet may be rejected in the validation Source box.

To inspect an existing rule, select a cell that has the dropdown and open Data > Data Validation. If multiple selected cells have different validation settings, Excel may not show one common rule; inspect a representative cell individually.

Choose the right method for your workbook

Use a UNIQUE-based helper list when the source changes and you want the dropdown options to stay current. It keeps the source data untouched and reduces routine maintenance, but requires a version with dynamic array functions and a correctly defined name. Use Advanced Filter or Remove Duplicates on a copy for a one-off list or an older Excel installation. Use a manually maintained range for a short list that rarely changes and where explicit control matters more than automation.

Consider who will maintain the workbook. A helper formula and defined name are easy to update once their purpose is documented, but an unfamiliar user may not know where the options come from. Label the source and helper columns, and consider adding a brief note that the validation rule uses the named range. If collaborators use older Excel versions, verify that the chosen formula functions are supported before sharing the file.

Excel for Mac has the same core Data Validation and dynamic-array approach, though ribbon placement and dialog appearance can vary slightly by release. In Excel for the web, list validation and dynamic arrays are available, but some name-management tasks are more convenient in the desktop application. If you cannot create or edit the defined name in your web workflow, set up the name in desktop Excel, save the workbook, and then check the dropdown in the browser.

Frequently Asked Questions

Can Excel remove duplicates directly from the dropdown?
Not as an automatic setting in the Data Validation dialog. Create a distinct source list with UNIQUE, Advanced Filter, or a cleaned copy, then point the validation rule to that list.

Does removing duplicates delete repeated values from my data?
A UNIQUE formula does not edit its source. The Remove Duplicates command does remove repeated rows from the selected range, so copy the data first if you need to preserve the original records.

Can I use UNIQUE directly in the Data Validation Source box?
It is more dependable to put the formula in a worksheet cell and create a defined name that refers to its spill range. Set the validation Source to that name. This also makes the setup easier to inspect and reuse.

How do I keep the list updated when I add choices?
Use a dynamic formula and make sure new entries fall within its source reference. For a spill-based validation list, define the name to refer to the complete spill range with the # operator. A fixed copied list will not refresh by itself.

Why are two choices that look the same both listed?
They may contain different underlying characters, such as a trailing space or a nonbreaking space, or may be stored as different data types. Check and clean the original values, then review the generated list.

Does the dropdown remove duplicate selections from the cells where people enter data?
No. The rule controls which values users can choose; it does not prevent the same choice from being selected in multiple cells. Preventing repeated selections across input cells requires a separate design, such as conditional formatting or a custom validation rule.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.