📊 Lesson 3.2: Data Validation & Dropdowns

Why Validate at the Point of Entry

Picture a form on a clipboard passed around an office. If it just has a blank line labeled "Department," you'll get back "Sales", "sales", "Sales Dept", "Slaes", and one heroic soul who writes "the sales team, third floor." Every one of those means the same thing, but to a spreadsheet they're five different categories. Now imagine the form had five checkboxes instead. Everyone picks one; the data comes back perfectly consistent. That's the entire idea of data validation: make the right entry the only easy entry.

You have two moments to keep data clean: at entry or later. Cleaning later means hunting down every "sales" variant, fixing typos, and re-standardizing dates — tedious, error-prone, and never truly finished because new mess arrives daily. Validating at entry means the mess mostly can't happen in the first place. It's the difference between installing a filter on a tap versus straining every glass of water by hand.

Validation lives under Data ▸ Data validation, where you "Add a rule," pick the range it applies to, and choose what kind of rule it is. Everything in this lesson starts there. Because it protects the Cells layer of our Cells → Formulas → Views model, good validation makes every layer above it more trustworthy.

Creating a Dropdown

A dropdown turns a cell into a menu: click it, pick from the offered options, done. It's the single most useful validation rule because it eliminates typos and spelling drift entirely. There are two ways to supply the options.

From a list of items you type

Select the column of cells you want to control (say the whole Category column), open Data ▸ Data validation ▸ Add rule, and choose "Dropdown". Type your options one per line: Groceries, Dining, Transport, and so on. Great for a short, stable set of choices you can hold in your head.

From a range on the sheet

Instead of typing, choose "Dropdown (from a range)" and point it at a range like Lists!A2:A10 — a column of options living elsewhere. Now the dropdown mirrors whatever is in that range: add "Health" to the range and it instantly appears in every dropdown, no rule editing needed. This is the professional approach for lists that grow or change, and it sets up the helper-tab pattern in Section 5.

Single vs multi-select

By default a dropdown holds one value per cell — pick "Dining" and that replaces whatever was there. Newer Sheets also offers multi-select, letting a single cell hold several chips at once (handy for tags like "urgent, finance"). Multi-select is powerful but be cautious: a cell with "urgent, finance" is a single text value to formulas, so it won't group cleanly in a pivot the way a single-value column does. For data you'll analyze, prefer one value per cell; save multi-select for informal tagging.

⚠️ Important Note: Dropdowns guide new entries, but they don't retroactively fix data already in the cells. If your Category column already contains "sales" and "Sales Dept", adding a dropdown won't rewrite them — it'll just flag them as invalid (a little warning corner) and offer the clean options going forward. Clean the existing values once, then let the dropdown keep them clean.

Other Validation Rules

Dropdowns are the star, but validation covers far more than lists. The same Data ▸ Data validation dialog offers rules for numbers, dates, text, and checkboxes — and even a custom formula for anything else.

Number ranges and dates

These are your everyday guardrails. A number-range rule on Amount ("greater than 0" or "between 0 and 10000") catches a fat-fingered negative or an accidental extra zero. A date rule ("is a valid date") rejects Sept 5th-ish and insists on a real, sortable date — which matters enormously, because Sheets can only sort and calculate with dates it recognizes as dates.

Checkboxes

Choose the Checkbox rule and the cell becomes a tick box that stores TRUE when checked and FALSE when not. Those are real values you can count: =COUNTIF(E2:E17, TRUE) tells you how many rows are marked paid. Checkboxes are validation and a friendly input control in one.

Custom formula validation (brief)

When no built-in rule fits, a custom formula lets you write your own test. The rule accepts the entry only when your formula returns TRUE for that cell. For example, to force whole numbers in cell A2 you'd use a formula like =A2=INT(A2). You'll get comfortable writing formulas over the next module; for now, just know this escape hatch exists for the rare case the menus don't cover.

Reject vs Warn, Help Text & Removing Rules

Every validation rule has to decide what happens when someone breaks it. That's the reject vs warn choice, and picking the right one is about how strict the data needs to be.

• Reject the input — a bad entry is refused outright and the cell keeps its previous value, with a message explaining why. Use this when clean data is non-negotiable, like a Category that must match your pivot exactly.
• Show a warning — the entry is allowed but the cell gets a red flag in the corner and a note on hover. Use this when you want to nudge without blocking, or when occasional exceptions are legitimate.

