A normal Excel drop-down often points to a fixed range. Add a new value below that range and the list does not notice. A dynamic drop-down fixes the source so new items appear without editing Data Validation again.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac. The Table method is the most compatible choice; dynamic-array formulas are cleanest in Microsoft 365.
Turn the source list into a Table
This is the easiest and most dependable approach.
- Click inside the source list.
- Press Ctrl+T on Windows, or use Insert > Table.
- Confirm the range and keep My table has headers checked if the first row is a header.
- With the table selected, open Table Design and give it a clear Table Name, such as ProductList.
- Click the cell where the drop-down should appear.
- Open Data > Data Validation.
- Set Allow to List.
- In Source, enter the table column reference, such as =ProductList[Item], replacing Item with your actual header.
- Click OK.
When you add another row to the Table, Excel expands the table and the drop-down list sees the new item automatically.
Use a dynamic named range
Use this when you do not want to convert the source list into a formal Excel Table.
- Open Formulas > Name Manager > New.
- Enter a name such as ProductList.
- In Refers to, enter a formula that returns the current list range. In Microsoft 365, a spilled range such as =Sheet1!$A$2# can point to all results beginning in A2.
- Click OK and close Name Manager.
- Select the drop-down cell.
- Open Data > Data Validation > List.
- Set Source to =ProductList and click OK.
More control
Sort the list automatically
If you are using Microsoft 365 dynamic arrays, build the named range from a sorted formula such as SORT so the drop-down appears alphabetically even when entries were typed in a different order.
Remove blanks and duplicates
Fixed ranges can show blank choices if the source points beyond the real list. Use the Table method to avoid that, or build the named range with FILTER and UNIQUE so empty cells and repeated values are excluded.
Check the actual Data Validation source
If new items do not appear, open Data Validation and confirm Source points to the table column or named range, not an old plain range like A2:A10.
Review the error alert
A strict Data Validation error message can make it seem as though the list is broken. Check the Error Alert tab if users cannot enter expected values.
Sources
- Microsoft Support – Create a drop-down list (2025)
- Microsoft Support – Overview of Excel tables (2025)
- Microsoft Support – Apply data validation to cells (2025)