Data cleaning
Bring the messy version
Every engagement starts with a file somebody apologises for. This is the first hour of that work, running on your machine: it reads the spreadsheet, says what is actually wrong with it, and hands back a cleaned copy.
Or to see what it catches.
What it looks for
Each of these comes from something that actually cost someone time. None of it is clever; all of it is the sort of thing that quietly makes a number wrong and survives three rounds of review because nobody can see it.
- Duplicate rows
- Identical once case and stray spaces are ignored, so totals count them twice.
- Mixed units
- Millimetres and inches in one column, or a unit on some values and not others.
- Two decimal conventions
- A comma in some rows and a full stop in others — a factor of a thousand.
- Dates in two orders
- ISO and day/month together. Below the 13th the two are indistinguishable.
- Numbers stored as text
- The column sorts alphabetically, so 100 comes before 20.
- The same value, spelled three ways
- Case, spacing and punctuation differences that break every join.
- Hidden placeholders
- N/A, TBC, "-" and blanks, counted together rather than averaged into a number.
- Stray whitespace
- Invisible on screen, enough to break an exact match.
Where this stops, and what comes next
This finds problems and fixes the mechanical ones. It will not tell you whether a blank means zero or unknown, which date order your supplier meant, or which of three spellings is the real customer — those need someone who knows the business. That conversation is the actual work, and it goes much faster once everyone is looking at the same list.
The same parsing runs inside the quote matcher, where cleaning the input first is worth 22.6 points of accuracy over matching on the raw file.
Questions people ask
- What is data cleaning?
- Data cleaning is finding and correcting the errors that make a dataset unusable or misleading: duplicate rows, missing values disguised as placeholders, inconsistent spellings of the same thing, numbers stored as text, mixed units and mixed date formats. It is usually the largest part of any analysis.
- What are the most common problems in a real spreadsheet?
- Duplicate rows that double a total; the same value spelled several ways, which breaks every join and group-by; units written inside the values so the numbers are not comparable; two decimal conventions in one column, which is a factor of a thousand; dates in both ISO and day-month order, which are indistinguishable below the thirteenth; and placeholders such as N/A or TBC that get averaged as if they were data.
- How do you find duplicate rows?
- Compare rows after normalising them — trimming whitespace and ignoring case — because exact matching misses duplicates that differ only by a trailing space or a capital letter. Those near-identical rows are the ones that survive a visual check and still inflate a total.
- Can I clean an Excel file, or only CSV?
- Both. Drop in a .xlsx workbook or a .csv, .tsv or tab-separated export and it is read the same way. The first sheet with data in it is the one profiled. Dates come back as dates rather than as the long text Excel sometimes produces, and a column whose header is blank keeps its values instead of losing them. The old .xls binary format cannot be read by anything in a browser — save it as .xlsx or export it as CSV first, and the tool says so rather than failing silently.
- My CSV opens as one column. Why?
- Almost always the delimiter. Excel on a Dutch or German machine writes semicolons rather than commas, and some exports use tabs or pipes. This tool works out which character actually separates the fields by parsing a sample with each candidate and keeping the one that produces a consistent table, so a comma file whose description column is full of semicolons still reads correctly.
- Is it safe to upload a spreadsheet to an online data cleaning tool?
- Usually that depends on the tool's hosting and retention policy. This one avoids the question: the file is read in your own browser and never sent anywhere, so there is no upload, no copy on a server and nothing to retain.
Try it on your own file first
Every tool here runs in your browser and uploads nothing, so you can see what we would see before telling us anything at all. When it is worth a conversation, send the real extract and the question you actually need answered.