MO-211 Expert Prep
Module 2 · Lesson 2.5

Data Validation

Edit on GitHub

In SQL, a CHECK constraint prevents invalid values from entering a table column. In Excel, Data Validation does the same thing — enforcing rules about what can be typed into a cell, with custom error messages when violations occur. It is the primary tool for building controlled, error-resistant data entry forms inside Excel.

Part 1: Opening the Data Validation Dialog

Data tab > Data Tools group > Data Validation > Data Validation...

The dialog has three tabs: Settings, Input Message, and Error Alert.

Part 2: Validation Types (Settings Tab)

The Allow dropdown controls what type of data is permitted:

Allow Use Case Additional Options
Any value No restriction (default) —
Whole number Integers only Between, Not between, Equal to, etc.
Decimal Numbers including decimals Between, Not between, Equal to, etc.
List Dropdown from a range or typed values Source: range or comma-separated values
Date Date values within a range Between, before, after, equal to, etc.
Time Time values within a range Between, before, after, equal to, etc.
Text length Restrict number of characters Between, equal to, less than, etc.
Custom Any formula that returns TRUE/FALSE Formula field

Part 3: Dropdown Lists

Lists are the most common validation type in professional workbooks.

Option A — From a cell range: In the Source field, enter a range reference: =$F$2:$F$6 or a named range like =DeptList.

Option B — Inline values (comma-separated): In the Source field, type: North,South,East,West

TIP
Use a **named range** as the dropdown source. This way, when items are added or removed from the source list, the validation updates automatically without touching the validation settings.

The In-cell dropdown checkbox (checked by default) displays the dropdown arrow in the cell. Uncheck it to validate silently without showing a picker.

Part 4: Input Message Tab

The Input Message appears as a tooltip when the user selects the validated cell — before they enter anything. Use it to provide instructions.

  • Title: Bold header of the tooltip (e.g., "Select a Region")
  • Input message: Body text (e.g., "Choose from the approved list of sales regions.")

Check Show input message when cell is selected to enable it.

Part 5: Error Alert Tab

The Error Alert fires when a user enters data that violates the rule.

Style Icon Behavior
Stop Red X Blocks the entry entirely. The user must correct it or cancel.
Warning Yellow triangle Warns the user but allows them to proceed anyway.
Information Blue i Informs the user but always allows the entry.
IMPORTANT
If the Error Alert style is set to **Warning** or **Information**, invalid data CAN still be entered. Only **Stop** enforces the rule absolutely. For exam questions about "preventing" invalid data, the answer is always Stop.

You can customize the Title and Error message text for all three styles.

Part 6: Circle Invalid Data

If data already exists in cells before validation rules were applied (or validation was bypassed), Excel can visually flag those violations:

Data tab > Data Validation dropdown arrow > Circle Invalid Data

Red ovals appear around all cells that violate the current validation rule. To remove the circles: Data tab > Data Validation > Clear Validation Circles.

Part 7: Custom Formula Validation

The Custom allow type accepts any formula that evaluates to TRUE (valid) or FALSE (invalid).

Goal Formula (applied to cell A2)
Only positive numbers =A2>0
No spaces in text =ISERROR(FIND(" ",A2))
Unique values only (no duplicates) =COUNTIF($A$2:$A$100,A2)=1
Must start with "INV-" =LEFT(A2,4)="INV-"
Date must be in current year =YEAR(A2)=YEAR(TODAY())
IMPORTANT
In Custom formula validation, the formula reference is relative to the **top-left cell** of the selected range. If you apply validation to `A2:A100`, write the formula as if it is evaluated for `A2`.

Part 8: Dependent (Cascading) Dropdowns

A dependent dropdown (also called a cascading dropdown) is a list whose options change based on the value chosen in a different cell. The classic exam scenario: a user picks a Country in column A, and the City dropdown in column B shows only the cities that belong to that country.

The mechanism is two named ranges and an INDIRECT() reference.

Step 1 — Build the Source Lists

Lay out the parent values (Country) in one row and the child values (Cities) below each parent in a separate area of the worksheet, e.g.:

USA France Germany
New York Paris Berlin
Chicago Lyon Munich
Los Angeles Marseille Hamburg

Step 2 — Name Each Child Range Using the Parent Value

Each child column needs to be a named range whose name is exactly the parent value.

  1. Select the cells that contain New York, Chicago, Los Angeles.
  2. Formulas tab > Defined Names group > Define Name.
  3. Type USA in the Name field. Confirm the Refers to range. Click OK.
  4. Repeat for France and Germany.

You should end up with three named ranges: USA, France, Germany.

Step 3 — Parent Dropdown (Country)

In the Country cell (e.g., A2): - Data > Data Validation > Allow: List - Source: the row of country names — for example =$D$1:$F$1.

Step 4 — Dependent Dropdown (City)

In the City cell (e.g., B2): - Data > Data Validation > Allow: List - Source: =INDIRECT(A2)

When the user picks France in A2, INDIRECT("France") returns the named range France, and the dropdown in B2 shows Paris, Lyon, Marseille.

The Spaces Pitfall

WARNING
Named ranges **cannot contain spaces**. If a parent value is "United States" or "South America," `INDIRECT("United States")` will fail because no name with that text exists. Two workarounds: - **Replace the space with an underscore in the name** — name the range `United_States`, then build the dropdown source as `=INDIRECT(SUBSTITUTE(A2," ","_"))`. - **Use a shorter parent label** that has no space — e.g., `USA` instead of `United States`.

Cleaning Up Stale Choices

When a user changes the Country, the City cell still contains its previous value — Excel does not auto-clear it. To handle that, either add a custom validation rule on the city, or rely on user discipline / a macro. The exam does not require auto-clear; it tests that you can build the cascading link.

Part 9: Clearing and Auditing Data Validation

Once validation rules are in place across a workbook, two questions come up frequently: "Where are all the validated cells?" and "How do I remove these rules cleanly?"

Clearing All Validation from a Selection

  1. Select the cells (or entire columns) you want to free of validation rules.
  2. Data tab > Data Tools group > Data Validation > Data Validation...
  3. In the dialog, click Clear All (bottom-left corner).
  4. Click OK.

This removes the rule, the input message, and the error alert from every cell in the selection in one step. It does not delete the data already entered.

TIP
To clear validation from the **entire worksheet**, click the corner triangle (above row 1, left of column A) to select all cells, then run the Clear All workflow.

Finding Every Cell with Validation

Excel does not visually mark validated cells. To locate them:

Home tab > Editing group > Find & Select > Data Validation.

Excel highlights every cell in the active sheet that has any validation rule. This is the audit shortcut — the equivalent of asking, "Where did the previous author put validation rules?"

You can scope the search: - Find & Select > Go To Special > Data Validation opens a dialog that lets you choose All (every validated cell) or Same (cells matching the rule of the active cell).

Circle Invalid Data

If validation rules were added after data was already entered — or if a Warning/Information style allowed bad data through — Excel can visually mark the violations:

Data tab > Data Validation dropdown arrow > Circle Invalid Data.

Red ovals appear around every cell whose current contents fail the rule.

To remove the circles: Data > Data Validation > Clear Validation Circles.

IMPORTANT
Circles disappear automatically when: - The cell value is corrected to satisfy the rule, **or** - The workbook is saved and reopened (circles are display-only and do not persist).

Official Resources