The unglamorous majority
People who do this for a living put the share of time spent preparing data somewhere between 60 and 80%. That is not a failing of their tools. It is what the job is, and it is worth knowing before you start, so that the afternoon you spend fixing dates does not feel like a detour.
The five faults you will actually meet
Duplicates. The same order exported twice, the same customer under two records. Check by counting rows and counting distinct identifiers; if they differ, find out why before doing anything else.
Dates as text. Your spreadsheet sees 03/04/2026 and applies a regional guess. Half the world reads that as March, half as April. Where the day is above 12 the guess is forced and correct; where it is below 12 it is silent and might be wrong. The result is a dataset where some dates are right and some are six months out, and nothing looks broken. Convert dates to YYYY-MM-DD as the first operation on any file.
Numbers as text. A currency symbol, a comma, a trailing space, and the column will not sum. Worse, it sums the subset that parsed, which produces a plausible total that is wrong by exactly the rows you did not notice.
Categories that multiply. Repair, repair, Repairs, Repair , REPAIR. Five categories to a computer, one to you. This is the fault that quietly ruins more small-business analyses than any other, because every count and every chart splits along lines you cannot see.
Missing values. An empty cell, a zero and the word N/A are three different things, and treating a blank as a zero turns “we did not record this” into “this was nothing”, which will drag every average you calculate towards a number that never happened.
Where a model helps, and where it must not
It helps with categories. Give it the 340 distinct values in your product column and ask which are the same thing, and it will group them well: that is meaning-matching, which is what these models are good at. It also writes the cleaning code faster than you will.
Two hard limits, both from module six.
It must not do the arithmetic. Totals, counts and averages come from the spreadsheet or the code, never from the model reading the data and reporting a figure.
And it must not decide what a blank means. Only you know whether an empty delivery-charge cell means free delivery, collection, or a till operator who skipped the field. Guessing here invents a business fact, and every number downstream inherits it.
The tools, all free
LibreOffice Calc does find-and-replace, text-to-columns and deduplication, and opens the CSV your platform exports without a licence.
OpenRefine is free, open source, and built exactly for the categories problem. Its clustering feature finds variant spellings automatically and is the fastest route from 340 product names to 90 real ones.
Python with pandas is free and handles files a spreadsheet will not open. Roughly fifteen lines does the whole job, and a model will write those fifteen lines correctly if you describe your columns.
Paid tools exist and do the same work with nicer screens. For the volumes in this course they add nothing a free tool does not already do.
Keep the original
Never clean in place. Keep the raw export untouched, do the cleaning as a script or a documented sequence of steps, and write the result to a new file.
Two reasons, and the second is the one that gets people. You will need to rerun this next month, and a remembered sequence of manual edits cannot be rerun. And when a figure looks wrong, the only way to find out whether the fault is in the data or the cleaning is to go back to what came out of the till.
The test that catches most of it
Before analysing, check four things and write the answers down.
How many rows, and how many distinct customers, orders or jobs. What the earliest and latest dates are: this catches the 1970 and 2087 rows that every export contains. What the smallest and largest amounts are, which catches the negative refunds and the misplaced decimal point. And how many distinct values each category column has, which catches the multiplying-categories problem immediately.
Four questions, ten minutes, and they find something in most real datasets. A figure produced without them is not wrong, exactly. It is unexamined, which is a different problem and usually a longer-lived one.
Every lesson here is open. Your progress is recorded once you pass the AI at Work exam.
Check your understanding
A workshop’s job export has a type column with 61 distinct values, which the owner knows should be about 12. She uses a model to group them and it returns 12 clean categories. What must she check before trusting any counts?
Pick the one you would defend. Your answer stays private.
Stuck on something?Ask Yetunde, your AI tutor