32.1 What Is Data Validation?

Data Validation restricts what a user can enter into a cell. You define a rule; when the user tries to enter something that violates the rule, Excel either alerts them, warns them, or silently accepts it depending on the alert type you choose.

Opening Data Validation

• Select the cell(s) to validate → Data tab → Data Tools group → Data Validation
• — or — Alt+A+V+V (keyboard sequence)
• The Data Validation dialog opens with three tabs: Settings, Input Message, Error Alert

The Three Tabs

32.2 All Validation Types — The Settings Tab

The Allow dropdown on the Settings tab determines the category of validation rule. Each type reveals its own set of criteria options.

Ignore Blank

At the bottom of the Settings tab: Ignore blank — when ticked, blank cells pass validation without triggering an error. Untick if the field is mandatory and must not be left empty.

Apply to All Cells with the Same Settings

Tick "Apply these changes to all other cells with the same settings" to update every cell in the sheet that currently has the same validation rule — useful for updating a rule without reapplying it to each range individually.

32.3 Drop-Down Lists — Creating and Managing

A drop-down list (List validation) is the most commonly used data validation type. Instead of typing, users click the dropdown arrow and select from a predefined list of options — eliminating spelling variations, capitalisation inconsistencies, and invalid entries.

Method 1 — Typed List (Quick but Hard to Maintain)

1. Select the cells for the dropdown
2. Data → Data Validation → Settings tab
3. Allow: List
4. Source: type the options separated by commas (e.g., Gauteng,Western Cape,KwaZulu-Natal,Eastern Cape,Limpopo)
5. Tick In-cell dropdown (ticked by default)
6. Click OK

Method 2 — Range-Based List (Recommended)

1. Type your list values in a column somewhere (e.g., a "Lists" sheet or a separate column): one value per row
2. Select the cells for the dropdown
3. Data → Data Validation → Settings tab → Allow: List
4. Source: click the range selector icon and select the range containing the list values (e.g., =$J$2:$J$11)
5. Click OK

Method 3 — Named Range List (Most Robust)

1. Type the list values in a column → select the range → click the Name Box → type a name (e.g., Provinces) → Enter
2. Data Validation → Source: type =Provinces
3. Named ranges are absolute by nature — the dropdown works correctly even when the formula is copied to other sheets

Drop-Down Lists on a Hidden "Lists" Sheet

32.4 Dynamic Drop-Down Lists — Lists That Grow Automatically

A static range-based list does not include new items added below the named range. A dynamic list automatically expands as you add items.

Method 1 — Excel Table as Source

1. Convert the list values to an Excel Table: select the list → Ctrl+T → OK
2. Name the Table (Table Design tab → Table Name box, e.g., "ProvinceTable")
3. In Data Validation Source: type the formula =ProvinceTable[Province] (using the Table name and column header)
4. New items added to the Table are automatically included in the dropdown

Method 2 — OFFSET Formula for a Dynamic Named Range

Dependent Drop-Downs — Cascading Lists

A dependent dropdown changes its options based on the selection in another cell. Example: selecting a Province in column A changes the available Cities in column B to show only cities in that province.

1. Create named ranges for each group (e.g., a range named "Gauteng" containing Johannesburg, Pretoria, Ekurhuleni; a range named "WesternCape" containing Cape Town, Stellenbosch, Paarl)
2. Critical: named range names must exactly match the values in the first dropdown (e.g., if the Province dropdown says "Gauteng", the named range must also be called "Gauteng")
3. Select the dependent dropdown cells (column B) → Data Validation → List → Source: =INDIRECT(A2)
4. INDIRECT converts the text value in A2 ("Gauteng") into a range reference (the named range "Gauteng") — the cities dropdown now shows only Gauteng cities when A2 = "Gauteng"

32.5 Input Message — Guiding Users Before They Type

An Input Message is a small tooltip that appears automatically when a user clicks a validated cell — before they enter any data. It is the most effective way to communicate instructions, acceptable formats, or examples directly in the spreadsheet.

Setting Up an Input Message

1. Data Validation dialog → Input Message tab
2. Tick "Show input message when cell is selected"
3. Title: a bold heading for the tooltip (e.g., "SA ID Number")
4. Input message: the body text (e.g., "Enter the 13-digit SA ID number. Example: 9001155009087. Do not include spaces or hyphens.")
5. Click OK

Input Message Examples

32.6 Error Alerts — Responding to Invalid Entries

The Error Alert tab controls what happens when a user enters data that violates the validation rule. There are three styles, each with a different level of strictness.

Error Alert Styles

Setting Up an Error Alert

