Data Validation in Excel: Rules, Dropdowns & Custom Formulas

Data validation forces cells to accept only the inputs you define. It's the difference between a spreadsheet that breaks when someone types "Jan" in a date field versus one that only accepts real dates. Whether you're building a data entry form or sharing a workbook with a team, validation rules prevent the garbage-in-garbage-out problem before it starts.

What Is Data Validation?

Data validation is a cell-level rule that either:

Restricts what can be entered (numbers only, dates in a range, values from a list)

Warns the user when input looks wrong but still allows it

Provides guidance via a pop-up message before they type

You find it under Data tab → Data Validation.

Setting Validation Rules

Select the cell or range you want to restrict, then open Data → Data Validation → Settings tab.

Number Rules

In the Allow dropdown, choose Whole Number or Decimal.

Then set the condition:

Between: min and max (e.g., 1 to 100)

Greater than / Less than / Equal to: single boundary

Not between: exclude a range

Example: A "Quantity" column should only accept whole numbers between 1 and 9999.

Allow: Whole Number

Data: between

Minimum: 1

Maximum: 9999

Date Rules

Allow: Date works the same way — set a date range using Between, or use dynamic references like =TODAY() to make the rule relative. Example: Only allow future dates in a "Deadline" column:

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

Text Length Rules

Allow: Text length → set a character limit. Useful for fields like ZIP codes (must be exactly 5 characters) or reference codes (max 10 characters).

Custom Formula Validation

Allow: Custom lets you enter any formula that returns TRUE or FALSE. If TRUE, the entry is accepted. Example: Only allow entries that start with "INV-": =LEFT(A2,4)="INV-" Example: Require a value in B2 before allowing input in C2: =B2<>"" Example: Prevent duplicate entries in column A: =COUNTIF($A$2:$A$100,A2)<=1

Dropdown Lists

Dropdown lists are the most-used validation type. They present users with a fixed set of choices, eliminating typos and inconsistent values.

Method 1: Manual List

Allow: List
Source: type values separated by commas: Yes,No,Pending,Cancelled

Good for small, stable lists. Not ideal if the list will grow.

Method 2: Range Reference

Allow: List
Source: select a range on the sheet, e.g., =$F$2:$F$10

Or type the range address. If the list is on a different sheet, you must use a named range (see below).

Method 3: Named Range

Type your list values somewhere (preferably on a dedicated "Lists" sheet)
Select them and go to Formulas → Define Name → give it a name like StatusList
In the validation Source field, enter =StatusList

Named ranges work across sheets and make the validation easier to maintain.

Input Messages

An input message is a tooltip that appears when the user clicks the validated cell — before they type anything.

Data Validation → Input Message tab:

Check Show input message when cell is selected
Title: "Enter a date" (bold header text)
Input message: "Use format MM/DD/YYYY. Must be a future date."

These are purely informational and never block input. Use them to guide users in forms.

Error Alerts

Error alerts fire when the user tries to enter an invalid value. There are three types with very different behaviors:

Style	Behavior	Icon
Stop	Blocks the entry entirely. User must retry or cancel.	Red circle
Warning	Warns but lets the user proceed by clicking Yes.	Yellow triangle
Information	Just informs the user. Always lets the entry through.	Blue circle

Data Validation → Error Alert tab:

Select style
Title: "Invalid Input"
Error message: "Please enter a whole number between 1 and 9999."

When to use each:

Stop: When bad data would break formulas or downstream processes
Warning: When the value is unusual but might be legitimate (e.g., unusually large order quantity)
Information: When you want to log or flag entries without blocking them

Custom Formula Validation: Advanced Examples

Validate Email Format (Basic Check)

=AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2)))

This confirms the cell contains both "@" and "." — a basic sanity check, not a full RFC email validator.

Only Allow Weekdays

=WEEKDAY(A2,2)<=5

WEEKDAY with mode 2 returns 1=Monday through 7=Sunday. Values 1-5 are weekdays.

Require Uppercase

=EXACT(A2,UPPER(A2))

EXACT is case-sensitive. This passes only if the value matches its own uppercase version.

Limit to Unique Values

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

Applied to the range A2:A1000. Each new entry is checked against all existing values. If the count is already more than 1, the entry is rejected.

Dependent Dropdowns

A dependent dropdown changes its options based on the value in another cell. Classic example: choose a Country, then the State/Region dropdown only shows states for that country.

Setup

Create your lists. Say you have:

- Column F: Fruits, Vegetables (main categories) - Column G: Apple, Banana, Mango (fruits) - Column H: Carrot, Broccoli, Spinach (vegetables)

Name each list to match the main category exactly:

- Select G2:G4 → Formulas → Define Name → Fruits - Select H2:H4 → Formulas → Define Name → Vegetables

For the main category cell (e.g., A2): Data Validation → List → Source: =$F$2:$F$3

For the dependent cell (e.g., B2): Data Validation → List → Source: =INDIRECT(A2)

INDIRECT converts the text value in A2 (e.g., "Fruits") into a range reference by looking up the named range with that name. When A2 changes to "Vegetables", B2's dropdown automatically shows vegetables. Important: The named ranges must exactly match the values in the main category list, including capitalization.

Managing and Auditing Validation Rules

Find All Validated Cells

Home → Find & Select → Data Validation highlights all cells with validation rules in the current sheet.

Choose Data Validation (same) to find cells with the same rule as the currently selected cell.

Copy Validation Without Copying Content

Copy a cell with the validation rule (Ctrl+C)
Select the destination cells
Paste Special (Ctrl+Alt+V) → Validation → OK

Remove Validation

Select the cells → Data → Data Validation → Clear All.

Frequently Asked Questions

Does data validation prevent pasting invalid values?

No. Paste operations bypass validation rules entirely. If users paste data into validated cells, invalid values will be accepted without triggering the error alert. To audit pasted data, use Data → Data Validation → Circle Invalid Data — this draws red circles around cells that currently violate their validation rules.

Can I apply validation to an entire column?

Yes. Click the column header to select the entire column, then apply the rule. However, be aware that this creates a rule on over a million cells. For large workbooks, it's more efficient to select only the range you expect to use (e.g., A2:A10000).

Why does my INDIRECT dependent dropdown stop working when I close and reopen the file?

This is a known issue when the named ranges and source lists are on a different sheet. Make sure the named ranges refer to absolute ranges (e.g., =Lists!$G$2:$G$10) and that the "Lists" sheet is not hidden or deleted. If the source data is in a Table, name the Table column instead.

Can I use data validation in Excel Online?

Yes, with limitations. Basic types (number, list, date, text length) work. Custom formula validation and INDIRECT-based dependent dropdowns may not function reliably in Excel Online or Google Sheets. For shared workbooks used in browsers, stick to simple list validation.

How do I show a red outline on invalid cells without using a popup?

Use the Circle Invalid Data feature: Data → Data Validation → Circle Invalid Data. This is useful for auditing existing data. Remove the circles with Clear Validation Circles in the same menu.