How to Clean Messy Excel or CSV Data (Step by Step)
Published September 8, 2026 · 5 min read
Almost every spreadsheet that lands on your desk has the same handful of problems: duplicate rows from a bad export, dates written three different ways, emails with stray capital letters or spaces, and phone numbers that don't match any consistent format. None of this is hard to fix individually — the tedious part is doing it by hand, cell by cell, across a few thousand rows.
Here's a repeatable process for cleaning that kind of file in a few minutes, using Data Studio — a free browser-based data cleaner that never uploads your file anywhere.
1. Trim invisible whitespace first
Before you even look at duplicates, strip leading and trailing spaces from every text column. A value like "john@email.com " with a trailing space looks identical to "john@email.com" in a spreadsheet cell, but most systems will treat them as two different values — which quietly breaks lookups, filters, and duplicate detection later. Doing this first means every other cleaning step actually works correctly.
2. Remove duplicate rows
Duplicates usually come from double form submissions, re-exports, or merging two files that overlap. The tricky part is deciding what counts as a "duplicate" — an exact full-row match is the safest default, but sometimes you need to match on just one column (like an email address) and keep the most recent row. A good cleaning tool lets you choose which columns define a duplicate instead of forcing an all-or-nothing match.
3. Standardize dates
Dates are the single biggest source of silent errors in spreadsheets. 03/04/2026 means March 4th in the US and April 3rd almost everywhere else — and a file with both formats mixed together will misinterpret roughly half the dates without throwing any error. Standardizing every date column to one unambiguous format (like YYYY-MM-DD) before you analyze anything is non-negotiable if the data came from more than one source.
4. Fix and validate emails
Common email issues are surprisingly mechanical: stray spaces, inconsistent capitalization, and missing @ symbols from copy-paste errors. Lowercase every email address (email addresses are effectively case-insensitive for the domain part) and flag anything that doesn't match a basic email pattern, so you can spot-check the flagged rows instead of eyeballing the entire column.
5. Normalize phone numbers
The same number can appear as 9876543210, +91 98765 43210, and 987-654-3210 in the same column. Pick one format, strip all non-digit characters first, then reformat consistently — this matters most if you're later trying to match phone numbers against another dataset.
6. Export and double-check row counts
Once cleaning is done, export to CSV or Excel and check the row count against what you started with. If you removed duplicates, the drop should match what you expected — if it doesn't, something unintended got filtered out, and it's worth reviewing before you use the file downstream.
Doing this without uploading your file
If the spreadsheet contains anything sensitive — customer emails, phone numbers, internal data — uploading it to a random web tool to "clean" it isn't a great idea, since you don't know what happens to it after that. Data Studio runs the entire process above inside your browser using JavaScript's File API — your spreadsheet is read, cleaned, and re-exported locally, and no copy of it is ever sent to a server.