1. Data Validation → Error Alert tab
2. Tick "Show error alert after invalid data is entered"
3. Style: choose Stop, Warning, or Information
4. Title: the heading of the error dialog (e.g., "Invalid SA ID Number")
5. Error message: a clear description of what went wrong and what is expected (e.g., "The SA ID number must be exactly 13 digits. Please check and re-enter.")
6. Click OK

Professional Error Alert Examples

32.7 Custom Formula Validation — Advanced Rules

Custom validation allows any Excel formula that returns TRUE or FALSE. If the formula returns TRUE, the entry is valid; FALSE triggers the error alert. This is the most powerful validation type, enabling cross-cell rules and complex data integrity checks.

Custom Validation Formula Reference Rule

Like Conditional Formatting formulas, the formula is written for the top-left cell of the selected range and applies relatively to all other cells. Use column-locked references ($A2) when the rule depends on a column; use fully absolute references ($A$1) for a fixed reference cell.

Essential Custom Validation Formulas

32.8 Managing, Copying & Removing Data Validation

Copying Validation to Other Cells

• Paste Special: Copy a cell with validation (Ctrl+C) → select the target cells → Ctrl+Alt+V → select Validation → OK. Pastes only the validation rule, not the cell content or formatting.
• Regular paste: Ctrl+C → Ctrl+V copies validation along with content and formatting (may not be what you want if the destination already has data).
• Format Painter: copies validation along with formatting (but not content).

Removing Data Validation

1. Select the cells to clear
2. Data → Data Validation
3. Click Clear All (bottom-left of the dialog) → OK
4. All validation rules, input messages, and error alerts are removed from the selected cells

Finding All Validated Cells

• Home → Find & Select → Go To Special → Data validation
• Choose All to find every validated cell on the sheet, or Same to find only cells with the same rule as the active cell
• All validated cells are selected — you can then remove or update their validation in one step

Circling Invalid Data

Excel can visually identify cells that already contain data violating the current validation rules (data entered before the rule was applied, or rules added after data was entered):

1. Data tab → Data Tools group → Data Validation dropdown arrow → Circle Invalid Data
2. Red circles appear around every cell that currently fails its validation rule
3. After correcting the data: Data Validation dropdown → Clear Validation Circles to remove the circles

32.9 Practical SA Data Validation Scenarios

HR Employee Capture Form

Monthly Timesheet Validation

32.10 Quick Self-Check

Q1: You want a Province dropdown in column C of a data entry form. The provinces are already listed in column A of a hidden "Lists" sheet (A2:A9). Write the Data Validation setup steps and the Source value to use.

✓ Select all cells in the Province column (e.g., C2:C200) → Data → Data Validation → Settings tab → Allow: List → Source: click the range selector → navigate to the Lists sheet → select A2:A9 (the province names). The Source box will show =Lists!$A$2:$A$9. Alternatively, if the province range has been named "Provinces" via the Name Box, type =Provinces in the Source field directly (this works even across sheets and is more portable). Click OK. A dropdown arrow appears in each C cell. For a more robust setup: name the range (select A2:A9 on Lists sheet → Name Box → type Provinces → Enter), hide the Lists sheet (right-click tab → Hide), and protect the workbook structure (Review → Protect Workbook) so users cannot unhide and edit the source list.

Q2: You need a cascading dropdown where selecting a Province in column A changes the available Cities in column B. "Gauteng" should show Johannesburg, Pretoria, Ekurhuleni. "Western Cape" should show Cape Town, Stellenbosch, Paarl. How do you set this up?

✓ Step 1 — Create the named ranges: on the Lists sheet, type the Gauteng cities in one range (e.g., D2:D4) and select them → Name Box → type "Gauteng" → Enter. Type the Western Cape cities in another range (e.g., E2:E4) → Name Box → type "WesternCape" → Enter (no spaces allowed in named range names). Step 2 — Province dropdown: set column A validation to List using the Provinces list as normal. Step 3 — City dependent dropdown: select B2:B200 → Data Validation → List → Source: =INDIRECT(SUBSTITUTE(A2," ","_")). The SUBSTITUTE replaces spaces in "Western Cape" with underscores to match the named range "WesternCape". INDIRECT converts the text string into the corresponding named range. When A2 = "Gauteng", the cities dropdown shows Johannesburg, Pretoria, Ekurhuleni. When A2 = "Western Cape", it shows Cape Town, Stellenbosch, Paarl.

Q3: Write a custom Data Validation formula for column B that prevents duplicate values — each entry in B2:B200 must be unique. What error style should you use?

✓ Select B2:B200 → Data Validation → Custom → Formula: =COUNTIF($B$2:$B$200,B2)=1. The formula uses an absolute range ($B$2:$B$200 stays fixed as it applies to each cell) but a relative cell reference for the criteria (B2 shifts to B3, B4, etc. as the rule applies down the column). COUNTIF counts how many times the current cell's value appears in the entire range. =1 means it appears exactly once — valid. If someone tries to enter a value already in the list, COUNTIF returns 2 (or more), the formula returns FALSE, and the error alert fires. Use a Stop error style (red icon) with the title "Duplicate Entry" and message "This value already exists in the list. Each entry must be unique."

