Most lists arrive with mixed formats, duplicates, stray characters and merged cells. Cleaning is mechanical if you work in the right order, and frustrating if you do not.
Step one: copy before you touch anything
Keep the raw file untouched and clean a copy. When something goes wrong, and it will, you can always compare against the original rather than trying to reverse a change.
Step two: fix the structure
- Unmerge every cell — merged cells break sorting and filtering
- Ensure a single header row with unique column names
- Remove blank spacer rows and repeated header rows
- Convert the range to a table so formulas follow the data
Step three: normalise text
- Trim spaces, including non-breaking spaces pasted from the web
- Remove stray characters such as trailing commas and quotes
- Standardise capitalisation per column rather than globally
- Split full names into first and last columns
- Correct obvious encoding damage such as accented characters shown as symbols
Step four: normalise numbers and dates
Phone numbers to one international format with the country code, stored as text so leading zeros survive. Dates to one unambiguous format. Currency to a numeric column plus a separate currency column, never a single text field.
Step five: de-duplicate
Remove exact duplicates first, then match on the strongest available key: website domain, then email, then phone. Review anything that is only a name match, because two different people share a name more often than people expect.
Step six: validate and separate
Check that required fields are present, emails pass syntax, websites resolve and no column is accidentally empty. Move anything uncertain to a second sheet with a reason attached rather than deleting it silently.
Step seven: make it usable
Common mistakes during cleanup
- Sorting or filtering while cells are still merged
- Using replace all on a character that appears legitimately elsewhere
- Changing phone numbers to a numeric format, which drops the leading zero
- Deleting uncertain rows instead of moving them aside with a reason
- Cleaning the original file rather than a copy
The replace all error is the most damaging because it is silent. A stray character removed from a column of company names can quietly corrupt hundreds of rows before anyone notices.
Tools inside Excel that do the work
Text to columns splits names and combined fields. Flash fill handles inconsistent capitalisation on a single column. Remove duplicates needs the right key chosen before it runs. Power Query is the better choice for a file you will refresh from the same source every month, because it records the steps and reapplies them automatically.
Cleaning syntax is only half the job; deliverability depends on verification too. See how to verify email addresses for that step, and if the volume is too large to handle in house, outsourcing data entry covers what to check before handing it over.
Freeze the header row, set sensible column widths, and save as both XLSX and CSV. Add a short note listing what was cleaned, what was rejected and why. That note prevents the same questions being asked twice.
Deliver two sheets: the clean data, and the rejected rows with reasons. Clients trust the second sheet as much as the first.