Data operations
How to clean up a messy spreadsheet
Every messy spreadsheet is messy in the same handful of ways. Here are the fixes, in the order they have to run — because doing them out of sequence wastes the work.
How do I clean up a messy spreadsheet?
Work in this order: make a copy, strip invisible characters and trailing spaces with TRIM and CLEAN, standardise casing and formats, split or merge columns, then remove duplicates last. Deduplicating before standardising is the most common mistake — two identical records that look different will not match.
The order matters more than the technique
Most people start by removing duplicates. That is the step that should come last.
John Smith and john smith are the same person, but no deduplication tool will match them while one has a trailing space and different casing. Standardise first, and the duplicates reveal themselves.
Step 1 — Copy the file before touching anything
Keep an untouched original. Not a version you have "only tidied slightly" — genuinely untouched. Every cleanup involves a judgement call you will later want to reverse.
Step 2 — Strip invisible characters
Trailing spaces and non-printing characters are the single biggest cause of failed matches, and they are invisible on screen.
=TRIM(A2)removes leading, trailing and repeated internal spaces=CLEAN(A2)removes non-printing characters, common in data pasted from PDFs or web pages=TRIM(CLEAN(A2))does both, which is what you usually want
Non-breaking spaces survive TRIM. If data came from a web page, run =SUBSTITUTE(A2,CHAR(160)," ") first.
Step 3 — Standardise casing
=PROPER(A2)for names and company names=LOWER(A2)for email addresses, always=UPPER(A2)for country and state codes
PROPER has a known weakness: it produces Mcdonald and O'brien. On a list of any size, spot-check surnames afterwards.
Step 4 — Fix the formats that break imports
Phone numbers. Pick one format and convert everything to it. E.164 — +447700900123 — is what most CRMs expect. 07700 900123 and +44 7700 900123 are the same number, and no system knows that until you tell it.
Dates. The most dangerous field in any spreadsheet, because 03/04/2026 means March in the US and April everywhere else. Convert to YYYY-MM-DD and the ambiguity disappears.
Numbers stored as text. Look for the green triangle in Excel. Multiply by 1, or use Text to Columns with the default settings, to convert the whole column.
Step 5 — Split and merge columns
Use Text to Columns (Excel) or SPLIT (Sheets) to separate a full-name column. Be careful: Maria del Carmen Garcia Lopez does not split into two fields cleanly, and neither do most non-Western naming conventions. Where the split is uncertain, keep the original column as well.
Step 6 — Standardise company names
Acme Ltd, Acme Limited, Acme Ltd. and ACME LTD are one company and four rows. Strip suffixes into a separate column so the base name can match:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," Ltd",""), " Limited",""), " Inc",""))
Step 7 — Now remove duplicates
With everything standardised, deduplication finally works. In Excel: Data → Remove Duplicates, matching on the identifier column, usually email.
Before you do, understand what it does: it keeps the first row and deletes the rest. If the second row has a phone number the first is missing, that phone number is gone. On data you care about, merge rather than delete — build a surviving record that takes the most complete value from each field.
Power Query, if the job repeats
If you clean the same export every month, do it once in Power Query (Excel: Data → Get & Transform). It records the steps and replays them on the next file. An afternoon of setup that saves a day a month.
When doing it yourself stops making sense
Everything above is genuinely worth learning, and for most spreadsheets it is enough. Three situations change the maths:
- Volume. Past roughly 2,000 rows, manual review of edge cases stops being realistic in an afternoon. That is the point most people hand it over — our Excel and Google Sheets cleanup gig starts there.
- Fuzzy matching. Excel matches exactly.
Bob SmithandRobert Smithat the same company need similarity matching, which Excel does not do. - Verification. Cleaning formatting does not tell you whether an address still works. That needs checking against a live source.
On one client database we found 51.4% of the records were unusable — not badly formatted, genuinely dead. No amount of TRIM would have revealed that.
Last reviewed 2026-07-28.
Questions
Common questions
What is the fastest way to clean data in Excel?
TRIM and CLEAN on every text column first, then Remove Duplicates last. That sequence alone fixes the majority of problems in a typical export. If you repeat the same clean-up monthly, record it once in Power Query instead.
Why does Remove Duplicates miss obvious duplicates?
Because it matches exactly. A trailing space, different casing, or 'Ltd' versus 'Limited' makes two identical records look different. Standardise before deduplicating and the matches appear.
Should I clean data before or after importing to my CRM?
Before, always. Cleaning in a spreadsheet has unlimited undo. Cleaning inside a CRM often does not - GoHighLevel, for example, merges a maximum of 10 records at a time and the merge cannot be reverted.
What does a professional spreadsheet cleanup cost?
From $250 for up to 2,000 rows, covering deduplication, standardisation and email verification. We clean a sample first and send it back before you pay, so you can see the difference on your own data.
Can AI clean a spreadsheet for me?
It can help with formatting and pattern recognition, and it is genuinely good at that. What it cannot do reliably is decide whether two similar records are the same company, or verify that an address still works. Those need checking against a source of truth.
Want this done for you?
Send a sample of your data. We will tell you what is wrong with it and what it would take to fix — free, within one business hour.