Session 2: Cleaning & Validation

The first principle of cleaning is that you should never fix the same problem twice by hand. Our State column has about 1,200 entries across nine spellings of three states. Changing them one at a time would take an hour, introduce new errors, and have to be done again next month when the next export arrives.

So cleaning is done with formulas in a helper column, which is fast, consistent and reviewable, and then the result is pasted back as values. A helper column is simply a new column beside the data holding a formula — CleanState next to State — which you can check before you commit to it. Never overwrite the original column in place until the helper column has been verified, because a formula dragged 1,200 rows down will happily apply a wrong logic 1,200 times.

The second principle is that cleaning must be documented. Every change you make — what, why, how many rows it affected — goes in a notes tab. Not for bureaucracy: because in six weeks you or a colleague will ask why LG became Lagos, and because a manager is entitled to know that fourteen duplicated rows were removed before the revenue figure was calculated.

Five functions handle almost every text problem in business data. `=TRIM(A2)` removes leading and trailing spaces and collapses repeated internal spaces — which fixes Adebayo Ogundimu and LAGOS with a trailing space in one step. `=CLEAN(A2)` strips non-printing characters, which is what you need for data that came out of another system and carries invisible junk; a value that looks identical to another but will not match is usually carrying one.

`=UPPER(A2)`, `=LOWER(A2)` and `=PROPER(A2)` control capitalisation. For names and products, PROPER is usually right, giving Adebayo Ogundimu and Bucket 20L. Be careful: PROPER also capitalises the second letter of anything after a punctuation mark, so McDonald becomes Mcdonald and 20L is fine but iPhone becomes Iphone — check the results rather than assuming.

`=SUBSTITUTE(A2, "old", "new")` replaces text, and it is the workhorse for standardisation. To collapse our nine State spellings into three you can chain them: `=SUBSTITUTE(SUBSTITUTE(PROPER(TRIM(B2)), "Lagos State", "Lagos"), "Lg", "Lagos")`. Note the order — TRIM and PROPER first, so that lagos, LAGOS and LAGOS all become Lagos before you try to match anything, otherwise you are matching against moving targets. SUBSTITUTE is also how you strip a naira sign and comma from a price: replace ₦ with nothing, then replace the comma with nothing.

Once UnitPrice is free of the naira sign and comma it may still be text. `=VALUE(A2)` converts a text-number into a real number; `=NUMBERVALUE(A2)` does the same and lets you specify the decimal and grouping separators, which matters when a file was produced with different regional settings. After converting, re-test with `=ISNUMBER()` — do not assume, because a stray space or an invisible character will make VALUE fail and return #VALUE!.

For our Quantity column, where some cells read 2 pcs, extract the number rather than retyping: pull the digits with `=VALUE(LEFT(A2, FIND(" ", A2) - 1))`, or, more robustly, strip the unit with SUBSTITUTE first and then convert. The general habit is extract, then convert, then verify — three steps, because a conversion that fails silently is worse than one that fails loudly.

Dates are harder because of the ambiguity we found. `=DATEVALUE(A2)` converts a text date to a real one, but only if your spreadsheet can interpret the format — and for 12/03/2025 it will guess using your system's regional settings, which may not be what the exporting system meant. The reliable method is to split the text and rebuild it explicitly: extract the parts with MID and FIND, then assemble with `=DATE(year, month, day)`, deciding for yourself which part is the month. Then verify against rows that are unambiguous — a value like 25/03/2025 can only be 25 March, and if your logic gets that one wrong, the logic is wrong.

Our fourteen duplicate rows came from double entry, and removing them is easy — but removing the wrong things is easy too. Highlight duplicates on OrderID first and look at them. Two rows with the same OrderID and identical values are a genuine duplicate. Two rows with the same OrderID but different products are a legitimate multi-line order, and deleting one loses a real sale. The duplicate check must be on the whole row, or on a key you have verified is genuinely unique, not on any column that happens to repeat.

Then remove them deliberately: copy the duplicates to a separate tab before deleting, so you have a record of what went and can put it back. Then delete. The count should match what you found — fourteen, not thirty-seven — and if it does not, stop and find out why.

Missing values are a decision, not an absence. Our PaymentMethod has blanks, N/A, a dash and unknown — all meaning the same thing, and Excel treats them as four categories. First standardise: replace all four with one value, say Not recorded, using SUBSTITUTE and a blank check. Then decide what it means for your analysis. If 9 per cent of payment methods are unrecorded, you can report cash versus transfer across the 91 per cent that is known — as long as you say so. What you must not do is treat the missing as a category called Not recorded and report it alongside Cash and Transfer as though it were a payment method.

Cleaning a dataset once is half the job; the other half is making sure next month's export is not equally messy. Data validation constrains what can be entered in a column. In Excel: Data → Data Validation. Set State to a List containing exactly Lagos, Ogun, Oyo, and nobody can type LAGOS again. Set Quantity to a Whole number between 1 and 500, and a keying error of 5000 is rejected at entry rather than discovered in the quarterly report.

Validation is cheap and it is the highest-value thing in this session, because every rule you add removes a cleaning step forever. The columns to constrain first are exactly the ones that were dirty: any category column becomes a list, any number column gets a range, any date column gets a date range, and any required column is set to reject blanks.

For data coming from a system you do not control, you cannot validate at entry, so validate on arrival: a checks tab with formulas that count rows where State is not in the allowed list, where Quantity is out of range, where LineTotal does not equal Quantity times UnitPrice. `=SUMPRODUCT(--(ISNA(MATCH(E2:E1201, {"Lagos","Ogun","Oyo"}, 0))))` counts invalid states in one cell. Run those checks on every new export before you analyse it, and cleaning stops being a monthly scramble.