How to clean up a messy spreadsheet
Upload the file once, look at what is actually wrong with it, fix the formatting, then deal with the duplicates. Each step hands the file to the next, so you upload once and download at the end.
Free, no sign-up, and your file is read on your own device rather than uploaded.
A spreadsheet is usually "messy" for four separate reasons at once: stray spaces that make two identical values look different, capitalisation that is inconsistent, dates and numbers stored as text, and rows that appear more than once. They need different fixes, which is why one button labelled "clean" tends to disappoint.
This is the order that works, and the reason for it is simple: cleaning changes what counts as a duplicate. "ACME Ltd " and "Acme Ltd" are not duplicates until the spaces and capitalisation are dealt with. Look first, fix formatting second, then hunt duplicates - do it the other way round and you will miss most of them.
- See what is actually wrong
- Fix the formatting
- Find the exact duplicates
- Find the ones that are nearly the same
- Download the cleaned file
The steps
See what is actually wrong
Before changing anything, find out what the file holds: which columns are text, numbers or dates, how many values are blank, and where the mixed columns are. This changes nothing - it only reports.
See What Is in My FileFix the formatting
Trim spaces, standardise capitalisation, normalise dates and numbers, and drop blank rows. This is the step that rewrites the sheet, and every change is one you have selected and can see before it is applied.
Data CleanerFind the exact duplicates
Now that the values are consistent, rows that are genuinely the same will look the same. Choose which columns define a duplicate - often a reference or an email rather than the whole row.
Duplicate FinderFind the ones that are nearly the same· only if you need it
Three spellings of one customer will still be three rows. This groups records that are similar rather than identical, and shows how alike each group is so you can judge the borderline ones yourself.
Find Similar RecordsDownload the cleaned file
Export from the cleaner as Excel or CSV. The Excel download keeps your original on a second sheet, so nothing is lost.
Data Cleaner
What "messy" looks like in practice
A customer export has 4,048 rows. Three of them read "ACME Ltd " with a trailing space, "Acme Ltd" and "ACME LTD" - the same company, entered by three people. Two rows are byte-for-byte identical because the export ran twice. One reads "Gama PLC" where every other row says "Gamma PLC".
Profiling finds the mixed capitalisation and the trailing spaces. Cleaning collapses the first three into one value. The duplicate finder then catches the exact repeat, which it could not have done before the cleaning, because the trailing space made those rows different. The near-miss spelling is the last one left, and that is what the fuzzy tool is for - with your judgement, because only you know whether "Gama" is a typo or a different company.
What this cannot do
Worth knowing before you start, rather than halfway through.
- Near-duplicates are reported, never merged automatically. Deciding which of two similar rows is the right one is a judgement about your data, not a rule a tool can apply safely.
- Cleaning works on one sheet at a time. If the mess is spread across several sheets, do them one after another.
- Very large files are limited by your device's memory rather than by an upload cap, because the work happens in your browser.
Common questions
- Is my spreadsheet uploaded anywhere?
- No. The file is read and processed by the page on your own device. It is never transmitted to VSThiran and no copy is stored on a server. It lives only in the tab you have open, which is why reloading the page clears it.
- Do I have to upload the file again for each step?
- No. Open it once and the other data tools offer to pick it up as it currently stands. If you clean it, the next tool sees the cleaned version rather than the file you started with.
- Can I clean a CSV as well as an Excel file?
- Yes. XLSX, XLS, CSV, TSV and plain text all work, and you can export as either Excel or CSV regardless of what you started with.
- Will it change my original file?
- Never. Nothing is written back to the file on your computer. You download a new copy, and the Excel export includes your original untouched on a second sheet.
- Why do I need to clean before finding duplicates?
- Because a duplicate finder compares values, and "Acme Ltd" is not the same value as "ACME Ltd ". Trimming and standardising first is what makes those two rows recognisable as the same record.
- What is the difference between duplicates and similar records?
- Duplicates are rows whose chosen columns match exactly. Similar records are rows that are close but not identical - a misspelling, an abbreviation, a missing middle initial. They need different tools because they need different judgement.
The tools this uses
- See What Is in My FileSee what is actually in a spreadsheet before you rely on it.
- Data CleanerTidy a spreadsheet, and see exactly what changed, with real before-and-after examples.
- Duplicate FinderFind duplicate rows by whichever columns actually define a duplicate.
- Find Similar RecordsFind the duplicates that an exact match will never catch.
Continue in the workspace
Your file stays open there - pick the next thing to do to it, without a second upload.