How to Create a Dynamic Dropdown List in Excel?
Struggling with static dropdown lists? Discover how to make your dropdowns dynamic so they update automatically with your data.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Create a data validation dropdown list that automatically updates when new items are added to the source list"
=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)This formula creates a dynamic range that adjusts as new items are added to the source list.
Step-by-step breakdown
- Use OFFSET to define the starting point of the range.
- Set the height of the range using COUNTA to count non-empty cells.
- Ensure the range expands as new items are added.
- Apply this range in Data Validation to create the dropdown.
Edge cases & warnings
- The source list contains blank cells, affecting the count.
- Items in the source list are deleted, causing the dropdown to shrink.
- The dropdown is not refreshing if the source list is on a different sheet.
- Data Validation settings may not update automatically in older Excel versions.
Examples
"Source list in A1:A5 with values: Apple, Banana, Cherry"
Dropdown shows: Apple, Banana, Cherry"Source list in A1:A10 with values: Apple, Banana, Cherry, (A4 is blank), Date"
Dropdown shows: Apple, Banana, Cherry, DateFrequently asked questions
How do I set up data validation for the dropdown?
Select the cell, go to Data > Data Validation, and use the dynamic range formula.
Can I use this with multiple columns?
Yes, but you will need to adjust the OFFSET formula accordingly.
What if my source list is on another sheet?
You can reference it directly in the OFFSET formula, but ensure the sheet name is included.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.