Excel Unique Dropdown List: Create One That Updates

To create a unique dropdown list in Excel for Microsoft 365, generate a deduplicated list with UNIQUE, then use that spilled range as the source for Data Validation. A dependable setup is to put the formula on a helper sheet, give its spill range a workbook-level name, and use that name as the dropdown source; the choices then update when the source data changes.

What a unique dropdown list does

A normal Excel dropdown can point to a range that contains repeated entries. If your source column says “North,” “South,” “North,” “West,” the dropdown can show “North” twice. A unique dropdown presents each distinct choice once, making long lists easier to scan and reducing inconsistent selections.

There are two related meanings of “unique” that are easy to mix up. Most people want a deduplicated list: include every distinct value, even if it appears many times in the source. Excel’s UNIQUE function does that by default. A different request is to show only values that occur exactly once. That requires its optional exactly-once argument, and it can produce a much shorter list.

The dropdown itself is created with Data Validation. The formula prepares the choices; Data Validation restricts what a user can select in the destination cells. Keeping those jobs separate makes the workbook easier to maintain.

Build the list with UNIQUE and FILTER

Suppose category names are in A2:A1000 on a sheet called Data. Put the following formula in D2 on a helper sheet called Lists. The source range in this example contains “Fruit,” “Office,” “Fruit,” “Travel,” and a blank cell; the formula returns Fruit, Office, and Travel, each once, in alphabetical order.

With source values in Data!A2:A1000 and the formula entered in Lists!D2, use:

=SORT(UNIQUE(FILTER(Data!A2:A1000,Data!A2:A1000<>"")))

FILTER removes empty cells, UNIQUE removes repeated entries, and SORT arranges the results alphabetically. The results spill from D2 into the cells below it automatically. Leave those cells clear; existing content in the spill area causes a #SPILL! error.

If your source data is on the same sheet as the formula, the shorter version is =SORT(UNIQUE(FILTER(A2:A1000,A2:A1000<>""))). The range should include the rows you expect to use, but avoid selecting an entire column unnecessarily in a large workbook: a bounded range can be easier to audit and may calculate more efficiently.

If you want the distinct values in the order they first appear instead of alphabetical order, omit SORT. With the same source range, use =UNIQUE(FILTER(Data!A2:A1000,Data!A2:A1000<>"")). This retains the source order while still excluding blanks and duplicates.

Connect the spilled results to Data Validation

Excel’s Data Validation list source is most reliable when you point it to a named range that refers to the spilled results. This also avoids a common restriction: a validation list source cannot always directly refer to a range on another worksheet.

On the Lists sheet, select the cell where people should choose an item, such as B2, or select the full destination range.
Choose Data > Data Validation. Under Allow, choose List, and make sure In-cell dropdown is selected.
Before creating the validation rule, create a workbook-level name. Choose Formulas > Name Manager > New. Enter a name such as UniqueChoices, set Scope to Workbook, and set Refers to to =Lists!$D$2#. The # refers to the complete spilled range starting in D2.
Return to the Data Validation dialog. In Source, enter =UniqueChoices, then choose OK.

Now the cell’s arrow opens a list of the distinct values. If your helper sheet has a space in its name, put single quotation marks around the sheet name in the named range reference, for example ='Lookup Lists'!$D$2#. The name itself should start with a letter or underscore and should not contain spaces.

On Excel for Mac, the corresponding commands are also on the Formulas and Data tabs; the precise dialog layout can vary by release. Excel for the web has a different interface, so if you cannot find the name or validation controls there, set up the rule in desktop Excel and then check that the dropdown works in the browser.

Make the dropdown update as data changes

A spilled formula resizes when its results change. If a new category is added within the source range, the formula recalculates, the spill includes the new value, and the named range ending in # follows the new spill size. The validation rule can therefore keep using the same source name instead of needing a new fixed range each time.

There is an important boundary: a formula cannot detect new entries outside the source range you gave it. If the formula uses A2:A1000 and someone adds a value in A1001, that value is not included. Choose a range large enough for expected growth, or use an Excel Table so the source expands as rows are added.

For example, if the source is an Excel Table named Orders with a column named Category, put the following formula in a helper cell. It returns the distinct, nonblank category names from the table, sorted alphabetically:

With category values in Orders[Category], use:

=SORT(UNIQUE(FILTER(Orders[Category],Orders[Category]<>"")))

