Data Tools & Integrity

Excel Mastery Track · Green belt · Node EX2.01 · ~6–8 focused hours · Microsoft 365 on Windows assumed

Every formula you have learned so far assumes something quietly heroic: that the data underneath it is clean. That the state name is spelled the way the lookup expects, that the GSTIN really has fifteen characters, that no order appears twice, that a "date" is a date and not a string wearing a date's clothes. In the real world none of that is true by default. Data arrives from human hands typing into forms, from ERP exports with trailing spaces, from CSV dumps where one column has silently become four, and a single dirty row can make a beautiful model return a confident, catastrophic lie. This module is where you stop reacting to bad data with more clever formulas and start preventing it at the door.

The craft here has two faces. The first is integrity at the point of entry, building gates (data validation), so wrong data cannot get in, and paint (conditional formatting), so suspicious data cannot hide once it is in. The second is repairing and hardening data you have already received, de-duplicating it, splitting mashed-together columns apart, reshaping messy exports with Flash Fill, and then locking the whole thing down so the one clean version you built cannot be casually broken by the next person who opens it. A senior analyst computes correctly, and their workbooks also stay correct after being handed to five other people. That property (durability under other people's hands) is what this node builds.

One framing to carry throughout: validation and protection are a system, not two features. A dropdown that blocks typing but not pasting is a lock on a door with the window open. A conditional format anchored to the wrong cell paints the wrong rows and teaches everyone to ignore the colour. The expert habit is to think in layers of defence, a gate, plus a paint job that flags what slips through, plus a protected sheet that stops the gate being bypassed, and to test each layer by actively trying to defeat it. We build every one of those layers here, on Indian (₹) and US ($) data, and we recompute every number so you can trust the mechanics before you trust yourself.

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. It introduced Tables with Ctrl+T and the habit of one fact per cell, which is what makes validation and de-duplication possible at all. You can't de-duplicate a column that has two facts jammed into each cell. EX0.02, Formulas I, covers relative, absolute and mixed references and the F4 toggle. Mixed references matter more than any other 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, gave you COUNTIF/COUNTIFS, LEN, ISNUMBER, TRIM and the text functions you'll pour into custom validation and CF rules. EX1.02, Lookups, matters because 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 the same way you'll learn to here. EX2.03 and the PivotTable nodes assume a clean, de-duplicated, correctly-typed source table, which is what everything here produces. On the analyst track, integrity isn't optional polish. The financial models in M2.xx rely on assumption cells that are validated and locked, so a reviewer can't fat-finger a growth rate. The whole "trusted workbook" property (EX7.01) rests on the layered defence you build in this node. One day you'll hand a model to a client or a manager. Whether "they trust it" or "they quietly rebuild it" very often comes down to whether the input cells fought back when they tried to break them, and that's what this material teaches.

Core teaching content

Error-proofing an input form

Now put the layers together into one artefact, a form a colleague cannot fill in wrong. The design principle is defence in depth. No single control is trusted to do the whole job, and each one catches what the one before it misses.

Take a vendor-onboarding form with fields for Vendor Name, State, GSTIN and Invoice Amount. Layer it like this:

1. Structure it as a Table (Ctrl+T) so the validated, formatted region grows cleanly as rows are added, and formulas that reference it expand automatically.
2. Gate each input with validation. State gets a List pointing at a named range States. GSTIN gets a Custom rule, =LEN(A2)=15. Invoice Amount gets Decimal ▸ greater than 0. Attach an Input Message to each so the user learns the rule before they type. Use a Stop error alert where the rule is inviolable (state, GSTIN) and a Warning where an unusual-but-legal value is possible, such as a very large invoice.
3. Paint what slips through with conditional formatting. A formula rule =$D2>100000 flags large invoices for a second look. A Duplicate Values rule on the GSTIN column catches a vendor entered twice. A rule that highlights any blank mandatory cell (=A2="") stops half-finished rows.
4. Lock it down. Unlock only the four input columns and leave labels, headings and any calculated columns locked. Then Protect Sheet, letting the user select and sort but not alter structure. Now the protection stops a paste of garbage into a validated cell, and nobody can overwrite the validation rule itself.
5. Audit the existing rows. Run Circle Invalid Data to red-ring anything already there that breaks the new rules, and fix those before you trust the form.

What you end up with is a form where nobody can type a wrong state or enter a mis-length GSTIN. A large invoice is allowed with a warning and also painted for review. A duplicate vendor lights up, and nobody can casually paste over any of the protective machinery. That's integrity by construction, and Worked example 1 builds it number by number.

Common mistakes & how experts think differently

1. Trusting validation to stop a paste when it only stops typing. Everything else on this page leans on this warning. Data Validation inspects entries made by typing or by picking from the dropdown. It says nothing at all about a paste, and a paste can even overwrite the validation rule itself. The beginner builds a beautiful dropdown, watches it reject a typo, and decides the cell is safe. Then someone pastes a block of external data straight over it, and every gate is gone without a single error. The expert never relies on validation alone for a cell that matters. They pair validation with a protected sheet so the validated input cells can't be overwritten. In their head, validation is "guidance for the honest typist" and protection is the "wall that stops the paste." A gate with no wall around it is theatre.

1. Conditional-format rules anchored to the wrong relative cell. A formula CF rule is written for the top-left cell of the selection and relativised across the rest, so the $ locks decide which cell each row actually tests. Write =D2>100000 when you meant =$D2>100000 and the format paints the wrong cells: only column D, never the row. Write =$D$2>100000 and every cell is judged against a single fixed cell. The beginner writes the formula with one cell in mind and never checks what it becomes three rows down. The expert talks it through: "the active cell is A2, and this rule will relativise. Lock the column so the row reads column D, and keep the row relative so each row tests itself." Then they check that row 5 is painted for the right reason, and that it isn't luck.

1. Color scales and data bars that bury the real signal. Gradient formats are for feeling the shape of a large block of numbers. They can't enforce a threshold. Apply a red-to-green color scale to a margin column when the business rule is "flag anything below 20%," and the sub-20% rows show up as a slightly warmer shade among their neighbours. The one line that matters disappears. The beginner reaches for the prettiest built-in. The expert asks "is this exploration or enforcement?" Exploration gets a gradient. Enforcement gets a discrete formula rule with a solid fill (=$E2<0.20 in red), because a threshold deserves a hard, unmissable edge. A gentle gradient lets the eye slide past it.

1. Protecting the sheet before unlocking the input cells. Every cell defaults to Locked, and that property only takes effect once the sheet is protected. The correct sequence is unlock the input cells first, then protect the sheet. Reverse it and your own form freezes solid. The input cells are still locked and protection now enforces it. Nobody can type, you included. The beginner clicks Protect Sheet first because it looks like the obvious "make it safe" button. The expert has turned the two steps into a single reflex (unlock what should be editable, then protect). They also know that "why can't I type in my own sheet?" nearly always comes down to this inversion.

1. Running Remove Duplicates without checking which columns define a duplicate. The column checkboxes carry the whole meaning of Remove Duplicates, and by default all columns are ticked. That means a row counts as a duplicate only if every field matches. Leave it like that when you meant "duplicate by order ID" and true duplicates survive, the ones with the same ID that differ in one stray column. Tick only one column when the others carry distinctions you need, and you destroy rows that really were different. It's also destructive, deleting in place. The beginner selects the range, clicks OK on the defaults and trusts the result. The expert works on a copy. They run a non-destructive Highlight Duplicate Values pass first to see what will die. They tick the full set of identity columns and nothing more. And they sort first, so the surviving first occurrence is the row they actually want to keep.

1. Treating destructive data operations as casual. Some tools change data irreversibly the moment you save and close. Remove Duplicates does. So does Text to Columns, which overwrites the columns to its right. So does a sort applied to a partial selection, which can scramble rows by detaching one column from its neighbours. The expert copies the sheet, or at least the range, before using any destructive tool. They never sort a single column of a table on its own. They sort the whole table, or go through the Sort dialog, which keeps rows intact.

1. Using a Stop alert where a Warning belongs, and the reverse. A Stop on a rule that has legitimate exceptions trains users to switch validation off entirely. A Warning on a rule that must never be broken lets bad data through on a shrug. The expert matches the alert's severity to how inviolable the rule really is. A hard Stop goes on "this can never be valid," and a Warning on "this is unusual, confirm you mean it."

One habit ties all seven together. The expert designs for the workbook's worst future user, not its author. Every gate gets tested by trying to defeat it: paste over it, fill it down wrong, sort it badly. Every layer exists because some other layer can be bypassed. Integrity is a stance you hold all the time, one that assumes the data, and the next pair of hands, are adversarial.

Worked examples

Worked example 1: Vendor-master input form with validation and flags (India)

The task. Build a vendor-onboarding form for an Indian firm with four fields (Vendor Name, State, GSTIN, Invoice Amount in ₹). The form should (a) offer a dropdown of states, (b) block any GSTIN that isn't 15 characters long with the custom rule =LEN(A2)=15, and (c) flag any invoice above ₹1,00,000 with conditional formatting. Then, over a 10-row sample, count how many invoices the flag catches and how many GSTINs the gate would reject. We recompute every count below.

Step 1, the sample data. Here's how it would sit in A2:D11, with headers in row 1 (Vendor, State, GSTIN, InvoiceAmount):

     A                    B              C                    D
1    Vendor               State          GSTIN                InvoiceAmount
2    Ashok Traders        Maharashtra    27AABCU9603R1ZM      85000
3    Bharat Steel         Gujarat        24AAACG1234M1Z5      145000
4    Crescent Textiles    Karnataka      29AABCT3518Q1ZR      62000
5    Deccan Auto          Telangana      36AADCS0472N1Z8      210000
6    Ganesh Chemicals     Tamil Nadu     33AAKCP7654L1Z2      98000
7    Himalaya Foods       Delhi          07AAECH1234F1Z9      175000
8    Indus Packaging      Maharashtra    27AAFCI5678K1ZP      120000
9    Jyoti Electricals    West Bengal    19AAGCJ4321M1Z       45000
10   Konark Cement        Odisha         21AAHCK8765N1Z55     320000
11   Laxmi Pharma         Rajasthan      08AALCL2109P1Z3      105000

Step 2, the state dropdown. Put the allowed states in a helper range and name it States, through Formulas ▸ Define Name or by typing the name into the Name Box. Select B2:B11 and open Data ▸ Data Validation (Alt, A, V, V). Under Settings ▸ Allow: List, set Source to =States. Then tick In-cell dropdown. On the Error Alert tab set Style: Stop, because a state that isn't on the list is never valid. Column B now accepts only real states, picked from an arrow.

Step 3, the GSTIN length gate. Select C2:C11 and go to Data Validation ▸ Settings ▸ Allow: Custom, Formula =LEN(C2)=15. The active cell is C2, so the rule relativises down the column. C3 reads =LEN(C3)=15, and so on. Set Error Alert Style: Stop with the message "GSTIN must be exactly 15 characters." Now a typed 14- or 16-character GSTIN gets refused.

Step 4, audit the GSTINs already there with Circle Invalid Data. The ten rows were typed before the rule existed, so run Data Validation ▸ Circle Invalid Data. Then check the lengths yourself, from first principles, by counting the characters in each GSTIN:

Two GSTINs fail the =LEN=15 rule. Jyoti Electricals has 14 characters and Konark Cement has 16, so Circle Invalid Data draws red ovals around those two cells and no others. Count of GSTINs the gate rejects: 2.

Step 5, the invoice flag with conditional formatting. Select the data body A2:D11, with A2 as the active cell, and go to Home ▸ Conditional Formatting ▸ New Rule ▸ Use a formula. Enter =$D2>100000 and set a solid red fill. The $D locks the column so every cell in a row reads the invoice in column D, and the relative 2 lets each row test its own invoice. Any row over ₹1,00,000 is now painted across all four columns. Check the count against the data. The rule flags when InvoiceAmount > 1,00,000, which is strictly greater, so an invoice of ₹1,00,000 itself would not flag:

Flagged rows: Bharat Steel, Deccan Auto, Himalaya Foods, Indus Packaging, Konark Cement, Laxmi Pharma = 6 of 10. As a sanity figure, the total invoiced across all ten is 85,000 + 1,45,000 + 62,000 + 2,10,000 + 98,000 + 1,75,000 + 1,20,000 + 45,000 + 3,20,000 + 1,05,000 = ₹13,65,000.

Step 6: harden it. Unlock the input cells, A2:D11. Leave the header and the States helper locked. Then go to Review ▸ Protect Sheet and allow select and sort only. Now nobody can get round the dropdown or the length gate by pasting over the cells, because the protected sheet refuses the overwrite. Step back and look at what you've built. There are two independent defences. A gate (validation) refuses bad GSTINs and states at entry. Paint (CF) surfaces the six large invoices for a second look. Both sit behind a wall (protection) that stops either one being pasted away. The gate rejects 2 GSTINs, the paint flags 6 invoices, and the wall makes both stick.

Worked example 2: De-duplicating a sales sheet and flagging thin margins (US)

The task. A US sales extract arrives with duplicate order IDs, and it needs a margin check. First run Remove Duplicates keyed on OrderID and confirm how many rows survive. Then apply a formula-driven conditional format that highlights every order whose margin is below 20%, and count the highlights. Margin = (Revenue − Cost) ÷ Revenue. We recompute both counts below.

Step 1, the raw extract. There are 12 physical rows in A2:D13, with headers OrderID, Product, Revenue, Cost:

     A          B          C           D
1    OrderID    Product    Revenue$    Cost$
2    US-1001    Router     1200        900
3    US-1002    Switch     800         680
4    US-1003    Firewall   1500        1000
5    US-1004    Cable      600         510
6    US-1005    AccessPt   2000        1700
7    US-1006    NIC        950         700
8    US-1007    Modem      1100        990
9    US-1008    Server     1300        900
10   US-1009    Adapter    700         620
11   US-1010    Rack       1800        1300
12   US-1003    Firewall   1500        1000      <- duplicate OrderID (of row 4)
13   US-1007    Modem      1100        990       <- duplicate OrderID (of row 8)

Step 2: Remove Duplicates, keyed on OrderID. Copy the sheet first (right-click the tab ▸ Move or Copy ▸ Create a copy), because the operation is destructive. Select A2:D13 on the copy and open Data ▸ Remove Duplicates (Alt, A, M). In the dialog, untick every column except OrderID. The rule of identity here is "one row per order," whatever the other fields happen to say. Click OK. Rows 12 and 13 repeat the OrderIDs US-1003 and US-1007 that already appear in rows 4 and 8, so they're removed. The first occurrence of each survives.

• Raw rows: 12. Unique OrderIDs remaining: 10. Duplicates removed: 2. (Excel reports "2 duplicate values found and removed; 10 unique values remain.")

Look at what nearly happened. Rows 12 and 13 would still be removed here if we'd left all columns ticked, because they match on every field. But that's luck. If row 12 had carried a different Cost, the all-columns setting would have kept it as "not a duplicate," and the de-dup would have failed without telling you. Ticking only OrderID states the true rule of identity.

Step 3, the margin flag. With 10 unique rows now in A2:D11, you can either add a helper margin column or reference cost and revenue directly in the rule. Select A2:D11 with A2 active, go to Conditional Formatting ▸ New Rule ▸ Use a formula, and enter:

=($C2-$D2)/$C2 < 0.2

The $C and $D lock the columns, and the relative row lets each line test its own margin. A solid red fill marks every order under a 20% margin. Now check each margin from first principles, with margin = (Revenue − Cost) ÷ Revenue:

Flagged (margin < 20%): US-1002, US-1004, US-1005, US-1007, US-1009 = 5 of the 10 unique orders.

Step 4, the "don't use a color scale here" check. Suppose you'd reached for a red-yellow-green color scale on the margin column instead. The five sub-20% orders, at 10.00% to 15.00%, would show up as slightly redder shades in a smooth gradient that also colours the 25% to 33% rows. Your eye couldn't tell you which five breach the 20% line, because the gradient has no edge at 0.20. The discrete formula rule draws that edge hard: five solid-red rows, and no doubt about which ones. That's the "gradient hides the threshold" lesson made concrete.

Step 5, read it. You've run two integrity operations and checked both. Keyed correctly on OrderID, the de-dup turns 12 rows into 10 by removing 2. The threshold flag surfaces the 5 thin-margin orders that need a pricing conversation. As a sanity figure, the blended margin across the ten unique orders is (11,950 − 9,300) ÷ 11,950 = 2,650 ÷ 11,950 = 22.18%. That's healthy on average, and it's the reason the per-order flag matters. The average hides five orders bleeding below the line, and only the discrete rule finds them.

Practice set

Work each problem before you read its solution. Assume ranges are real Tables where that makes sense, and M365 throughout. Where a UI path helps, you'll get it along with its keyboard route.

P1 (guided). A state dropdown. You need to restrict B2:B50 to one of five states (Maharashtra, Gujarat, Karnataka, Tamil Nadu, Delhi), picked from a dropdown, and refuse anything else. Give the full steps and the two settings that matter most.

Solution. Put the five states in a range such as H1:H5 and ideally name it States. Select B2:B50, open Data ▸ Data Validation (Alt, A, V, V), go to Settings ▸ Allow: List, set Source to =States (or the literal Maharashtra,Gujarat,Karnataka,Tamil Nadu,Delhi), and tick In-cell dropdown. Of all the settings, two matter most. (1) Use a range reference instead of a typed list, so adding a state to H updates the dropdown automatically. (2) On the Error Alert tab, set Style: Stop, so a state that isn't listed gets refused and isn't merely questioned. A reference-backed Stop list survives both a growing state list and a careless typist.

P2 (guided). The GSTIN length gate. Write the custom data-validation rule that allows a value in A2:A100 only if it has 15 characters, neither more nor fewer. Then explain why the reference is written A2 and not $A$2.

Solution. Select A2:A100, go to Data Validation ▸ Allow: Custom, and enter Formula =LEN(A2)=15. The reference is relative (A2 and not $A$2) because the rule is written for the active top-left cell and Excel relativises it down the selection. In row 7 it has to read =LEN(A7)=15, testing that cell. Lock it as $A$2 and every cell in the column tests the length of A2 alone. Once A2 held a valid 15-character value, every other cell would be accepted whatever its own length. A relative reference means "each cell checks itself," and that's what a per-cell rule needs.

P3 (guided). Count the invoice flags. Take the Worked example 1 invoice list: 85,000; 1,45,000; 62,000; 2,10,000; 98,000; 1,75,000; 1,20,000; 45,000; 3,20,000; 1,05,000. A conditional format flags any invoice strictly greater than ₹1,00,000. How many rows are flagged, and what CF formula produces a full-row highlight for a selection whose top-left is A2, with invoices in column D?

Solution. The formula is =$D2>100000. Lock the column, keep the row relative, and apply it to the whole-row range with a solid fill. The invoices above 1,00,000 are 1,45,000; 2,10,000; 1,75,000; 1,20,000; 3,20,000; 1,05,000 = 6 rows. The sub-lakh values (85,000; 62,000; 98,000; 45,000) stay unpainted, and none here falls at 1,00,000 itself. Watch the strictly greater boundary. Had the rule been >=, a value of 1,00,000 would flag too, and that matters when a threshold falls on a round number people actually hit.

P4. Choose the duplicate key. A sheet has columns OrderID, Customer, Date, Amount, and you want one row per order. Which columns do you tick in Remove Duplicates, what's the risk of leaving all of them ticked as the default does, and why run it on a copy?

Solution. Tick only OrderID, because "one row per order" means the OrderID alone defines identity. With all columns ticked, the default, a row counts as a duplicate only if every field matches. So two rows with the same OrderID but a one-rupee difference in Amount, or a Date formatted differently, are treated as distinct. Both survive, and the de-dup fails without telling you. Run it on a copy because Remove Duplicates deletes rows in place with no lasting undo. A copy, or a Highlight-Duplicates pass beforehand to preview, is your only safety net.

P5. Count the margin flags. Ten orders have these (Revenue, Cost) pairs: (1200,900), (800,680), (1500,1000), (600,510), (2000,1700), (950,700), (1100,990), (1300,900), (700,620), (1800,1300). A rule highlights margin < 20%, where margin = (Rev − Cost)/Rev. Write the CF formula (top-left A2, revenue in C, cost in D) and give the count.

Solution. The formula is =($C2-$D2)/$C2<0.2. Lock the columns, leave the row relative, put a solid fill on the row range. Work out each margin: 25.00%, 15.00%, 33.33%, 15.00%, 15.00%, 26.32%, 10.00%, 30.77%, 11.43%, 27.78%. The ones below 20% are the 2nd (15%), 4th (15%), 5th (15%), 7th (10%) and 9th (11.43%), so 5 are flagged. In production, guard against dividing by a zero revenue by wrapping the formula with IF($C2=0, FALSE, …). All the revenues here are positive, though, so the bare formula is fine.

P6. Text to Columns, how many columns? A single column holds strings like Ashok Traders,Maharashtra,27AABCU9603R1ZM,85000. You run Text to Columns, Delimited, on the comma. How many columns do you get? Name one setting that would matter if a vendor name itself contained a comma.

Solution. Splitting on the comma gives 4 columns: Vendor, State, GSTIN, Amount. Now suppose a vendor name contained a comma, as in Ashok Traders, Pvt Ltd. The naive split would wrongly produce five columns and shift every field to the right. The setting that saves you is the Text qualifier ("). If the source wraps such fields in quotes ("Ashok Traders, Pvt Ltd",Maharashtra,…), setting the qualifier to " tells Excel to ignore commas inside the quotes. You keep four columns. Without quoting, you'd split on a safer delimiter or clean the source first.

P7. Coerce text-numbers with Text to Columns. An imported column of "amounts" is left-aligned and won't SUM, because the values are text. Describe the fastest fix using Text to Columns, and give one alternative.

Solution. Select the single column, open Data ▸ Text to Columns (Alt, A, E) and choose Delimited. Click through without selecting any delimiter, leave Column data format as General, and click Finish with the same column as the destination. Re-parsing makes Excel read each cell afresh, so the text-numbers turn into real, right-aligned numbers that sum. The same wizard fixes wrong-order dates through Column data format ▸ Date ▸ DMY (or MDY). As an alternative, multiply the range by 1 with paste-special multiply, or wrap the values in =VALUE(...). For a whole column in place, though, Text to Columns is the fastest.

P8. Multi-level sort. You need the sales table sorted by Region (A→Z), then within each region by Salesperson (A→Z), then within that by Date (newest first). Name the tool and give the level order.

Solution. Use Data ▸ Sort (Alt, A, S, S) and make sure My data has headers is ticked. Then build three levels from the top. Level 1 sorts by Region, Order A→Z. Click Add Level for Level 2, Then by Salesperson, A→Z. Click Add Level again for Level 3, Then by Date, Order Newest to Oldest. Levels apply in order. The first is primary, and each later one breaks ties within the one before. Never sort one column on its own with the ribbon A→Z button on a partial selection, because it can detach that column from its row-mates and scramble the data. The Sort dialog keeps rows intact.

P9. FILTER vs AutoFilter. Two colleagues both want "only West-region rows." One is eyeballing the sheet interactively; the other is building a dashboard block that must always show current West rows. Which tool for each, and the key property that decides it?

Solution. The colleague exploring by hand uses AutoFilter (Ctrl+Shift+L). It hides non-matching rows in place and it's temporary. They can change it on a whim as they explore. The dashboard builder uses the FILTER function, =FILTER(A2:D100, C2:C100="West", "none"). It returns a live spilled array of the West rows that recalculates automatically when the source changes, and it leaves the original untouched. What decides it is liveness and who consumes the result. AutoFilter is a manual, in-place view for a human. FILTER is a self-updating extract feeding a formula or report.

P10. Protect an input form in the right order. You have a model with input cells in B2:B10 and formulas everywhere else. List the steps, in order, that make only B2:B10 editable, and say what goes wrong if you protect first.

Solution. (1) Select B2:B10, open Format Cells ▸ Protection (Ctrl+1) and untick Locked. These become the only editable cells. (2) Leave all formula and label cells locked, as they are by default. (3) Go to Review ▸ Protect Sheet (Alt, R, P, S), set the allowed actions, add a password if you want one, and click OK. If you protect first, every cell is still locked by default and the protection now enforces it. So B2:B10 is frozen too and nobody can type, including you. To fix it you'd unprotect, unlock B2:B10, then re-protect. The rule never changes: unlock the input cells, then protect the sheet, and never the reverse.

P11 (timed: 8 min). Error-proof a form end to end. Design the validation and protection for a two-field form: a Segment cell that must be Gold/Silver/Bronze, and an AnnualSpend cell that must be a positive whole number. Then explain how you stop a user pasting an invalid segment over the cell.

Solution. For the Segment cell, use Data Validation ▸ List with Source Gold,Silver,Bronze (or a named range), an in-cell dropdown and a Stop error alert. For the AnnualSpend cell, use Data Validation ▸ Custom with =AND(A2>0, A2=INT(A2)) (positive and whole), plus a Stop alert and an Input Message stating the rule. Validation ignores pastes, so a paste could still defeat the segment gate. To stop it, unlock only these two input cells, lock everything else, and Protect Sheet. The protection refuses a paste that would overwrite the validated input cell, now in a locked context, and it also keeps the paste from stripping the validation rule. Validation and protection together are the only combination that resists both typing and pasting.

P12 (timed: 8 min). Pick the right tool. For each task name the single best tool/feature and justify in one line: (a) prevent a duplicate invoice number being typed into a column; (b) split 2024-04-01BharatSteel0000145000 fixed-width into date/vendor/amount; (c) turn Anil Sharma in column A into asharma in column B for 500 rows, one-off; (d) show the shape of a 300-cell revenue block at a glance; (e) enforce that a "flag any margin < 20%" rule is unmissable.

Solution. (a) Custom data validation =COUNTIF($A$2:$A$100,A2)=1. It refuses a value that's already present, a primary-key guard at entry. (b) Text to Columns, Fixed width. The fields sit at fixed character positions, so you place break lines on the ruler. (c) Flash Fill (Ctrl+E). Type asharma once and let Excel infer the pattern for a one-off reshape, and switch to formulas if it has to stay live. (d) Color scale. Feeling the shape of a large block is what a gradient is for, its legitimate use. (e) A discrete formula CF rule =$E2<0.2 with a solid fill. It puts a hard, unmissable edge at the threshold, where a gradient would bury it. The skill underneath all five is matching the tool to whether you're preventing, repairing, exploring or enforcing.

Applied mini-project

Build a self-defending vendor & sales workbook: one workbook that can't be filled in wrong, and that repairs the messy data it receives. It brings every layer in this node together into a single durable artefact.

Sheet 1, Vendor onboarding form. Make it a real Table (Ctrl+T) with columns VendorName, State, GSTIN, InvoiceAmount, for at least 10 vendors:

1. State: List validation pointing at a named range States (at least 8 real states), with an in-cell dropdown and a Stop alert.
2. GSTIN: Custom validation =LEN([@GSTIN])=15 (or =LEN(C2)=15), with a Stop alert and an Input Message stating the rule.
3. InvoiceAmount: Decimal ▸ greater than 0, with a Warning alert above a large threshold you choose. A Warning and not a Stop, so a rare big invoice can be confirmed instead of blocked.
4. Conditional formatting. Add a full-row formula rule =$D2>100000 (solid fill) for large invoices, a Duplicate Values rule on the GSTIN column, and a rule that highlights any blank mandatory cell (=A2="").
5. Circle Invalid Data once to red-ring any pre-existing bad rows, and fix them.

Sheet 2, Sales cleanup. Paste in a raw sales extract of ~12 rows with at least two duplicate OrderIDs and a Revenue/Cost pair on each row.

1. Copy the sheet first. On the copy, run Remove Duplicates keyed on OrderID only, and record how many rows remained and how many were removed.
2. Add a Margin column =(Revenue-Cost)/Revenue and a formula CF rule =$E2<0.2 that flags thin margins, then count the flags.
3. Show the wrong tool too. Apply a color scale to the margin column and see how it hides which rows breach 20%. Then remove it, and write one sentence on why the discrete rule wins for a threshold.

Sheet 3, Reshape drill. Start from a single column of mashed strings (Vendor,State,GSTIN,Amount).

1. Use Text to Columns, Delimited (comma) to get four clean columns, and confirm the count.
2. Add a column and use Flash Fill (Ctrl+E) to derive a short code from the split name, such as the first four letters of the vendor in upper case.

Lock it all down:

1. On the form sheet, unlock only the input columns, leave everything else locked, and Protect Sheet allowing select and sort.
2. Protect Workbook structure so nobody can delete or reorder your three sheets.
3. Adversarial test, the real deliverable. Try hard to break your own form. Paste a fake state over the dropdown cell, and protection should refuse it. Type a 14-character GSTIN, and the gate should stop it. Type an invoice over the threshold, and the Warning should let it through once you confirm. Enter a duplicate GSTIN, and the CF should light it up. Note which layer caught each attack.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its validated form and locked-down structure become the template you reuse whenever a model has to survive other people's hands.

Lab: open the Data tools & integrity workbook lab under Practice ▸ Excel and pass its checks.

Reading & resources

Feature references (reach for these first):

• Microsoft Learn has the official pages for Apply data validation to cells, Use conditional formatting, Filter data / FILTER function, Remove duplicates, Split text into columns and Protect a worksheet. Search each phrase plus "Microsoft support". They're the authority on dialogs, options and edge cases. [Free] [Beginner]
• ExcelJet (exceljet.net) has concise, example-driven entries for conditional-formatting formulas, FILTER and custom data-validation rules, with copy-paste formulas you can adapt. [Free] [Beginner]

Deeper practice and pattern libraries:

• Chandoo (chandoo.org) has the classic write-ups on "conditional formatting based on another cell," "dynamic dropdowns" and "data validation," with downloadable files. It's the best single site for learning to anchor the formula right. [Free] [Beginner]
• Leila Gharani (YouTube) goes deep on data validation, dependent dropdowns and conditional formatting, and her videos are the strongest moving-picture treatment of these dialogs. [Free] [Beginner]
• Mynda Treacy / MyOnlineTrainingHub has articles on protection, form design and cleaning your data that stress the judgment of which tool, when, the same judgment you've been practising here. [Free] [Intermediate]

For the sceptic's habits, an extension of the common-mistakes section:

• Any reputable article on "why validation doesn't stop paste" or "conditional formatting wrong cells". Search those phrases, and learn each failure before you inherit a workbook full of them. [Free] [Beginner]

Bridge forward: the input-cell unlocking and sheet protection you learn here are the same machinery EX2.02 (What-If & Sensitivity) uses to protect a Data Table's structure. And the clean, de-duplicated, correctly-typed output of this work is the precondition for every PivotTable and model downstream.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Data validation (list, custom-formula), formula-anchored conditional formatting, Remove Duplicates, Text to Columns, Flash Fill, and sheet/workbook protection as a single layered integrity system",
  "use": "AI drafts a first-pass custom-validation formula or conditional-formatting rule well from a plain description of the business rule ('reject a state not on this list', 'flag any row where column E is under 20%'), and can explain an inherited validation/CF setup. It is weakest exactly where this module's signature warning lives: asked 'how do I stop bad data getting into this sheet', a general-purpose answer will describe data validation and stop there, because validation LOOKS like the whole answer -- it will rarely volunteer, unprompted, that validation does nothing against a paste, which is the fact this entire module is organised around.",
  "tools": ["Microsoft Copilot in Excel", "ChatGPT / Claude (general chat, for drafting a first-pass validation or conditional-formatting formula from a plain description of the rule)"],
  "prompt": "I need to stop bad data entering this range: <describe the rule>. Give me the data-validation formula. Then, separately and explicitly: does this rule stop a paste? If not, what else do I need so a colleague cannot paste over it?",
  "verify": "Test any generated validation or CF formula on the module's own boundary cases: a value that should pass, one that should fail, and one sitting exactly on the threshold (a GSTIN of exactly 15 characters, an invoice of exactly Rs 1,00,000). Then run the paste test yourself: copy an invalid value from elsewhere and paste it over the cell the AI just 'protected' -- if it lands with no complaint, the AI's answer solved only the half of the problem this module warns is never the whole answer alone.",
  "diy": "The mastery gate asks you to write a custom-validation formula and a full-row CF anchor from a blank cell, and to explain from memory why validation alone never stops a paste. Nothing in this callout is admissible in that room."
}

Modern Data Analysis

By hand first. Worked Example 2 deliberately keys Remove Duplicates on OrderID alone and leaves "all columns" unticked. Ticking every column keeps a duplicate the instant any one other field differs, and the worked example calls it plain luck when the two rows happen to agree.

Today's workflow. In pandas, drop_duplicates(subset=...) carries the identical hazard under different syntax. Its default, subset=None, checks every column, just like Excel's all-columns-ticked default, and misses a true duplicate the moment one field differs.

Tools & sources (IN + US). You don't need an external dataset. Reuse the twelve-row extract from the second worked example, then change one value, so the "that is luck" line becomes a proven fact instead of an assertion. Run this in your notebook:

# recomputed in-session -- R3
import pandas as pd
rows = [
    ("US-1001","Router",1200,900), ("US-1002","Switch",800,680), ("US-1003","Firewall",1500,1000),
    ("US-1004","Cable",600,510),   ("US-1005","AccessPt",2000,1700), ("US-1006","NIC",950,700),
    ("US-1007","Modem",1100,990), ("US-1008","Server",1300,900),   ("US-1009","Adapter",700,620),
    ("US-1010","Rack",1800,1300), ("US-1003","Firewall",1500,1050), ("US-1007","Modem",1100,990),
]   # note: the second US-1003 row now has Cost 1050, not 1000 -- one field differs from its earlier twin
df = pd.DataFrame(rows, columns=["OrderID", "Product", "Revenue", "Cost"])

by_key   = df.drop_duplicates(subset=["OrderID"], keep="first")     # the module's own, deliberate rule
by_all   = df.drop_duplicates(subset=None, keep="first")            # Excel's "all columns ticked" default
print("OrderID-only:", len(by_key), "rows  |  all-columns:", len(by_all), "rows")

Verify. Keying on OrderID alone reduces the sheet to 10 rows, matching the second worked example. Using every column as the key (subset=None) keeps 11. It fails to remove the second US-1003 row, because that row's Cost no longer matches. So the worked example's warning, "if row 12 had carried a different Cost, the all-columns setting would have kept it", is now a fact you've reproduced. The true duplicate order survives undetected because one incidental field happened to differ.

Quantitative lens

Published research does not find a large category of pure arithmetic mistakes in spreadsheets, because Excel's arithmetic is not what fails. Ray Panko's meta-analysis of field audits pulls together studies including Hicks 1995, Coopers & Lybrand 1997, KPMG 1998, Lukasic 1998, Butler 2000, and Lawrence & Lee 2001, across 85 inspection studies. It's summarised in Panko, "What We Don't Know About Spreadsheet Errors Today," 2016. He reports that the large majority of operational spreadsheets audited in practice contain at least one error, cited at 94% in that aggregate. Other Panko papers draw on overlapping but not identical study sets and cite figures from roughly 80% up. His error taxonomy names just three categories, and none of them is "the calculator got the sum wrong". Mechanical errors are things like pointing at the wrong cell, a typo, a bad reference. Logic errors use the right cells with the wrong formula or business rule. Omission errors leave out something that should have been there. Two of his controlled studies report the mix directly. Panko & Halverson (2001) found 45% logic, 23% mechanical and 31% omission. Panko & Sprague (1998) found 44% logic, 3% mechanical and 53% omission:

# recomputed in-session -- R3
logic, mechanical, omission = [45, 44], [23, 3], [31, 53]
print("mean logic:", sum(logic)/2, "mean mechanical:", sum(mechanical)/2, "mean omission:", sum(omission)/2)
print("mean structural (mechanical+omission):", (sum(mechanical)+sum(omission))/2)

Average just these two studies and you get logic 44.5%, mechanical 13.0% and omission 42.0%. Mechanical and omission together (55.0%) already outweigh logic errors alone, and none of the three categories is a failure of arithmetic itself. Take common mistake #2 from earlier, the conditional-format formula written =D2>100000 instead of =$D2>100000 that paints the wrong cells. In Panko's own vocabulary that's a textbook mechanical error. The arithmetic is flawless, and the formula is pointed at the wrong cell. Nothing in the toolkit you've built here defends against a correctly computed wrong number. Validation, duplicate keys, anchored CF and the unlock-then-protect order all defend against a wrong cell, a wrong rule, or a wrong assumption about what should have been there. Those are Panko's three categories, and there's no fourth "bad math" one.

Panko's papers also report per-cell error rates far below the headline sheet-level figure. They run on the order of a few tenths of a percent for routine, mechanical entry and several percent for complex logical steps. The papers also publish the formula for how those compound across a whole sheet of n independent cells, E = 1-(1-e)^n:

# recomputed in-session -- R3
def prob_at_least_one_error(e, n):
    return 1 - (1 - e) ** n

for e, label in [(0.005, "0.5%, routine mechanical entry"), (0.05, "5%, complex logical steps")]:
    for n in [50, 200, 1000, 5000]:
        print(f"e={label:32s} n={n:5d} -> P(>=1 error) = {prob_at_least_one_error(e, n)*100:.2f}%")

Even at the low 0.5% per-cell rate, a workbook with 1,000 formula cells has a 99.33% chance of containing at least one error. At the 5% rate for complex logic, just 50 cells already reach 92.31%. That's the mathematical reason a single "looks right" glance is worthless at any real scale. It's also why you've been building gates (validation) and paint (CF) instead of relying on a human to notice.

Where the numbers stop. The 94% figure comes from a meta-analysis of field-audit inspection studies, which isn't a random sample of every spreadsheet in use. Audited spreadsheets are disproportionately ones an organisation already suspected were important enough to check. That likely biases the figure upward relative to the true population rate, a limitation Panko's own later papers acknowledge. The 44.5%/13.0%/42.0% split is the mean of two named lab studies, and it isn't a general law of all spreadsheet errors. Different tasks in Panko's own record swing mechanical from 3% to 23%. What the research supports is the qualitative claim behind everything you've built here: errors are overwhelmingly about structure, reference and completeness, and not about the arithmetic operators themselves. It doesn't support a precise universal percentage.

Where this goes next: galaxy cross-links

• G2 · CS2.03 Databases & SQL: the production answer to "validation that cannot be pasted over" is a CHECK constraint or a UNIQUE index enforced by the database engine itself, whatever client happens to be doing the typing.
• G2 · DA1.02 Pandas Data Wrangling: the drop_duplicates(subset=...) hazard demonstrated above, plus the wider toolkit of .duplicated(), dtype checks and merge-validation that these habits grow into once the data leaves the worksheet.

Teach it back & journal

Feynman prompt. Explain to a shopkeeper who has never used a spreadsheet how you'd build an order pad that can't be filled in wrong. Use only the metaphor of a paper form on a clipboard, and cover three ideas in plain language. The first is a list of allowed answers the person must pick from, which is the dropdown. The second is a rule that rejects an answer of the wrong shape, like an ID that must have all fifteen boxes filled and no more, which is the length gate. The third is a laminated form where only the blank lines can be written on while everything else is sealed under plastic, which stands for locking plus protection. Finish with the one trick a clever-but-careless clerk might use to get round your form, sticking a pre-printed label over a box, which is a paste. Explain why the lamination, the protected sheet, is what actually stops them, and why the dropdown can't. Only in your last two sentences may you use the words data validation, conditional formatting and protect sheet, and attach each one to the moment in the story it describes.

Journal prompt. Open one workbook you already rely on, such as a tracker, a form, or an import you clean every month, and hunt it for three things. (1) An input cell that should have a dropdown or a rule but accepts anything, so a typo there would flow downstream unnoticed. (2) A place where you clean the same mess by hand every time, splitting a column, removing duplicates or re-typing amounts, that Text to Columns, Remove Duplicates or Flash Fill could do in one pass. (3) Any sheet you've handed to someone else where a formula could be overwritten because nothing is locked. Rebuild one of the three: add the validation, automate the clean, or unlock-then-protect the inputs. Write down what became more trustworthy the moment the workbook started fighting back against bad data. Then write the one sentence you want to remember for good, about why preventing a bad entry at the door is worth ten formulas that try to detect it afterward, and why a workbook's real quality shows in how it behaves in the next person's hands and not in yours.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.