Ravindra BagaleCourses & study guides Track your progress

Chapter 34

Data Validation: Complete Guide

By · Free Excel chapter · /excel/ch34-data-validation-complete-guide/

Build rock-solid Excel forms with Data Validation: dropdown lists, whole numbers and decimals, dates, text length, custom formulas, and dependent Region→City lists. The practice workbook already has working rules — try to break them.

Ravindra Bagale’s Tip

After you set a rule, deliberately type something wrong. If Excel stays silent, the rule is on the wrong cells.

About this data. Order and employee forms mix Indian and foreign names with INR prices and 2026 dates. Lists sheet holds Region/City/Status/Dept. Demo only. File: https://ravindrabagale-cards.s3.ap-south-1.amazonaws.com/excel/ch34/ch34-dv-practice.xlsx.
Download practice workbook (xlsx) Practice Task Hub

1. Why data validation matters

Validation is cheaper than cleaning the same column every Monday.

Why: One wrong region spelling breaks pivots and Power Query merges. Forms need guard rails.

What: Data Validation limits what a user can type or pick in a cell — lists, numbers, dates, text length, or a custom formula.

How:

  1. Decide which columns must stay clean (Region, Qty, Date, Email).
  2. Data → Data Validation on the input range.
  3. Set Allow type, Input Message, and Error Alert.
  4. Test with a wrong value — Excel should block or warn.
Before / setupRule or formulaResultMeaning
Region free text “Wesst”List: West/South/NorthDropdown onlyNo more typos

2. Dropdown lists (typed or range)

Keep list sources on a Lists sheet so business users can add items without editing the rule.

Why: Status and department must match a fixed vocabulary.

What: Allow → List. Source can be a comma list ("Open,Closed,Hold") or a sheet range like Lists!$E$2:$E$5.

How:

  1. Select the Status cells (D2:D11).
  2. Data → Data Validation → Allow: List.
  3. Source: select Lists!E2:E5 (or type the commas).
  4. Optional: tick In-cell dropdown. Add Input Message “Pick a status”.
  5. Error Alert Style: Stop (hard block) or Warning.
Before / setupRule or formulaResultMeaning
Status columnList → Lists!$E$2:$E$5Dropdown appearsControlled vocabulary

3. Whole numbers and decimals

Match the rule to the real business limit — do not invent 1–9999 if stock max is 500.

Why: Qty cannot be 3.5 boxes; price cannot be negative.

What: Allow → Whole number or Decimal, then between / greater than / less than with min and max.

How:

  1. Select Qty cells → Data Validation → Whole number → between 1 and 500.
  2. Select Unit price → Decimal → greater than 0.
  3. Write a clear Error Alert: “Qty must be a whole number from 1 to 500”.
  4. Try typing 0 or 12.5 — confirm the alert.
Before / setupRule or formulaResultMeaning
Qty = 12.5Whole number 1–500Excel blocks entryInteger only

4. Dates and text length

Date rules stop “02/30/2026” style chaos when people type by hand.

Why: Orders must stay inside this year; notes must stay short for exports.

What: Allow → Date (between start and end) or Text length (≤ N characters).

How:

  1. Select Order date → Date → between DATE(2026,1,1) and DATE(2026,12,31) — or pick cells with those dates.
  2. Select Notes → Text length → less than or equal → 40.
  3. Test a 2025 date and a 50-character note.
Before / setupRule or formulaResultMeaning
Notes 50 charsText length ≤ 40RejectedKeeps CSV columns tidy

5. Custom formulas (unique, email, phone)

Write the formula as if for the first cell in the range; Excel adjusts rows.

Why: Lists cannot express “must be unique” or “must contain @”.

What: Allow → Custom. Formula must return TRUE for valid cells. Excel evaluates relative to the active cell in the range.

How:

  1. Select Employee ID A2:A9 → Custom → =COUNTIF($A$2:$A$9,A2)=1
  2. Select Email → Custom → =ISNUMBER(SEARCH("@",B2))
  3. Select Phone → Custom → =AND(ISNUMBER(VALUE(D2)),LEN(D2)=10)
  4. Error alerts should say what to fix, not only “Invalid”.
Before / setupRule or formulaResultMeaning
Duplicate E-1001COUNTIF(...)=1Second entry blockedUnique key

6. Dependent dropdowns (Region → City)

Named ranges must match the parent cell text character for character.

Why: City list must change when Region changes.