Table structured references expand with the table, so the formula can include categories added in new table rows. Keep the helper formula outside the table: dynamic arrays do not spill inside Excel Tables. The named-range and Data Validation steps are otherwise the same.

Choose between distinct values and values that occur once

For a normal deduplicated dropdown, use UNIQUE with its default setting. For a list containing only items that appear exactly once in the source, set its third argument to TRUE. With source values in Data!A2:A1000 and the formula in Lists!D2, use:

=SORT(UNIQUE(FILTER(Data!A2:A1000,Data!A2:A1000<>""),FALSE,TRUE))

For the example values Fruit, Office, Fruit, and Travel, this returns Office and Travel: Fruit is omitted because it occurs twice. This is useful for finding one-off entries, but it is usually not what someone means when asking for a dropdown without duplicates. Check which interpretation you need before building the list.

Also decide whether alphabetical order is useful. Sorting makes a long list predictable, but source order can be meaningful—for example, a list of steps or a preferred sequence of statuses. Use UNIQUE alone when preserving first-seen order matters.

Common problems and practical fixes

The dropdown is empty or reports a source problem: Confirm that the validation Source is =UniqueChoices, that the name exists at workbook scope, and that its Refers to entry points to the formula cell’s spill range with #.
The formula shows #SPILL!: Clear cells below and beside the formula where its results need to appear. Merged cells or other obstructions in the spill area can also prevent results from appearing.
Blank choices appear: Use the FILTER condition shown above. If the source contains formulas that return an empty string, the condition removes those too. Cells containing spaces are not truly blank, so trim or clean inconsistent source entries if necessary.
A new value is missing: Check whether it falls inside the formula’s source range. With a fixed range, additions below its last row are ignored; a Table-based source is often easier to extend.
The list contains entries that look the same: Check for leading or trailing spaces, spelling differences, and inconsistent punctuation. For example, “Office” and “Office ” may appear identical to a person but are different text values. Clean the source data if those should count as the same choice.
Users can still type an invalid value: In Data Validation, check the Error Alert settings. A Stop-style alert blocks entries that do not meet the rule; an informational or warning-style alert may allow a user to proceed.

Dynamic-array functions such as UNIQUE, FILTER, and SORT are available in Microsoft 365 and Excel 2021 or later. If a workbook must work in an older Excel release, these formulas may not calculate there. Test the file in the oldest version your audience uses before relying on the dropdown.

Alternatives for older Excel versions

In Excel versions without dynamic arrays, you can place a deduplicated list in a helper range using the Remove Duplicates command or an Advanced Filter, then make that range the Data Validation source. These are workable options for a list that changes occasionally, but they do not automatically rebuild the choices every time source data changes. You need to refresh or recreate the helper list.

Another option is a PivotTable that summarizes categories, but it must be refreshed after source changes, and using its output as a validation source may require additional setup. For a simple, frequently changing list in Microsoft 365, a dynamic formula plus a named spill range is usually easier to keep current. For a small, fixed set of choices, typing the items directly into Data Validation may be simpler, provided the list is short and does not need to update from worksheet data.

Frequently Asked Questions

Can I use UNIQUE directly in the Data Validation Source box?
Use a helper cell for the formula and point Data Validation to a name referring to the spill range. This is more dependable than entering a dynamic-array formula directly in the Source box, especially when the source data is on another sheet.

Does UNIQUE remove blank cells automatically?
No. It can return a blank as one of the distinct results. Wrap the source in FILTER with a not-equal-to-empty-string condition when you want to exclude blank cells.

Will the dropdown change when I add a new item?
Yes, if the new entry is included in the formula’s source range and the formula recalculates. A Table-based source can expand with added table rows; a fixed range does not include entries placed beyond its final row.

Can I use the unique list in several dropdown cells?
Yes. Apply the same Data Validation rule to a range of destination cells, or copy a cell with the rule and use Paste Special > Validation. Each cell uses the same named list, while users make their selections independently.

Why does my list still show apparent duplicates?
Look for differences that are hard to see, especially extra spaces, punctuation, or inconsistent spelling. The values may not be identical to Excel even when they look similar on screen. Clean the source data before generating the unique list.

Can I make the dropdown searchable?
A standard Data Validation dropdown is not the same as a searchable combo box. For a short list, users can often type the first letters to move through matching entries, but a true search interface needs a different control or a custom design.

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.