Excel Data Validation: Dropdown Lists That Work (2026)

Quick answer: Excel data validation restricts what a cell can contain and can turn a cell into a dropdown list. Select the cells, open Data > Data Validation, choose List under Allow, and point Source at either a comma-separated set of values or a range. Tick the in-cell dropdown box to show the arrow. A list sourced from another sheet needs a defined name or a table reference — typing a range on a different sheet directly into Source is rejected.

Part of our Excel series. If formulas are still new, skim Excel formulas everyone needs first — validation pairs naturally with lookups.

Building an in-cell dropdown

The fastest list is a comma-separated set typed straight into the Source box. It works for status values that rarely change.

Data > Data Validation > Allow: List
Source:  Draft,Review,Published,Archived

For anything longer than a handful of items, keep the options on a sheet and reference them. Select the list cells, type a name in the Name Box (the small box left of the formula bar), and use that name as the Source. A named range is the reliable way to reference values on a different sheet.

Source:  =StatusList
      (where StatusList refers to =Lists!$A$2:$A$8)

If the options live in an Excel Table (Insert > Table), you can reference the column instead — =StatusTable[Status] — and the dropdown grows automatically whenever you add a row to the table. That is the cleanest pattern for lists that keep changing.

The validation types

Custom rules with formulas

The Custom option is where validation becomes powerful. Write a formula that returns TRUE for allowed values, referencing the active cell relative to the selected range. Two rules people reach for constantly:

// Allow only text that ends in a digit (applied to A2:A100)
=AND(LEN(A2)>3, ISNUMBER(VALUE(RIGHT(A2,1))))

// Reject duplicates in a list (applied to B2:B200)
=COUNTIF($B$2:$B$200, B2)=1

The reference must be relative to the top-left cell of the applied range, or the rule evaluates the wrong cell for every row below it. The duplicate rule above is the standard way to stop repeated invoice numbers being keyed twice, and it works alongside the cleanup steps in how to remove duplicates in Excel.

Input messages and error alerts

Validation has three tabs; the second and third are what make it friendly. An input message appears when the user selects the cell and tells them what to enter. The error alert controls what happens when the rule is broken.

The dropdown from a list validation also respects the Ignore blank check, so a blank cell is always allowed unless you untick it. This matters when users are still filling a sheet in and every required field would otherwise raise an error.

Finding invalid data later

Data validation applies to new typing; it does not clean data that is already wrong. Use Data > Data Validation > Circle Invalid Data to mark existing cells that break the rule, then correct them or paste a corrected column over the top. Combined with conditional formatting you can make out-of-range values turn red before anyone runs the report.

Common mistakes

• Cross-sheet range in Source. Excel rejects it; create a defined name or a table reference first.
• Rules copied with the wrong reference. The formula must be written relative to the top-left applied cell.
• Expecting validation to fix old data. It only governs new entries until you circle and correct the rest.
• Applying rules cell by cell. Select the whole range first so one rule covers every row.
• Forgetting Ignore blank. Unticking it makes partially filled sheets unusable.

Once the sheet is controlled, lookups against it are safer — the patterns in XLOOKUP, IF and SUMIF rely on consistently spelled values, and summaries are quickest to build with the steps in Excel pivot tables.

Drowning in messy spreadsheets at work? Ampersand Academy teaches Excel one-to-one, using your own workbook as the curriculum.

Frequently asked questions