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

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.