# Data Validation in Excel: Rules, Dropdowns & Custom Formulas
> Master Excel data validation: create dropdown lists, number rules, date constraints, error alerts, and dependent dropdowns to keep your data clean.

- Web page: https://logicexcel.com/tutorials/data-validation
- Language: en
- Also in: [Deutsch](https://logicexcel.com/de/tutorials/data-validation.md), [Español](https://logicexcel.com/es/tutorials/data-validation.md), [Français](https://logicexcel.com/fr/tutorials/data-validation.md)

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

1. Type your list values somewhere (preferably on a dedicated "Lists" sheet)
2. Select them and go to **Formulas → Define Name** → give it a name like `StatusList`
3. 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

1. Create your lists. Say you have:
   - Column F: `Fruits, Vegetables` (main categories)
   - Column G: `Apple, Banana, Mango` (fruits)
   - Column H: `Carrot, Broccoli, Spinach` (vegetables)

2. Name each list to match the main category exactly:
   - Select G2:G4 → **Formulas → Define Name** → `Fruits`
   - Select H2:H4 → **Formulas → Define Name** → `Vegetables`

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

4. 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

1. Copy a cell with the validation rule (`Ctrl+C`)
2. Select the destination cells
3. **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.
