Microsoft Excel

How to Keep an Excel Dropdown Unique as New Rows Are Added

Follow 5 steps to build a unique Excel dropdown that expands with new source rows and stays free of duplicate choices.

When a dropdown uses a fixed cell range, new source entries may not appear; when the source contains repeats, users may see the same choice more than once. In desktop Excel for Microsoft 365, Excel 2024, or Excel 2021, you can combine an Excel Table with the UNIQUE function and a named spill range. The result is a dropdown list that expands with new table rows and shows each nonblank item once. This guide is for people maintaining a workbook with a growing list, such as product names or departments.

Prerequisites

  • Tools: Desktop Excel for Microsoft 365, Excel 2024, or Excel 2021; a worksheet with source values and a worksheet for the dropdown.
  • Time: About 10–15 minutes for a simple workbook; allow more time to review an existing workbook’s names and validation settings.
  • Difficulty: Easy to moderate. The method uses an Excel Table, a dynamic array formula, and a defined name.

Step 1: Turn the source list into an Excel Table

Put the values you want to offer in one column with a header, for example, Item. Select a cell in the list and choose Insert > Table (or press Ctrl+T in Windows). Confirm that the table has headers. On the Table Design tab, give it a simple name such as tblItems.

A table expands when you type or paste a new value in the row immediately below it. Structured references, such as tblItems[Item], refer to the table column as it grows. Microsoft documents this table behavior for dropdown sources and for UNIQUE formulas.

Step 2: Create a unique helper list

Choose an unused column on a helper sheet, or create a separate sheet named Lists. In cell A1, enter a label such as Unique items. In A2, enter:

=SORT(UNIQUE(FILTER(tblItems[Item],tblItems[Item]<>"")))

FILTER excludes blank cells, UNIQUE removes repeated values, and SORT puts the choices in ascending order. A formula result that spills fills neighboring cells automatically; leave the cells below A2 clear. This formula requires a version of Excel that supports dynamic arrays and these functions. If sorting is unnecessary, use =UNIQUE(FILTER(tblItems[Item],tblItems[Item]<>"")).

Step 3: Name the spilled results

Open Formulas > Name Manager > New. Set the name to ItemChoices and set Scope to Workbook. In Refers to, enter a reference to the formula cell followed by the spill operator, for example:

=Lists!$A$2#

Use your actual sheet name and formula cell. The # spill operator refers to the full dynamic result, not just A2. Select OK and close Name Manager.

Step 4: Use the name as the dropdown source

Select the destination cell or range, then choose Data > Data Validation. Under Allow, select List. In Source, enter =ItemChoices, confirm In-cell dropdown is checked, and select OK. Apply validation to the input cells where users need the dropdown. A workbook-level name lets the validation rule refer to the list by a short, reusable label.

Step 5: Add a row and confirm the result

Type a new item directly beneath the last row of tblItems. Include an item already present as well as a genuinely new item if you want to check duplicate removal. Select a validated cell and open its dropdown: the new value should appear, while the repeated value should appear once. If the source is updated by a process that does not expand the table, resize the table or ensure the new data is entered in its next row.

Troubleshooting

  • The new entry does not appear: Check whether the source cell became part of tblItems. Confirm the formula uses the correct table and column names, then check that calculation is set to Automatic under Formulas > Calculation Options.
  • The dropdown is blank or shows an error: Make sure the spill area below the formula cell is empty. A value in the way causes #SPILL!. In Name Manager, confirm the name refers to the correct sheet, cell, and # operator.
  • Excel rejects the validation source: Confirm the name exists at workbook scope and that the Source field is =ItemChoices. If the workbook must support an older Excel version without dynamic arrays, use a manually maintained list or a different compatibility approach.

FAQ

Does an Excel Table alone remove duplicate dropdown choices?

No. A table can make a list source expand as rows are added, but it does not deduplicate the values. The helper formula using UNIQUE creates the distinct choices.

Can I use this dropdown in Excel for the web?

Microsoft’s current guidance says Excel for the web can edit some dropdowns with manually entered sources, while named-range sources may require desktop Excel to change. Create and maintain this named spill setup in desktop Excel, then check the workbook in your target environment.

Will the list update if I paste several new rows?

It should update when the pasted rows become part of the source table and the formulas recalculate. Verify that the table expanded, especially when pasting data from another workbook or importing it.

Sources

More in Microsoft Excel