Excel beginner data validation dropdown list unique

Data Validation Dropdown Lists in Excel

Data Validation Dropdown Lists is a Excel function that using unique with a table reference creates a dynamic dropdown source that automatically updates when new departments are added to the data.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Dropdown lists prevent bad data entry and make spreadsheets more user-friendly. Here's how to create them — static and dynamic.

Generate This Formula Browse Library

The Formula

Prompt

"Create a dropdown list that shows all unique department names"

Excel 
=UNIQUE(Departments[Department])

Using UNIQUE with a Table reference creates a dynamic dropdown source that automatically updates when new departments are added to the data.

Step-by-Step Breakdown

  1. First, create a helper range with =UNIQUE(Departments[Department])
  2. Go to Data > Data Validation > Allow: List
  3. Source: reference the UNIQUE formula's spill range (e.g., =E2#)
  4. The # (spill operator) captures all results from the dynamic array
  5. New departments added to the table automatically appear in the dropdown

Edge Cases & Warnings

  • UNIQUE and spill references require Excel 365 or 2021
  • For older Excel: use a static list or OFFSET-based dynamic range
  • Dropdown lists have a 255-character limit for direct-entry lists
  • Dependent dropdowns (cascading) require INDIRECT or FILTER formulas

Examples

Prompt

"Departments: [Sales, Engineering, HR, Marketing]"

Excel 
Dropdown shows all 4 options
Prompt

"Add new department: Legal"

Excel 
Dropdown automatically includes Legal

Frequently Asked Questions

How do I make a dependent (cascading) dropdown?

Create named ranges for each parent option's children. Then use =INDIRECT(A2) as the source for the dependent dropdown, where A2 is the parent selection.

Can I allow custom entries alongside the dropdown?

In Data Validation settings, uncheck 'Show error alert after invalid data is entered.' This allows the dropdown suggestions while still accepting custom text.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples