Excel Data Validation: Allow Unique List Values?

No: an Excel data-validation dropdown does not, by itself, require unique choices or stop the same choice being entered in multiple cells. You can remove duplicates from the dropdown’s source with a dynamic formula, or use a custom validation rule to block repeated entries—but a cell can use only one validation type at a time, so those two goals need different approaches.

What “unique values only” can mean

The question has two common meanings. First, you may want a dropdown whose options contain each label just once, even though the original source range has repeats. Second, you may want each selection to be used only once across a column—for example, assign each staff member to one task, with no one assigned twice. These are separate problems.

A list validation rule restricts entries to items in its source, but it does not deduplicate that source or track what has already been chosen in other cells. If the source contains “North” twice, “North” can appear twice in the menu. And if “North” appears once in the menu, different cells can still select it repeatedly. Choose the method based on which kind of uniqueness you need.

Create a dropdown with distinct options

In Microsoft 365 for Windows, put a formula in an unused helper column to produce a distinct list, then use that result as the dropdown source. For example, assume the original choices are in Lists!A2:A10 and the helper formula will go in Lists!H2. The formula below removes blank cells, returns each different value once, and sorts the results alphabetically:

With source values in Lists!A2:A10 and the formula entered in Lists!H2, use =SORT(UNIQUE(FILTER(A2:A10,A2:A10<>""))). If the source contains Red, Blue, Red, and Green, the spilled result is Blue, Green, Red. The formula requires Excel 2021 or Microsoft 365; the dynamic array result fills cells below H2 automatically.

Select the cells that should have the dropdown.
Choose Data > Data Validation. On the Settings tab, set Allow to List.
For a source on another worksheet, create a workbook-level name in Formulas > Name Manager. Name it UniqueItems and set Refers to to =Lists!$H$2#. The # refers to the full spilled result.
In the validation dialog’s Source box, enter =UniqueItems, then select OK. Keep In-cell dropdown selected if users should see the arrow.

A name is a useful bridge between the helper sheet and the validation rule: Excel’s list-source box does not accept every direct cross-sheet reference. If your helper list is on the same sheet, you can also point the source to a fixed range that covers its results, though blank cells in that range may appear as empty options. A named spill range keeps the source connected to the formula’s changing size.

Block duplicate entries in a range

If the aim is to stop a value being entered more than once, use a Custom validation rule instead of List. Suppose the permitted entries go in C2:C100. Select that range, open Data > Data Validation, set Allow to Custom, and enter this formula. The reference C2 is relative so the rule checks the cell being edited; the range is absolute so every cell checks the same full area.

With entries in C2:C100 and C2 as the first selected cell, use =OR(C2="",COUNTIF($C$2:$C$100,C2)=1). A nonblank entry is accepted only when it occurs once in the range; a second copy is rejected. For instance, entering “Morgan” in C2 is allowed, while entering “Morgan” in C3 is not. The OR condition allows empty cells to remain empty.

In the validation dialog, set the Error Alert style to Stop if you want Excel to reject a duplicate typed directly into a cell. You can customize the alert text—for example, “Choose a value not already used.” This rule works for manual entry, but it is not a substitute for reviewing the data: existing duplicates are not automatically removed, and pasting or importing data can bypass or replace validation.

Why you cannot combine both rules in one cell

Excel assigns one validation type to a cell: List, Whole number, Decimal, Date, Time, Text length, or Custom. A cell cannot simultaneously have a List rule that supplies a dropdown and a Custom rule that checks whether a selection is duplicated. Applying a second rule replaces the first for the selected cells.

That limitation matters when a dropdown should both show approved choices and prevent repeated selections across rows. A distinct helper list solves repeated items in the menu, but it does not prevent the same option being chosen again. A Custom formula can reject a repeat, but it does not provide the dropdown. If both behaviors are essential, consider a different workflow, such as selecting from a separate assignment interface or using a macro, and test it with the people and workbook environment involved.

What the duplicate-check formula does—and does not do

COUNTIF counts cells matching the value in the edited cell. The formula accepts a value when that count is one, meaning the current entry has no second match in the checked range. The cell reference C2 must match the top-left cell of the selected range. If you select D5:D50 instead, adjust both references so the formula starts with D5 and the fixed range covers D5:D50.

