Customer list clean-up toolkit (Excel)

What is inside the workbook

• 1. Raw Data: paste your customer list here (name, email, phone, postcode, notes). The phone column is preformatted as text so leading zeros survive.
• 2. Cleaned Data: formula columns that standardise every field and flag EXACT DUPLICATE and CHECK rows, for up to 1,000 records.
• 3. Summary: before and after counts, missing emails, phones and postcodes, and how many unique customers you actually have.
• 4. Cleaning Checklist: a one page, ten step process with a status dropdown, so the clean-up gets finished rather than started.

How the data clean up formulas work

• Names: PROPER(TRIM(name)) fixes SHOUTING, lowercase and double spaces in one pass.
• Emails: lowercased and trimmed, so John.Smith@Example.com and john.smith@example.com stop looking like two customers.
• Phones: spaces, dashes, dots and brackets stripped, +44 and 44 prefixes converted to a leading 0. Best effort by design: anything unusual is left visible for a human decision.
• Postcodes: uppercased with a single space before the final three characters, so ch411aa becomes CH41 1AA.
• Duplicates: COUNTIFS across all four cleaned fields flags exact duplicates. A second flag catches rows sharing a normalised name and postcode, which is where the sneaky near duplicates live.

No tax rates are used in this toolkit. Rates last checked: 15 July 2026.

Why I flag duplicates instead of deleting them

Excel's Remove Duplicates button is the fastest way to quietly lose order history. It only matches rows that are identical character for character, so "John Smith" and "john smith " survive as two customers, and it merges without telling you which row it kept. This toolkit takes the slower, safer route: normalise first, flag second, and let you make the merge decision with both rows in front of you. Once the duplicates are merged, most lists turn out meaningfully smaller than the CRM claimed, and that real customer count changes marketing budgets.

How do I clean data in Excel?

Work in a copy, then use TRIM to remove stray spaces, PROPER to fix name case, SUBSTITUTE to strip unwanted characters and COUNTIFS to flag duplicates. That is exactly what this toolkit does: paste your raw list into one tab and the cleaned version appears on the next, formulas included, so you can see how every fix works.

How do I remove duplicates from a customer list in Excel?

Do not reach for the Remove Duplicates button first. It only catches rows that match exactly, and it deletes without showing you what it merged. Flag duplicates with COUNTIFS first, review them, then merge. The toolkit flags exact duplicates and near duplicates (same normalised name and postcode) separately so you decide what gets merged.

How do I fix UK phone numbers in Excel?

Format the phone column as text first, otherwise Excel drops the leading zero. Then strip spaces, dashes and brackets with SUBSTITUTE, and convert +44 or 44 prefixes back to a leading 0. The toolkit does all of this in one formula column, best effort, and the checklist tells you which leftovers need a manual look.

Why does Excel remove the leading zero from phone numbers?

Excel treats anything that looks like a number as a number, and numbers do not keep leading zeros, so 07700 900123 becomes 7700900123. Format the column as text before you paste, or prefix with an apostrophe. The raw data tab in this toolkit is already formatted as text for that reason.

When is a spreadsheet no longer enough for customer data?

When more than one person edits the list, when the same customer exists in three places, or when the list feeds invoicing, marketing or a CRM. At that point clean-up becomes a repeating chore rather than a one-off. That is usually the trigger to move to a proper system, and cleaning the data first is what makes that migration cheap instead of painful.

Related resources