Q4: What are the three Error Alert styles in Data Validation, and when would you use each in an SA payroll context?

✓ Stop (red icon): completely blocks invalid entry; user cannot proceed without entering a valid value or cancelling. Use for fields that must be correct — SA ID numbers (must be 13 digits), employee numbers (must be unique), leave days (must be 1–30). An invalid payroll entry that gets through could cause incorrect PAYE calculations or UIF contributions. Warning (yellow triangle): shows a caution but allows the user to override by clicking Yes. Use for unusual but possible values — a salary above R200,000/month is flagged but valid for senior executives; overtime hours above 8 are warned but valid with prior approval. Information (blue circle): informs the user but always accepts the entry. Use for gentle guidance where overrides are routine — noting that a date is outside the standard reporting period but not necessarily wrong.

Q5: You have a spreadsheet with 500 rows of historical employee data. You now add a Data Validation rule requiring all ID numbers to be 13 characters. Some existing ID numbers are wrong. How do you identify which cells violate the new rule without checking each one manually?

✓ After applying the validation rule to the ID column: Data tab → Data Tools group → click the dropdown arrow on the Data Validation button → Circle Invalid Data. Excel draws a red oval circle around every cell in the validated range that currently contains data violating the rule — including all the pre-existing invalid entries that were entered before the rule was applied. Review each circled cell and correct the data. Once corrected: Data Validation dropdown → Clear Validation Circles to remove all circles. This is the professional approach for bulk data auditing — far faster than manually reviewing 500 rows.

Q6: What is the difference between the List validation type using a typed source ("Gauteng,Western Cape,KwaZulu-Natal") versus a range-based source (=Provinces)? Which is better for an SA HR system and why?

✓ Typed source: the values are stored directly in the Data Validation dialog. To add, remove, or edit any province, you must open the Data Validation dialog for every cell using that list and retype the entire source. If 50 cells use the same list, you update 50 dialogs. Range-based source (=Provinces): the values are stored in a range of cells (usually a dedicated hidden Lists sheet) assigned a named range. To update the list, you change the values in the source range once — all 50 dropdown cells automatically reflect the change. For an SA HR system, range-based is clearly better: (1) Lists can change (job titles, departments, cost centres change with restructuring) — one-place updates are essential; (2) The source list can be protected and hidden so only administrators can modify valid options; (3) The list is visible and auditable without opening dialog boxes; (4) Longer lists (all SA municipalities, all bank names, all job grades) are impractical to type as a comma-separated string.

✓ Module 32 Complete — You Have Learned:

• Data Validation overview — Data tab → Data Validation; 3 dialog tabs (Settings, Input Message, Error Alert); purpose (restrict entries, guide users, prevent errors before they happen)
• All 8 Allow types — Any value, Whole number (criteria: between/equal/greater etc.), Decimal (same + decimals), List (typed or range), Date (with TODAY() references), Time, Text length (exactly 13 for SA ID), Custom (formula-based); Ignore blank toggle; Apply to same settings cells
• Drop-down lists — Method 1 (typed comma-separated, quick but hard to maintain); Method 2 (range-based, recommended: update once, reflects everywhere); Method 3 (named range source = most portable, works across sheets); best practice: hidden Lists sheet with named ranges, protected workbook structure
• Dynamic lists — Excel Table as source (grows automatically); OFFSET+COUNTA formula for dynamic named range; dependent cascading lists with INDIRECT + named ranges (one named range per group, names match first dropdown values exactly, SUBSTITUTE for spaces)
• Input Message — tooltip shown on cell select; Title + body text; guiding before typing; 4 SA examples (ID, salary, date, province)
• Error Alert — Stop (red, blocks completely; for mandatory correct fields), Warning (yellow, allows override; for unusual but possible values), Information (blue, always accepts; for guidance only); Title + message for each; 4 professional SA examples
• Custom formula validation — formula written for top-left cell, applies relatively; 10-formula table (LEN=13, COUNTIF=1 for no duplicates, cross-cell date comparison, email @/. check, phone 10 digits starting 0, AND range, not blank, ISNUMBER, YEAR=current year, COUNTIF on approved list)
• Managing validation — copy via Paste Special → Validation; clear via Clear All; find all validated cells via Go To Special → Data validation; Circle Invalid Data (highlights pre-existing violations with red ovals); Clear Validation Circles
• SA scenarios — HR employee capture form (9 fields with appropriate validation types and error styles); monthly timesheet (hours range, leave type list, date within current month using EOMONTH, max hours custom formula)