What: Name ranges for each region (West, South, North). Parent list picks Region; child list uses =INDIRECT(A2).

How:

  1. On Lists sheet, put West cities in B2:B4, South in C2:C4, North in D2:D4.
  2. Formulas → Name Manager → define names West, South, North pointing at those ranges (names must match region text exactly).
  3. Region column: List → Lists!$A$2:$A$4.
  4. City column: List → Source =INDIRECT(A2).
  5. Change Region from West to South — City dropdown updates.
Before / setupRule or formulaResultMeaning
Region WestINDIRECT(A2) → West citiesPune/Mumbai/AhmedabadCascading lists

7. Circle invalid data, copy, and clear

Circling does not fix data — it only shows what still breaks the rule.

Why: Old data was typed before the rule existed.

What: Data Validation → Circle Invalid Data draws red ovals. Clear Validation Circles removes them. Paste Special can copy validation.

How:

  1. On OrderForm, temporarily type a bad Qty (use Warning style if Stop blocks you, or clear the cell rule briefly).
  2. Data → Data Validation dropdown arrow → Circle Invalid Data.
  3. Fix the cells, then Clear Validation Circles.
  4. To remove rules: select range → Data Validation → Clear All.
Before / setupRule or formulaResultMeaning
Legacy bad QtyCircle Invalid DataRed ovals appearAudit before share

8. Design tips for solid forms

Teach users the Input Message — a good prompt cuts support questions.

Why: Validation fails when ranges drift or lists sit on the same sheet users overwrite.

What: Put lists on a Lists sheet, extend named ranges carefully, protect the Lists sheet, and document the Allow type in a header note.

How:

  1. Keep one Lists sheet; hide it if needed.
  2. Use tables or dynamic named ranges when lists grow often.
  3. Protect sheet but allow selecting unlocked input cells.
  4. Pair validation with Conditional Formatting blanks for a second safety net.
Before / setupRule or formulaResultMeaning
Growing city listTable or dynamic nameDropdown stays currentLess maintenance

Practice tasks (12)

  1. On OrderForm row 5, pick Region South and Status Open from the dropdowns.
    Hint

    Use the in-cell arrows.

  2. Try Qty = 0 on row 5 — confirm the whole-number alert.
    Hint

    Rule is between 1 and 500.

  3. Enter Unit price -10 — confirm decimal > 0 blocks it.
    Hint

    Error Alert on column F.

  4. Type a 2025-01-01 order date — confirm year-2026 rule.
    Hint

    Date between Jan–Dec 2026.

  5. Paste a 60-character note into Notes — confirm text length ≤ 40.
    Hint

    Column H rule.

  6. On CustomRules, duplicate E-1001 on a new row — confirm unique rule.
    Hint

    COUNTIF custom formula.

  7. Enter an email without @ — confirm custom email rule.
    Hint

    SEARCH("@",…) formula.

  8. Enter phone 12345 — confirm 10-digit rule.
    Hint

    LEN and VALUE check.

  9. On Dependent, set Region to North and pick Lucknow.
    Hint

    INDIRECT named range North.

  10. Change that row’s Region to West — City must be re-picked from West list.
    Hint

    Old city may no longer be valid.

  11. Circle Invalid Data on OrderForm after planting one bad value, then clear circles.
    Hint

    Data Validation menu arrow.

  12. Add a new city “Surat” under West on Lists and use it in Dependent (extend named range if needed).
    Hint

    Update name West to include the new cell.

Quick quiz

Q1. Data Validation lives on which ribbon tab?

Q2. A dropdown of fixed words uses Allow type…

Q3. Dependent City lists usually use which function in Source?

Q4. To block duplicate IDs you typically use…

Q5. Circle Invalid Data does what?

Q6. Text length ≤ 40 is best for…

Q7. Named range for region West must…

Q8. Error Alert style Stop means…

End-of-chapter checklist

  • I can build List, Whole, Decimal, Date, and Text length validations with clear alerts.
  • I can write Custom formulas for unique IDs and simple email/phone checks.
  • I can build dependent dropdowns with named ranges and INDIRECT.
  • I can Circle Invalid Data and Clear All validation when needed.

Quick revision cards (sample of 3)

Approved Excel card style: standalone, QR to this lesson. Full set can expand at deploy. Cards hosted on S3.

Dropdown lists revision card
Dropdown lists
Numbers dates text length card
Numbers, dates, length
Custom and dependent lists card
Custom & dependent