Help text

Every rule lets you add help text — a short message shown when someone selects the cell or trips the rule. "Choose a category from the list" or "Enter an amount between 0 and 10000" turns a confusing rejection into helpful guidance. Always write help text for rules other people will use; it's the difference between a guardrail and a mystery.

Removing or editing validation

Rules aren't permanent. Open Data ▸ Data validation, and you'll see every rule on the sheet listed. Click one to edit its range, options, or reject/warn setting; click Remove rule (the trash icon) to delete it. There's also a "Remove all" option if you want to clear the slate. Editing a rule updates every cell it covers at once.

Dropdowns + a Helper "Lists" Tab

When your dropdown options live "from a range," where should that range be? The professional habit is a dedicated helper tab — call it Lists — that holds nothing but your option columns: Categories in column A, Statuses in column B, and so on. Your data tab stays clean; the machinery lives out of the way.

Why a separate tab beats stuffing lists into spare cells on your data sheet:

• Nothing gets sorted or deleted by accident. Options tucked next to your data can get swept up in a sort or overwritten; on their own tab they're safe.
• One place to update. Add "Health" to Lists!A once, and every dropdown pointing there updates instantly — no rule editing.
• It scales. As your workbook grows, a tidy Lists tab keeps all your controlled vocabularies in one obvious spot.

Point a dropdown-from-a-range rule at, say, Lists!A2:A20, leaving room to grow. A small caution: if the range is exactly the current items, a newly added option below it won't be picked up until you extend the range — so give it a little headroom, or use a whole-column reference like Lists!A2:A.

⚠️ Important Note: Keep your Lists tab's options spelled and cased exactly how you want them stored — because a dropdown copies the option's text verbatim into the data cell. If the list says groceries lowercase, every entry will be lowercase. Decide your canonical spelling once, put it on the Lists tab, and every dropdown enforces it for free.

🎯 Project: Dropdowns & a Rejection Rule

You'll upgrade the expense dataset from last lesson (or a fresh copy) with real guardrails: two dropdowns fed from a helper tab, a number rule that rejects impossible amounts, and a date rule that insists on genuine dates. Then you'll deliberately break each rule to see the chips and the rejection message do their job.

Lists tab
        A            B
1   Category      Status
2   Groceries     Pending
3   Dining        Cleared
4   Transport     Refunded
5   Utilities
6   Shopping
7   Health        (added in Step 6)

Dropdown ranges (from a range):
  Category column  ->  Lists!A2:A
  Status column    ->  Lists!B2:B

Bonus custom-formula rule (whole-number amounts only).
A cell holds ONE validation rule, so to keep the 0–10000
check, combine both tests in one custom formula:
  =AND(D2>=0, D2<=10000, D2=INT(D2))
D2=INT(D2) is TRUE only when the amount has no decimals,
so every amount with cents gets flagged. Try it on a
copy of the Amount column first.

Best Practices for Data Validation

✅ Do's

• Validate the columns that feed analysis — categories, statuses, dates, amounts — before you rely on them.
• Keep option lists on a dedicated Lists tab and reference them "from a range."
• Write help text so a rejection guides rather than frustrates.
• Prefer one value per cell for anything you'll sort, filter, or pivot.

❌ Don'ts

• Don't rely on "warn" when consistency matters — a warned cell still holds the messy value.
• Don't assume validation fixes existing data — clean current values once, then let the rule keep them clean.
• Don't hard-code long option lists into the rule if they'll change; a range is easier to maintain.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

Your sheet now defends itself. Categories and statuses come from tidy dropdowns, amounts and dates are forced to be sensible, and your option lists live safely on their own tab. You've moved from arranging data (last lesson) to protecting it — the foundation that makes everything in the analysis modules trustworthy.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 3.3: Named Ranges & Organizing Large Sheets — we give your ranges plain-English names (so Lists!A2:A becomes CategoryList), rewrite a formula to read clearly, and reorganize a workbook into clean Data / Lists / Summary tabs. It's the natural next step from the helper-tab habit you just built.

✅ Before the Next Lesson

• Keep your validated expense sheet with its Lists tab — you'll name ranges on it next
• Make sure at least one dropdown is built "from a range" (not just typed options)
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Create a dropdown list & data validation (Google Support)
• Google Sheets Help Center — full topic list
• sheets.google.com — open the app