COUNTIF is not case-sensitive: “Morgan” and “MORGAN” are treated as the same text for this check. It does not normalize spaces or punctuation, so “Morgan” and “Morgan ” may be treated as different entries. If consistent spelling matters, clean the source data or set clear entry instructions. Also make the checked range large enough for expected future entries; a rule limited to C2:C100 does not check a new entry in C101.

Validation is an entry aid, not a complete data-integrity system. Excel can show a warning or stop a direct typed entry, but copying and pasting, filling cells, importing, or running automation may introduce values that do not follow the intended rule. Use a separate audit when accuracy is important. With data in C2:C100, this formula flags a value whose count is greater than one:

With values in C2:C100, enter =COUNTIF($C$2:$C$100,C2)>1 in a helper column beside the first row and fill it down. TRUE indicates that the value in that row appears more than once. This makes existing or pasted duplicates easier to find; it does not delete or correct them.

Keeping the source list current

The UNIQUE-based helper list updates when its source values change, provided the source range includes the new entries. A fixed reference such as A2:A10 will not include a value added in A11. For a growing list, an Excel Table can make the source easier to expand: select the source data and choose Insert > Table, then use a structured reference in the helper formula. If you prefer a conventional range, allow room for future entries and keep the formula’s range aligned with it.

The helper formula needs clear space below it for the spilled results. If another value or merged cell blocks that area, Excel displays a spill error and the dropdown source will not work as intended. Place the helper list in an unused area or on a dedicated Lists worksheet. Do not type into the cells where the results spill; edit the original source values instead.

Choose the method that fits your task

Only remove repeats from the menu: use UNIQUE, optionally with SORT and FILTER, then point List validation to the result.
Allow free typing but reject repeats in a range: use the Custom COUNTIF rule and set the error alert to Stop.
Allow only approved entries and also require each to be used once: Excel’s built-in validation cannot apply both rules to the same cells. Consider a redesigned workflow, or use a separate check to identify repeats.
Find duplicates already in a range: use a helper count or conditional formatting to highlight them, then review the flagged values rather than assuming validation has cleaned them up.

For a simple list of categories, duplicate dropdown options are usually best fixed at the source. For one-time assignments such as equipment IDs or staff allocations, the more important requirement is often preventing or detecting repeated entries. Decide which rule matters before setting up validation, because the choice affects how people enter data and how exceptions are handled.

Windows, Mac, and version notes

The Windows paths above apply to Excel for Microsoft 365. Excel for Mac also provides Data Validation and the UNIQUE, SORT, and FILTER functions in supported versions; ribbon placement and dialog layout can differ slightly, so look for Data > Data Validation. On Mac, Option+F11 is commonly used to open the Visual Basic Editor, whereas Windows uses Alt+F11. Keyboard shortcuts are not necessary for either validation method.

Excel for the web supports common validation tasks, but the desktop app is the safer choice when creating and troubleshooting a named dynamic-array source or a more involved workbook setup. UNIQUE, SORT, and FILTER are available in Microsoft 365 and Excel 2021 or later. In older Excel versions without those functions, create a distinct source list manually or use an appropriate legacy method; do not expect the formula above to calculate there.

Frequently Asked Questions

Can Excel show each dropdown option only once?
Yes. Make a distinct helper list with UNIQUE, then use that result as the List validation source. This removes duplicate options from the menu, but does not stop the same option being selected in different cells.

Can I prevent users from selecting the same dropdown choice twice?
Not with the built-in List rule alone. A Custom COUNTIF rule can reject repeated values, but a cell cannot have both that Custom rule and a List rule at once. A separate check or a redesigned entry process may be needed for both requirements.

Does data validation remove duplicates already in my cells?
No. Validation controls or warns about later entries; it does not clean existing data. Use a duplicate check, review the results, and remove or correct repeats deliberately.

Why can someone still paste a duplicate into a validated cell?
Paste and import operations can bypass validation or overwrite the cell’s validation settings. Treat validation as a helpful guardrail, then audit important lists for duplicates after bulk edits or imports.

Is Excel’s duplicate check case-sensitive?
No. COUNTIF treats text that differs only by capitalization as a match. It may not treat extra spaces or other formatting differences as the same value, so consistent data entry still matters.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.