Data Tools & Integrity

Learning objectives

By the end you can:

1. Build data validation of every practical kind (list/dropdown, whole-number, decimal, date, text-length, and custom-formula) and attach input prompts and error alerts, choosing deliberately between a Stop, Warning, and Information alert style.
2. Write a custom-formula validation rule that returns TRUE/FALSE, understanding that it is evaluated relative to the active cell of the selection, and use it to enforce structural rules such as "a GSTIN must be exactly 15 characters" (=LEN(A2)=15) or "no duplicate entries".
3. Build formula-driven conditional formatting that highlights whole rows on a condition, anchoring the rule's references correctly with mixed $ locks so it evaluates against the right cell as it fills across a range.
4. Use the built-in conditional-format rules (highlight duplicates, color scales, and data bars) and state precisely when not to use them, because a colour gradient can bury the very signal you needed to see.
5. Perform a multi-level sort and use both AutoFilter and the FILTER function, and articulate when a live spilling FILTER beats a manual AutoFilter and vice versa.
6. Use Remove Duplicates safely (always on a copy, and always after consciously choosing which columns define a duplicate) and explain why the wrong column selection silently destroys good rows.
7. Split a mashed column with Text to Columns in both delimited and fixed-width modes, use it to coerce text-numbers and mis-parsed dates, and reshape patterned data with Flash Fill (Ctrl+E).
8. Protect a sheet and workbook correctly, understanding that a cell's Locked property only bites once the sheet is protected, and therefore that you must unlock input cells first, then protect, never the reverse.
9. Assemble all of the above into an error-proofed input form that resists both mistyped and pasted bad data, and diagnose the five classic integrity failures this module warns against.

Prerequisites & connections

Builds on. EX0.01 (Interface & Data Entry, the Table discipline with Ctrl+T, and the habit of one-fact-per-cell that makes validation and de-duplication even possible; you cannot de-duplicate a column that has two facts jammed into each cell). EX0.02 (Formulas I, relative, absolute and mixed references and the F4 toggle, which is the single most load-bearing idea in formula-driven conditional formatting, because a rule anchored =$D2>100000 behaves completely differently from =D2>100000 or =$D$2>100000). EX1.01 (Formulas II: COUNTIF/COUNTIFS, LEN, ISNUMBER, TRIM, and the text functions you will pour into custom validation and CF rules). EX1.02 (Lookups, a validation dropdown often points at a lookup list, and CF rules frequently call COUNTIF against a reference range).

Feeds forward. EX2.02 (What-If & Sensitivity) protects the input cells of a Data Table exactly the way this module teaches. EX2.03 and the PivotTable nodes assume a clean, de-duplicated, correctly-typed source table, the output of everything here. On the analyst track, integrity is not optional polish: M2.xx's financial models rely on assumption cells that are validated and locked so a reviewer cannot fat-finger a growth rate; the entire "trusted workbook" property (EX7.01) rests on the layered defence you build in this node. When you later hand a model to a client or a manager, the difference between "they trust it" and "they quietly rebuild it" is very often whether the input cells fought back when they tried to break them, which is precisely this material.

Data Validation: gates on your inputs

First principles. A spreadsheet cell is, by default, radically permissive: it will accept the number 42, the word banana, a date, a formula, or a paragraph, with equal happiness. Data Validation revokes that permissiveness for chosen cells. It installs a gate that inspects each typed entry against a rule and, if the entry fails, either refuses it outright or warns about it. The mental model is a bouncer at a door: before a value is allowed to become the cell's content, the bouncer checks it against the guest list.

You reach the gate through Data ▸ Data Validation (keyboard: Alt, A, V, V). The dialog has three tabs, and mastery means using all three, not just the first.