Data Validation

Who this is for

This lesson is for aspiring and current Data Analysts who use spreadsheets to collect, clean, or standardize data. If you build input templates, KPI trackers, or hand off sheets to others, strong data validation prevents messy data and saves hours.

Prerequisites

• Comfort entering/editing data in spreadsheets (Excel or Google Sheets).
• Basic formulas (e.g., SUM, COUNT, TODAY).
• Basic understanding of ranges and named ranges.

Why this matters

Real analyst tasks that rely on data validation:

• Building intake forms so sales or ops teams select valid options (e.g., region, product tier).
• Protecting dashboards from broken metrics by blocking out-of-range values (e.g., conversion rates 0–100%).
• Cleaning CSV imports by flagging bad dates or unexpected categories before analysis.
• Maintaining unique keys (e.g., invoice IDs) to prevent duplicate records.

Data validation is a small investment that prevents rework, broken pivots, and incorrect insight.

Concept explained simply

Data validation sets rules for what can go into a cell or range. When someone types or pastes data, the spreadsheet checks the rules. If the value passes, it stays; if not, the user gets a warning or the value is rejected.

Mental model

Think of your sheet like a form with gates. Each gate (rule) allows only values that fit your analysis. Gates should be predictable, visible (with messages), and as strict as needed to protect downstream reports.

Core validation types you will use

• List (dropdown): Allow only items from a list or range.
• Number: Whole or decimal; between, greater than, less than, equal to.
• Date/Time: In a range (e.g., not in the future).
• Text length: Min/max length (e.g., ID codes).
• Custom formula: Your own logical test returning TRUE/FALSE (e.g., unique values, pattern checks).

Error behavior options typically include rejecting invalid data or allowing it with a warning. Add input messages to guide users before they type.

Worked examples

Example 1 — Dropdown for Department

Goal: In column A, allow only Departments from a clean list on a Lists sheet (A2:A10).

1. Select the target input range (e.g., A2:A1000).
2. Open Data Validation, choose List/From a range.
3. Set the source to Lists!A2:A10 (or a named range like Departments).
4. Add an input message: "Choose a department from the list."
5. Set behavior: Reject invalid input.

Result: Only listed departments can be entered.

Example 2 — Dependent dropdown (Category → Subcategory)

Goal: B2 should show subcategories based on the category in A2.

1. Create named ranges for each category. Example on Lists:
   • A2: "Hardware", A3:A? list of hardware items; name this range Hardware.
   • B2: "Software", B3:B? list of software items; name this range Software.
2. In A2:A, apply a list validation using the master category list.
3. In B2:B, use a list validation whose source references the category name via INDIRECT of A2.
   • Source formula idea: INDIRECT(A2)

Tip: Named ranges must match the text in A2 exactly. If categories include spaces, use underscores in both the category text and the named range name.

Example 3 — Score must be 0–100

Goal: In C2:C, allow only numbers from 0 to 100 inclusive.

1. Select C2:C.
2. Choose a Number validation with Between 0 and 100.
3. Input message: "Enter a score from 0 to 100."
4. Reject invalid input.

Example 4 — Date cannot be in the future

Goal: D2:D must be today or earlier.

1. Select D2:D.
2. Use a Date validation with an end date of TODAY(), or a Custom formula: =D2<=TODAY().
3. Warn users with an input message: "Use a date up to today."
4. Reject invalid input.

Implementation steps you can reuse

1. Separate lists: Keep allowed values on a Lists sheet (one column per list). Name important ranges.
2. Apply rules by column: Select entire columns or large ranges to future-proof new rows.
3. Add messages: Write short input messages and clear error texts.
4. Decide strictness: Use Reject for critical fields; use Warning when there are rare exceptions.
5. Test by pasting: Try invalid paste to ensure rules still hold.

Common mistakes and self-check

• Typed lists inside validation: Hard to maintain. Self-check: Are sources pointing to ranges instead of comma-typed lists?
• Forgetting new rows: Validation only on existing cells. Self-check: Insert a new row and see if validation persists.
• Spaces/case in dependent lists: INDIRECT fails if names mismatch. Self-check: Compare cell text to the named range name exactly.
• Allowing warnings on critical fields: Results slip through. Self-check: Try entering bad data—does it get blocked?
• No guidance text: Users guess. Self-check: Is there an input message for each key field?

Exercises (hands-on)

Mirror of the Exercises panel below. Do them in a blank workbook with a Lists sheet and a Data Entry sheet.

• Exercise 1: Build a Category dropdown and a dependent Subcategory dropdown using named ranges and INDIRECT.
• Exercise 2: Apply number, date, and uniqueness validations to protect a KPI input area.

Checklist before you finish

• All dropdowns pull from range-based lists.
• Critical fields reject invalid data.
• Input messages exist for key fields.
• Dependent dropdown works when you change the parent.
• Invalid pastes are blocked.

Mini challenge

Design a 5-column input form: Date, Region, Product, Units, Owner Email. Requirements:

• Date: must be this month and not in the future.
• Region: dropdown from a list of 5 regions.
• Product: dependent dropdown based on Region.
• Units: whole numbers between 1 and 10,000.
• Owner Email: must contain "@" and end with your company domain (use a custom formula).

Hints

• This month check: first day is EOMONTH(TODAY(),-1)+1; last day is EOMONTH(TODAY(),0).
• Email domain check idea: AND(ISNUMBER(SEARCH("@",E2)), RIGHT(E2, LEN("example.com"))="example.com").

Practical projects

• Team Intake Sheet: Create a clean intake template with 6 validated fields and helpful messages. Share with a colleague for user testing.
• CSV Clean Room: Import a messy CSV into a staging sheet. Add validations to detect issues (bad dates, new categories). Log rejections in a notes column.
• Sales Ops Uploader: Build a sheet that blocks duplicate Deal IDs and restricts Stage and Owner fields to valid lists before export.

Learning path

• Start: Data Validation (this lesson).
• Next: Lookup functions (VLOOKUP/XLOOKUP/INDEX-MATCH) to map IDs to names.
• Then: Conditional formatting to visually flag invalid or unusual values.
• Finally: Pivot tables and charts using the cleaned, validated data.

Next steps

• Apply validation to one live sheet you maintain today.
• Add input messages and consistent error texts.
• Document your Lists sheet and naming conventions for teammates.

About the Quick Test

The Quick Test is available to everyone. If you log in, your progress will be saved automatically.

FAQ

Should I reject or warn for invalid inputs?

Reject on critical fields (IDs, categories, dates driving metrics). Use warnings where occasional exceptions are acceptable.

How do I handle new categories added later?

Keep lists in a separate sheet and point validations to ranges that include future rows. Consider converting lists into dynamic named ranges or tables so the source expands as you add items.