Yes—but Excel’s Data Validation list does not automatically remove duplicates from its source. In Microsoft 365 for Windows, create a distinct list with the UNIQUE function in a helper range, then point the dropdown to that spilled list through a defined name; if you mean preventing people from choosing the same item twice, add a separate custom validation rule.
What “unique list” can mean in Excel
The phrase usually describes one of two different goals. A dropdown might need to show each available choice only once, even though the source column contains repeated entries. Or a form might need to stop users from entering the same choice in multiple cells. These are related, but they require different setups.
A Data Validation list controls which values a cell offers or accepts. It does not deduplicate the source range, and a clean dropdown list does not automatically prevent two cells from having the same selection. Decide which outcome you need before building the rule. You can use both methods together when a form needs a tidy menu and unique selections.
Create a dropdown from distinct source values
Put the helper formula somewhere with enough empty space below it for the results to spill. For this example, assume the original values are in A2:A100 on a sheet called Entries, and the helper list will begin in H2 on that same sheet. In H2, enter:
=UNIQUE(A2:A100)
The result is a list of distinct values from the source range. UNIQUE is available in Microsoft 365 and Excel 2021 or later. It returns a dynamic array, so Excel expands the results into the cells below H2; do not type other content into that spill area.
- Select the cell or cells that should contain the dropdown.
- On the Windows ribbon, choose Data > Data Validation. In the dialog, set Allow to List.
- In the Source box, use a defined name that refers to the helper list’s spill range. A name is a dependable way to connect a validation list to a dynamic array.
- Make sure In-cell dropdown is selected, then choose OK.
To create the name, choose Formulas > Name Manager > New. If the helper formula is in H2 on a sheet named Entries, name the range UniqueItems and set Refers to to =Entries!$H$2#. The # refers to the entire spilled result, including items added later. In Data Validation’s Source box, enter =UniqueItems. If your sheet name contains spaces, Excel requires single quotes around it in the reference, as in ='Source Data'!$H$2#.
On Excel for Mac, the Data Validation command is also on the Data tab, although dialog layout can vary by release. Excel for the web can work with many existing validation lists, but desktop Excel is the clearest place to create and manage this named spill-range setup.
Sort the list and remove blank entries
UNIQUE removes repeated values but does not sort the results. If the source contains empty cells, those may also produce an unwanted blank-looking item. To get an alphabetical list while excluding blanks, keep the same source layout—values in A2:A100 on Entries, with the formula in H2—and use:
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"","")))
FILTER keeps only nonblank source cells, UNIQUE removes repeats, and SORT orders the remaining values. The final empty-string argument gives FILTER a result if there are no matching nonblank entries. These functions are available in Microsoft 365 and Excel 2021 or later. If you prefer the source order, omit SORT and use =UNIQUE(FILTER(A2:A100,A2:A100<>"","")).
For a short, stable list, you can also type choices directly into Data Validation’s Source box, separated by commas. That is quick for a few fixed options, but it does not calculate unique values from your data, is awkward to maintain, and is constrained by the source box’s character limit. A helper range is a better fit for a list that changes.
Prevent duplicate selections across cells
If users are filling several cells and each choice must be used only once, a distinct dropdown list alone is not enough. Keep the dropdown if it is useful, then add a Custom validation rule to the entry cells. For example, suppose the allowed choices are in the named list above and users make selections in D2:D100. Select D2:D100 with D2 as the active cell, open Data > Data Validation, set Allow to Custom, and enter:
=OR(D2="",COUNTIF($D$2:$D$100,D2)=1)
The formula allows an empty cell, or allows the current entry only when it appears once in the selection range. The reference to D2 is relative so the rule adjusts for each selected cell; the dollar signs keep the COUNTIF range fixed. If blanks should not be permitted, use the same layout and formula =COUNTIF($D$2:$D$100,D2)=1 instead. Set the Error Alert style to Stop if you want Excel to reject typed entries that fail the rule.
To keep both a dropdown and the duplicate check, Excel’s normal validation dialog cannot apply two independent validation rules to one cell at once. You can instead make the dropdown’s source list depend on the remaining available options, or use a different design such as a helper warning column. For many simple forms, retaining the dropdown and checking duplicates in a separate column is easier to maintain. The right choice depends on whether preventing repeats is essential or merely helpful.
Keep the dropdown current as data changes
A dynamic array updates when its source values change, and the spill reference in the defined name follows the changed result. If a new source value is added inside A2:A100, the helper list recalculates and the dropdown can offer it without manually editing the validation source. The fixed range in this example does not include entries typed below row 100, however. Expand the range when needed or use an Excel Table for a growing source list and adjust the formula to refer to the table’s data column.
The helper formula needs room to spill. If a value, merged cell, or another obstacle blocks the output area, Excel displays a spill error and the list will not work as expected. Put the helper on a dedicated worksheet or in an unused column, and avoid placing the source list inside the cells where users enter selections.
Excel also treats the unique list as a calculated view of the source, not as a permanent catalog. Removing a value from the original data can remove it from the helper list. If users need choices to remain available after they disappear from transaction records, keep a separate maintained list of approved choices instead.
Limits and common problems
- The formula appears as an error: Confirm that your Excel version supports the dynamic-array functions and that the spill area is clear. A workbook opened in an older Excel version may not calculate these functions correctly.
- The dropdown is empty or incomplete: Check that the defined name points to the helper cell with the formula and includes the # spill operator. Also confirm the Data Validation Source is
=UniqueItems, with the leading equals sign. - Blank entries or zero appear: Use the FILTER version to exclude empty source cells, and check whether the source contains formulas returning empty strings or numeric zeros that are legitimate values.
- A duplicate still gets in: Data Validation is not a complete data-security boundary. Copying and pasting can place values into cells without the normal validation prompt, so inspect or audit imported and pasted data when uniqueness matters.
- Two entries look the same but are treated differently: Extra spaces or other hidden characters can make text values distinct. Clean the source data—for example, by removing unwanted spaces—rather than assuming the dropdown will standardize it.
Validation also has an Error Alert setting. A Stop alert is the strictest choice for ordinary typing; Warning and Information alerts let users proceed after a message. Even with Stop selected, validation should not be treated as a guarantee that all data entered by every route is clean. For important records, add a review or duplicate-check step.
When to use another approach
A formula-driven helper list is a good fit when source values change and you want a dropdown to stay current. A manually maintained list is preferable when the allowed choices are governed by a process and should not change just because a new value appears in transaction data. A Table can make a source range easier to extend, while conditional formatting can highlight repeated entries for review without rejecting them.
For a small list that rarely changes, entering choices directly in the validation dialog may be simplest. For a form requiring distinct selections, use a custom duplicate rule or a design that offers only unused choices. And when data arrives through imports, pasted blocks, or other automated processes, validate the finished data separately: a dropdown is an entry aid, not a substitute for data cleanup.
Frequently Asked Questions
Can I use UNIQUE directly in the Data Validation Source box?
Usually, the reliable approach is to put UNIQUE in a worksheet cell, define a name for its spill range, and use that name as the Source. The validation dialog is not a worksheet cell where you can freely enter a dynamic-array formula as if it were a normal formula.
Does a unique dropdown stop users selecting the same item twice?
No. It removes repeats from the choices shown in the dropdown, but different input cells can still select the same choice. Add a duplicate-check rule or use a selection design that removes already-used items.
Can I make the list alphabetical?
Yes. Wrap the unique results in SORT, as in the formula above. The helper list will then be alphabetized as its source changes.
Why does my unique list show a blank?
The source range may contain empty cells, and the unique results can include a blank-looking item. Filter out empty strings before applying UNIQUE, or maintain a clean source range without blanks.
Will the rule catch duplicates pasted into the cells?
Not reliably. Pasting can bypass normal Data Validation prompts. Use a separate duplicate check or review step when the workbook may receive pasted or imported data.
Related
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
- Contacts List Template Excel: Build a Useful Workbook
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Attendance Sheet With Time: Setup and FormulasTo make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out,…
- Calculate Quarterly Dates in Excel: Formulas and ExamplesTo calculate a calendar quarter’s first day in Excel, put a date in A2 and enter =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1); for…
- Excel CONCAT: Join Cell Values with a FormulaTo combine cell values in Excel, use the CONCAT function: with text in A2 and B2, enter =CONCAT(A2,B2)…
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
- SUMIF Formula Criteria Examples for Excel: Rules and TipsUse Excel’s SUMIF function to add values that match one condition, such as a product name, a minimum…
- Excel Filter: Exclude Specific Text from ResultsIn Excel for Microsoft 365 on Windows, open the filter arrow on the column you want to check,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.