Skip to content
Tutorials Center
My learning

Topics

Lesson 43 of 93 ยท Tables, Sorting and Filtering

Data Validation and Dropdown Lists

Learn Excel data validation: dropdown lists, whole number, date and custom rules, input messages, error alerts, dependent dropdowns and circling invalid data.

Most spreadsheet problems start with bad input: "Northh" instead of "North", a quantity of -5, a date typed as text, a duplicate invoice number. Data validation is Excel's way of stopping those mistakes at the door. You attach a rule to a cell or range, and Excel checks what people type. The rule can limit entries to a dropdown list, a number range, a date window or a formula you write yourself, and it can show a helpful message before and after the entry.

Validation is the foundation of every shared template, like the budget tracker and the invoice template. The dialogs here are real Excel. Excel cannot run on this site, so the examples are stand-ins: JavaScript programs that apply the same kinds of rules, so you can see what is accepted and rejected and why.

What a rule can allow

List Pick from a dropdown.
Numbers Whole or decimal, with limits.
Dates and times Inside a window you choose.
Custom Any formula that returns TRUE.
1 / 2

Setting up a rule

Select the cells, then choose Data โ†’ Data Validation โ†’ Data Validation.... The dialog has three tabs.

Settings holds the rule. Under Allow you pick the kind of rule, then fill in the details:

Allow Typical use Example
Whole number Quantities, ages between 1 and 100
Decimal Prices, percentages between 0 and 1
List Dropdown choices Open,Closed,On hold
Date / Time Deadlines, shifts greater than or equal to a start date
Text length Codes, comments equal to 5, at most 200
Custom Anything else a formula such as =COUNTIF($A:$A,A2)=1

The Ignore blank box lets empty cells pass. Leave it ticked for optional fields, and clear it for fields that must be filled (though a user can still delete the contents of a cell, which Excel does not treat as an entry).

Input Message shows a small yellow tip when someone selects the cell, for example "Enter a quantity from 1 to 100". Error Alert controls what happens after a bad entry, with a title, a message and one of three styles:

  • Stop: the entry is rejected. The user may only Retry or Cancel.
  • Warning: Excel explains the problem and asks "Do you want to continue?". The user may accept it anyway.
  • Information: Excel shows the message, and the user may accept the entry with OK.

Only Stop guarantees the rule. Choose a gentler style when the rule is a guideline rather than a requirement.

A List rule gives you a dropdown arrow in the cell. There are three ways to supply the items:

  1. Type them, separated by commas: Low,Medium,High. Fine for short, fixed lists. (Some regional settings use a semicolon instead of a comma.)
  2. Point at a range, such as =$F$2:$F$6. This is the best choice, because you maintain the list in one place. Put it on a separate sheet or area and give it a name (see Ranges and Named Ranges).
  3. Point at a spilled list, for example =$H$2#. When a formula like =SORT(UNIQUE(Data[Region])) produces the list, the # reference makes the dropdown grow and shrink with it. See Dynamic Arrays and Spilling.

Keep In-cell dropdown ticked, or users will not see the arrow. If your list is an Excel Table column, you cannot use a structured reference directly in the dialog in older versions. Name the column's range, or use the spill approach above.

Dependent dropdowns

A dependent dropdown changes with an earlier choice: pick a country, then see only its cities. The classic recipe uses names and INDIRECT:

  1. Lay out one column of cities per country, with the country as the header, and name each range exactly like its country: France, Japan, Italy.
  2. Give the first dropdown (say in A2) a list rule that points at the list of countries.
  3. For the second dropdown (B2), choose a List rule with the source =INDIRECT($A2). INDIRECT turns the text in A2 into a reference to the named range of that name.

Names cannot contain spaces, so for "United States" you would name the range United_States and use =INDIRECT(SUBSTITUTE($A2," ","_")). A modern alternative avoids INDIRECT altogether, using FILTER and a spill reference. See FILTER, SORT, SORTBY and UNIQUE. INDIRECT is volatile and recalculates constantly, which matters in huge workbooks (see OFFSET, INDIRECT and Dynamic References).

JS A stand-in for list and dependent dropdowns

Press Run (or Ctrl + Enter) to see the console output here.

No output.

When the first choice is not on the country list, the dependent source becomes #REF!, which is what Excel's INDIRECT returns for a name that does not exist, and the second dropdown shows an error.

Number, date and custom rules

Number and date rules take operators: between, not between, equal to, greater than and so on. The limits can be values, cell references or formulas, so a rule can say "the end date must be on or after the start date in B2". A date limit can also be dynamic, for example =TODAY() to forbid past dates. See Dates and Times in Excel.

Custom rules use a formula that must return TRUE for a valid entry. Write the formula as if for the top-left cell of your selection, with references relative to that cell; Excel adjusts it for the other cells just like copying a formula (see Cell References). Useful patterns for a selection that starts in A2:

TEXT
=COUNTIF($A$2:$A$500, A2) = 1        no duplicates in the column
=AND(ISNUMBER(B2), B2 > 0)           a positive number only
=LEN(C2) = 8                         exactly 8 characters
=AND(D2 >= TODAY(), WEEKDAY(D2, 2) < 6)   a future workday
=ISNUMBER(SEARCH("@", E2))           contains an @ sign
JS Validation rules with Stop, Warning and Information

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Limits and good habits

  • Validation only checks typing. Values that are pasted, filled by a formula, or already in the cell are not checked. Pasting over a validated cell can even replace the rule itself. To audit existing data, use Data โ†’ Data Validation โ†’ Circle Invalid Data, which draws red ovals around entries that break the rule, and Clear Validation Circles to remove them.
  • Copy rules deliberately. Use Paste Special โ†’ Validation to copy only the rule. To find validated cells, use Home โ†’ Find & Select โ†’ Data Validation.
  • Write error messages that help. "Enter a whole number from 1 to 100" is better than "Invalid input".
  • Protect the sheet if people must not delete rules (see Collaboration, Comments and Protecting Work).
  • Do not trust it alone. For important data, add checks with formulas like COUNTIF, covered in Finding and Removing Duplicates.
  • A typed list is limited to 255 characters total; use a range for longer lists.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which error alert style rejects a bad entry outright?

    Stop only allows Retry or Cancel. Warning and Information let the user keep the entry.
  2. 2. A user pastes a value of -50 into a cell that allows whole numbers from 1 to 100. What happens?

    Validation only checks typed entries. Use Circle Invalid Data to find values that slipped in.
  3. 3. What does the list source =INDIRECT($A2) do in a dependent dropdown?

    INDIRECT converts text into a reference. If A2 says France and a range named France exists, the second dropdown lists its cells.
  4. 4. In a custom validation formula for a selection starting at A2, which formula blocks duplicates in A2:A500?

    The range is anchored with $ and the criterion A2 is relative, so each cell counts how often its own value appears. A valid entry appears exactly once.
  5. 5. What does Circle Invalid Data do?

    It only marks cells that already hold values breaking the rule. You decide how to correct them.

Summary

  • Data validation restricts what can be entered: list, whole number, decimal, date, time, text length or a custom formula.
  • Input Message tips and Error Alert styles (Stop, Warning, Information) shape the experience; only Stop enforces the rule.
  • Dropdown sources can be typed, a range, a named range or a spill reference such as $H$2#; dependent dropdowns commonly use named ranges with INDIRECT.
  • Custom formulas are written for the top-left cell of the selection and must return TRUE.
  • Validation does not check pasted or old values; use Circle Invalid Data to audit.

Next: Finding and Removing Duplicates.