Free download
Customer list clean-up toolkit (Excel)
Paste a messy customer list into one tab and get a standardised, duplicate-flagged version out of the other. I built this workbook for the data cleaning in Excel job every business puts off: name case fixed, stray spaces trimmed, UK phone numbers normalised, postcodes tidied, exact and near duplicates flagged, with before and after counts and a one page cleaning checklist.
What you get: one Excel workbook (.xlsx, no macros) with five tabs: README, Raw Data, Cleaned Data, Summary and Cleaning Checklist. The form asks for your name and email address before the download starts; they are sent to Digital Adaption with the name of the file you asked for.
Quick answer
To clean a customer list in Excel: trim spaces with TRIM, fix name case with PROPER, format phone columns as text so leading zeros survive, standardise postcodes to uppercase with a single space, then flag duplicates with COUNTIFS before merging anything. This free workbook has all of those formulas prebuilt for up to 1,000 rows.
Get the clean-up toolkit
Enter your details and the Excel workbook downloads immediately. Paste in your raw list and the cleaning formulas, duplicate flags and before and after counts fill themselves in.
No newsletter spam. The form sends me your name, email and the exact resource requested so I can follow up properly.
Your workbook is ready
Your request has been sent to me with the resource name, so I can follow up with the right notes.
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. Contents last checked: 8 October 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.
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
Sources
There is no official standard or government template for this job. The layout is our own working practice from data migration and reporting projects. The links below are the public guidance closest to it. Each link was opened and checked on 8 October 2026.
- Microsoft Support: Top ten ways to clean your data: the TRIM, PROPER and SUBSTITUTE techniques.
- Microsoft Support: Find and remove duplicates: how Excel's built-in duplicate tools behave.
- Microsoft Support: COUNTIFS function: how the counting and duplicate-flag formulas work.
- ICO: the accuracy principle: the legal duty to keep personal data accurate and up to date.
Digital Adaption also does paid data migration and reporting work for UK businesses; contact us if that would help.