Topic 09
Data Validation
Data Validation lets you control what users can enter into a cell — before they type it. It supports seven input types, guides users with messages, and enforces rules with customisable error alerts. It also has two important limitations that every Excel user should know.
The Seven Validation Types
All types are found in the Settings tab of the Data Validation dialog under the Allow dropdown. The most powerful — and most flexible — is Custom.
| Type | What it restricts | Typical use |
|---|---|---|
| Whole Number | Integers only — within a range, greater than, less than, etc. | Quantity, age, headcount |
| Decimal | Any number including decimals — with the same comparison options. | Price, weight, percentage |
| List | Only values from a defined set — shown as a dropdown arrow in the cell. | Status, department, category |
| Date | Date values within a range or relative to today. | Hire date, deadline, event date |
| Time | Time values within a range — useful for shift scheduling. | Start time, end time |
| Text Length | Restricts the number of characters entered. | Codes, phone numbers, short labels |
| Custom ✦ | Any formula that returns TRUE or FALSE. Entry is accepted when the formula returns TRUE. | Workdays only, unique values, conditional rules — see advanced examples below |
List type — two ways to define the options
Range reference:
=$A$1:$A$5 — dynamic, add items to the range and the dropdown updates.
Typed directly: Small,Medium,Large — static, comma-separated, no spaces. Use the range approach for anything that might change.
Input Message — Guide Before They Type
The Input Message tab lets you define a tooltip that appears whenever the validated cell is selected. It appears before the user starts typing — before any error can occur.
Input Message tab — what you fill in
Title
Date Required
Input Message
Enter a date between 01/01/2024 and 31/12/2024.
What the user sees
Date Required
Enter a date between 01/01/2024 and 31/12/2024.
- The tooltip appears automatically when the cell is clicked — no action needed from the user.
- Title is optional but helps distinguish multiple validated cells on the same sheet.
- Uncheck "Show input message when cell is selected" to disable it without deleting the text.
Error Alert — Three Styles
The Error Alert tab controls what happens when invalid data is entered. The three styles differ in how strictly they enforce the rule.
Stop
Completely blocks the entry. The user must retype a valid value or press Escape to cancel. The invalid value is never accepted.
Blocks entry
Warning
Shows a message and asks "Do you want to continue?" The user can click Yes to proceed with the invalid value anyway.
Can override
Information
Shows a message for awareness only. The user clicks OK and the entry is always accepted regardless of validity.
Always accepts
Choose Stop for data integrity
For shared workbooks or data that feeds into reports, always use Stop. Warning and Information give users an easy way around the rule — which defeats the purpose in most professional scenarios.
Two Important Limitations
Data Validation is powerful but not airtight. Two gaps are worth knowing before you rely on it for critical data.
1
Drawback
It cannot control existing values
Applying validation to a cell that already contains data does not remove, flag, or change that data. The rule only applies to new entries made after the rule is set. Any pre-existing invalid values sit quietly in the cell — no error, no highlight.
To find pre-existing violations: use Data → Data Validation → Circle Invalid Data. Excel draws red circles around cells that violate the current rule, even if the data was there before the rule was applied.
2
Drawback
It cannot control pasted values
When a user pastes data into a validated cell, the validation rule is bypassed. Even with a Stop error alert, a paste operation will overwrite the cell with whatever was copied — invalid or not. The rule is simply not triggered by paste.
Partial workaround: protect the sheet so users cannot paste into validated ranges. A fully locked solution requires VBA to intercept paste events — beyond standard validation.
Advanced Custom Rules
Both examples use the Custom type — a formula that returns TRUE to accept the entry, or FALSE to reject it.
Advanced 01
Allow Only Workdays (Sunday – Thursday)
A date cell should only accept working days. Entering a Saturday or Sunday should be blocked. The
WEEKDAY function converts a date into a day-of-week number — the mode 2 makes Monday = 1 and Sunday = 7.
WEEKDAY default mode — the number map
Sun
1
Mon
2
Tue
3
Wed
4
Thu
5
Fri
6
Sat
7
Green 1–5 = Sun–Thu, workdays (accepted) · Red 6–7 = Fri–Sat, weekend (rejected)
The formula
= WEEKDAY(A1) <= 5
→ TRUE for Sun–Thu, FALSE for Fri–Sat
How to apply it
1
Select the date cell(s) to restrict.
2
Data → Data Validation → Settings tab → Allow: Custom
3
In the Formula box:
=WEEKDAY(A1)<=5 (use the first cell of your selection — it adjusts for the rest).4
Error Alert tab → Style: Stop → Title: Weekday Required → Message: Please enter a date that falls on Sunday through Thursday.
Why no second argument needed for Qatar
The default WEEKDAY mode maps Sunday=1 through Saturday=7. In Qatar the working week is Sunday–Thursday (1–5) and the weekend is Friday–Saturday (6–7). The
<=5 condition lines up perfectly with the default numbering — no mode argument needed.Advanced 02
Allow Only Unique Values
Prevent duplicate entries in a column. The rule uses
COUNTIF to count how many times the entered value already exists in the range. If the count is more than 1 (meaning the value exists elsewhere too), the entry is rejected.
The formula
= COUNTIF($A$1:$A$100, A1) = 1
→ TRUE when this is the only occurrence
Why = 1 and not = 0?
When validation evaluates the formula, the value being entered is already counted in the range. So a brand-new unique entry returns
1 (only this cell has it). A duplicate returns 2 or more. That's why the condition is = 1, not = 0.
How to apply it
1
Select the entire column range you want to protect — e.g.
A1:A100.2
Data → Data Validation → Settings → Allow: Custom
3
Formula:
=COUNTIF($A$1:$A$100,A1)=1 — the range is absolute (locked with $), the cell reference A1 is relative so it adjusts for each row.4
Error Alert → Stop → Title: Duplicate Value → Message: This value already exists. Each entry must be unique.
The $ pattern — same as before
$A$1:$A$100 is locked so the range never shifts as the formula is applied to each row. A1 is relative so each row checks itself. This is the same mixed/absolute reference logic from Topic 01 — now being applied inside a validation formula.