Excel Data Validation: The Complete Guide (2026)
Home›Excel›Data Validation
ExcelPillar Guide⏱ 22 min read

Excel Data Validation: The Complete Guide

Data validation is the difference between a spreadsheet that stays clean for years and one that turns into "which spelling of 'Complete' did we agree on?". A few well-placed rules eliminate typos, enforce structure, and make dashboards and formulas trustworthy. This guide covers every validation type, dependent dropdowns, custom formulas, error handling, and the patterns that pay for themselves the first day.

What data validation is

Data validation restricts what users can enter into a cell. You define a rule; Excel enforces it. When someone types something that doesn't match, Excel either blocks the entry, warns them, or shows an info message — depending on how strict you set the rule.

Two big wins from validation:

  1. Data quality. No typos in Status columns ("Complete" vs "complete" vs "Compelte"). Dropdowns force consistency.
  2. Guidance. Users see what they're allowed to enter before making a mistake. Reduces support questions and rework.
Validation is layered on top of data

Validation rules apply to what users type next, not to what's already in the cell. Adding validation to a range with existing bad data doesn't fix the bad data — but the "Circle Invalid Data" tool highlights the problems so you can clean them.

Where to find it

Ribbon: Data → Data Validation

The dropdown has three options:

  • Data Validation… — opens the rule editor (the main dialog)
  • Circle Invalid Data — puts red circles around cells that violate current rules
  • Clear Validation Circles — removes the circles (doesn't touch the data or the rules)

The validation types

The Settings tab of the Data Validation dialog has an "Allow" dropdown with eight options.

Any value (default)

No restriction. Same as no validation. Only useful for clearing an existing rule while keeping the Input Message or Error Alert configured.

Whole number

Only integers. Sub-options: between / not between / equal to / not equal to / greater than / less than / greater than or equal to / less than or equal to. Two operand fields (or one for equal/greater/less variants).

Allow: Whole number → Data: between → Minimum: 1 → Maximum: 100

Decimal

Same as Whole number but allows fractional values. Use for prices, percentages, measurements.

List

The most popular choice. Restrict entries to a specific set of values, shown as a dropdown. Values can be typed inline (comma-separated) or come from a range.

Allow: List → Source: Red,Green,Blue (or =Sheet2!$A$1:$A$10)

Date

Only date values, with range options. Great for start/end dates that must fall in a specific window.

Allow: Date → Data: greater than or equal to → Start date: =TODAY()

Time

Only time values. Common for shift schedules and appointment slots.

Text length

Restricts the number of characters. Perfect for fixed-length codes, PINs, or short-answer fields.

Allow: Text length → Data: equal to → Length: 10 (for a fixed 10-character SKU)

Custom

Any formula that returns TRUE (allow) or FALSE (block). The most powerful option — use it for cross-cell rules, pattern matching, or anything the built-ins don't cover. Covered in detail below.

The simplest dropdown (inline values)

  1. Select the cell(s) that need the dropdown
  2. Data → Data Validation
  3. Allow: List
  4. In Source, type the values separated by commas: New,Contacted,Qualified,Won,Lost
  5. OK

Every cell in the selection now has a dropdown arrow. Users can pick from the list or type a matching value.

Inline lists don't scale

Editing a rule to add or remove options means going back into the dialog. Fine for a fixed list of 5-6 items; painful for anything that changes. Use range-based sources for anything else.

Dropdown from a range

Put your list values in a column somewhere (usually a "Settings" or "Lookups" sheet).

  1. List values, one per row, on the source sheet
  2. Select the cells that need the dropdown
  3. Data → Data Validation → List
  4. Source: click into the field, then select the range with your values (e.g., =Settings!$A$2:$A$20)
  5. OK

Dropdown from a named range

Named ranges make source references readable and portable.

  1. Select your list range on the source sheet
  2. Formulas → Define Name → give it a clear name like StatusList
  3. OK
  4. In Data Validation, set Source to =StatusList

Now if you rearrange sheets or move the list, the reference still works.

Dropdown from a Table column

The best option for lists that grow over time.

  1. Format your list as a Table (Ctrl+T)
  2. Name the Table (Table Design → Table Name): tblStatus
  3. Create a named range pointing to the Table column: Formulas → Define Name → Name: StatusList, Refers to: =tblStatus[Status]
  4. Use =StatusList as your Data Validation source

As you add rows to the Table, the dropdown expands automatically. No more updating validation rules when the list grows.

Why not point Source directly at a Table column?

Data Validation's Source box doesn't accept structured references like =tblStatus[Status] directly. The workaround is a named range that does reference the Table column. One level of indirection, but the flexibility is worth it.

Dependent dropdowns

The showcase feature. One dropdown's choice controls the options in another dropdown.

Example: Region column, then City column. Pick "West" for Region and the City dropdown shows only western cities. Pick "East" and it shows eastern cities.

Setting up dependent dropdowns

  1. On a "Settings" sheet, create columns with headers matching your first-level values: West, East, Central
  2. Under each header, list the second-level options: cities under West, cities under East, etc.
  3. Select each column (including the header) and name the range using Formulas → Create from Selection → Top row → OK. This creates a named range for each column, using the header as the name.
  4. In your data sheet: apply normal List validation to the first column (Region), with source = your list of top-level values
  5. For the dependent column (City), apply Data Validation with Allow: List → Source: =INDIRECT(A2) (where A2 is the Region cell for that row)

Now pick "West" in Region — the City dropdown shows only cities named under the West range. Pick "East" — cities change to eastern cities.

The INDIRECT trick explained

INDIRECT converts text to a reference. If A2 contains "West", INDIRECT(A2) resolves to a reference to the named range called "West". Data Validation reads that range as its source. Change A2 to "East" and the reference updates automatically.

Named range names have restrictions

Named ranges can't contain spaces or start with numbers. If your first-level values have spaces ("North America"), replace spaces with underscores in the named range ("North_America") and use SUBSTITUTE in the INDIRECT formula: =INDIRECT(SUBSTITUTE(A2, " ", "_")).

Three-level dependent dropdowns

Country → State → City. Same pattern, applied twice:

  • Column A: Country (list of countries)
  • Column B: State (source = =INDIRECT(A2))
  • Column C: City (source = =INDIRECT(B2))

Named ranges must exist for each combination. This scales to any depth, though the settings sheet gets large quickly. For deep hierarchies, consider Power Query to reshape a normalized lookup table into named ranges instead.

Custom formula validation

When no built-in rule fits, use Custom. Enter a formula that returns TRUE (allow the entry) or FALSE (block it). Excel evaluates the formula for the top-left cell of the selection, adjusting relative references as it moves across the range.

Prevent duplicates in a column

Unique-values validation

=COUNTIF($A$2:$A$1000, A2) <= 1
Blocks any value that would create a duplicate. Apply to A2:A1000.

Enforce a text prefix

Codes must start with "INV-"

=LEFT(A2, 4) = "INV-"

Prevent weekends in a date column

Weekdays only

=WEEKDAY(A2, 2) <= 5

Cross-cell rule: end date after start date

Apply to the end-date cell (column B)

=B2 > A2
Blocks any end date not strictly after the start date in the same row.

Only allow entries when a checkbox is TRUE

Cell locked unless approval flag is on

=$G$1 = TRUE

Whole-row prerequisite: don't allow status "Done" without a completion date

Applied to the status column

=OR(A2 <> "Done", ISNUMBER(B2))
If status is "Done", column B must have a date. Any other status is unrestricted.

Regex-style patterns (Excel doesn't do regex, but this comes close)

Phone number: 10 digits

=AND(LEN(A2) = 10, ISNUMBER(VALUE(A2)))
Test custom formulas in a helper cell first

Custom validation formulas can be tricky. Before adding one to Data Validation, type the formula into a spare cell and test that it returns TRUE/FALSE as expected. Once verified, copy the same formula (minus the leading =) into Data Validation.

Input messages and error alerts

The Data Validation dialog has three tabs: Settings (which we've covered), Input Message, and Error Alert. Both are worth using.

Input Message

Text that appears as a tooltip when the cell is selected. Great for explaining what's expected before the user types anything wrong.

  • Title — bold heading of the tooltip
  • Input message — the body text (up to a few sentences)

Example: Title = "Deal Value"; Message = "Enter the deal size in USD. Leave blank if not yet estimated."

Error Alert

What happens when the user enters an invalid value. Three severity levels:

StyleBehavior
Stop (default)Blocks the entry. User must fix or press Cancel/Escape.
WarningAlerts the user but lets them proceed if they click Yes.
InformationNotifies but doesn't block. Any value is accepted.

Rule of thumb: Stop for critical fields (unique IDs, required categories); Warning for soft rules (recommended date range); Information for advisory-only.

Write helpful error messages

The default "This value doesn't match the data validation restrictions" is generic and unhelpful. Set a custom title and message: Title = "Invalid Region", Message = "Please pick one of: West, East, Central, or South". Users know exactly how to fix it.

Finding invalid data

Validation only applies to new entries. Existing values that violate the rule stay in place — but you can find them.

Ribbon: Data → Data Validation dropdown → Circle Invalid Data

Excel draws red circles around every cell whose value violates its validation rule. Fix them one at a time (the circle disappears when the value becomes valid) or use Clear Validation Circles to remove them without editing data.

Selecting all cells with validation

To audit which cells have validation on a sheet:

  1. Press F5 (or Ctrl+G) → Special
  2. Choose "Data validation" → "All" → OK
  3. Every validated cell is selected. Copy addresses or use Format Painter to spread the rules.

Under Special, the "Same" option selects only cells with the same validation as the currently selected cell — useful for finding all cells that share one rule.

Copying and clearing validation

Copy validation to a new range

Two ways:

  • Format Painter — click a validated cell, click Format Painter, drag over the target range. Validation copies along with formatting.
  • Paste Special — copy a validated cell (Ctrl+C), select the target, Paste Special (Ctrl+Alt+V) → check "Validation" → OK. Copies only the validation, not the data or formatting.

Extend validation as data grows

If your validation covers A2:A100 but you've now filled A101:A200, the new rows aren't validated. Fixes:

  • Format Painter one validated cell across the new rows
  • Or convert the range to a Table (Ctrl+T) — Tables auto-extend validation to new rows

Clear validation

Ribbon: Data → Data Validation → Settings tab → Clear All button

Removes the rule and Input Message and Error Alert. Data in the cells stays put. Alternative: Edit → Clear → Clear All in modern Excel doesn't only clear validation — it clears everything. Prefer the Data Validation dialog for surgical removal.

Common patterns

1. Status column with dropdown and color coding

  1. Data Validation List with your statuses
  2. Conditional Formatting rules per status value → different fill colors
  3. Together: fast entry (dropdown) + visual scanability (colors)

2. Owner column from a Team table

  1. Maintain a Table on a Team sheet with team members
  2. Named range =tblTeam[Name]
  3. Owner column validation → List → Source =NameList
  4. Adding a new hire to the Team table auto-adds them to the dropdown

3. Date column that only allows future dates

Prevent backdated entries

Allow: Date → Data: greater than or equal to → Start date: =TODAY()

4. Prevent duplicate IDs across a column

Custom unique-value validation

=COUNTIF($A$2:$A$10000, A2) = 1

5. Cascading region-country-city (three levels)

As described in Dependent dropdowns. The classic use case for forms.

6. "Choose one" with an "Other, please specify" option

  1. Data Validation List with your options plus "Other"
  2. Custom validation rule that only allows values from the list OR any text if the previous cell (also in the row) is "Other"
  3. Users pick from the list or type a custom value only when Other is selected

7. Required field enforcement

Prevent blanks in a critical column

=LEN(TRIM(A2)) > 0
TRIM handles trailing spaces. LEN > 0 requires something meaningful.

8. Total across a row must equal 100%

Percentage-allocation entry

=SUM($A2:$E2) <= 1
Allow each new entry only if the running total wouldn't exceed 100%. Applies to each of A2:E2.

Data validation in Google Sheets

Google Sheets has an equivalent under Data → Data validation. Notable differences:

  • Google's version has a cleaner interface for dropdown chips (colored labels)
  • Dropdown from range is simpler — just point at a range, no need for named ranges
  • Custom formulas work the same way with the same syntax
  • No exact equivalent of "Circle Invalid Data" — use conditional formatting to flag invalid entries instead
  • Dependent dropdowns need Apps Script or workarounds (INDIRECT works but is less native than in Excel)

Pitfalls to avoid

Users can still paste over validation

Data validation checks direct typing. Pasting (Ctrl+V) can insert invalid values without triggering the rule. If enforcement is critical, combine validation with a sheet protection scheme, or use conditional formatting to flag pasted invalid entries.

Merged cells break dropdowns

Data validation on merged cells behaves unpredictably. Only the top-left cell of the merge shows the dropdown, and the rule doesn't apply cleanly to the merged area. Avoid merged cells in data-entry columns.

Case sensitivity in List validation

List sources are case-insensitive when validating direct entry: typing "west" is accepted if "West" is in the list. But the value stored is what the user typed, not the list value. This can bite formulas that expect exact case. Add TRIM+PROPER helpers if downstream logic depends on capitalization.

Deleting the source range breaks the rule

Dropdowns pointing to a range on another sheet break when the source is deleted. Data → Data Validation shows the broken reference. Fix by editing the Source field to point to a valid range, or use Table-based sources that resist deletion.

Circular custom validation

A rule like =COUNTIF(A:A, A2) = 1 counts A2 itself as one match — subtly correct only because we're checking for exactly one. Change to <= 1 or > 1 and you'll get unexpected behavior. Test carefully.

Document your validation rules

Create a "Notes" or "Setup" sheet listing every column with validation, what the rule does, and where the source list lives. Six months later, this documentation saves your future self hours of poking around.

Data validation vs. conditional formatting vs. sheet protection

ToolPurposeBest for
Data ValidationRestrict what users can enterEnforcing dropdowns, ranges, patterns
Conditional FormattingHighlight cells that meet a conditionVisual signaling of good/bad values
Sheet ProtectionLock cells against editing entirelyProtecting formulas, headers, calculations

Real-world workbooks combine all three: sheet protection locks formulas, data validation enforces entry rules, conditional formatting draws attention to specific values.

Excel Wizard

Validation rules that don't break tomorrow

Dependent dropdowns, custom formulas, cross-cell rules — data validation gets tricky fast. Excel Wizard takes your intent ("dropdowns for Region and City where cities depend on region") and builds the named ranges, INDIRECT formulas, and validation rules with correct absolute references.

Install Excel Wizard →

Frequently asked questions

What is data validation in Excel?

Data validation restricts what can be entered into a cell. You define a rule (list, number range, date range, custom formula) and Excel enforces it. Users see dropdowns or get errors when entries don't match.

How do I create a drop-down list in Excel?

Select the range → Data → Data Validation → Allow: List. In Source, type comma-separated values or reference a range. OK.

How do I create dependent dropdowns in Excel?

Set up options as named ranges (one per category). Apply Data Validation → List with Source =INDIRECT(A2) where A2 holds the category. Selecting a category changes the dependent dropdown's options.

How do I remove data validation?

Select the cells → Data → Data Validation → Clear All → OK. Removes the rule but keeps the data. F5 → Special → Data Validation → All selects every validated cell on the sheet.

Why isn't my data validation dropdown showing?

Usually: in-cell dropdown is unchecked, the rule is Custom (not List), the source range is empty, or Excel is in Edit mode. Verify each in Data Validation settings.