How to clean up a messy customer spreadsheet (and when to automate it)

Almost every small business has one: the customer spreadsheet that has grown for years. Names in different cases, dates in three formats, the same person listed twice, postcodes with no space. Here is a practical way to clean it up in Excel or Google Sheets, and how to tell when it’s time to stop doing it by hand.

Before you start: make a copy

Always work on a copy and keep the original untouched. Add a column called Notes so you can mark rows you’re unsure about rather than deleting them.

1. Trim spaces and fix capitals

Extra spaces stop matches and duplicates from being spotted. In a helper column use =TRIM(A2), then =PROPER(TRIM(A2)) for names. Watch out for names like McDonald or O’Neill, which PROPER can get wrong, and check them by eye. Paste the results back as values.

2. Standardise dates

UK sheets often mix 03/04/2025, 2025-04-03 and “3 April 2025”. Worse, a date imported from a US system may have the day and month swapped. Decide on one format (DD/MM/YYYY is usual in the UK), convert text dates with DATEVALUE, and spot-check any date where the day is 12 or below, since those are the ones that can be ambiguous.

3. Fix postcodes

UK postcodes should be upper case with a single space before the last three characters, for example BN1 1AA. A formula that removes spaces, upper-cases the text and inserts a space before the final three characters fixes most of them. Anything that still doesn’t look like a postcode should go in your Notes column.

4. Check email addresses

Lower-case all emails and trim them. Then filter for entries missing an “@” or a dot after it. Common typos such as “gmial.com” or “hotmail.co” are worth a quick search-and-replace.

5. Remove duplicates carefully

Excel and Google Sheets both have a Remove duplicates tool, but it only catches exact matches. Clean the data first (steps 1–4), then de-duplicate on email address, which is usually the most reliable identifier. Where two rows have different details for the same person, keep the most recent and note the change.

6. Split or combine columns

If full names are in one column and you need first and last names separately, use Data > Split text to columns. If the address is spread over five columns but your mailing tool wants two, combine them with TEXTJOIN.

7. Keep a record of what you changed

Add a note at the top of the sheet listing what you fixed and when. It helps the next person and is good practice under UK GDPR, which expects personal data to be accurate and kept up to date.

When should you automate it?

A one-off clean-up is often fine by hand. It’s worth automating when:

• you get a fresh export every week or month and fix the same things each time;
• several sources need merging, such as your website, till and email list;
• the file has thousands of rows and manual checks are unrealistic;
• mistakes have real costs, like missed deliveries or emails to the wrong person.

An automated clean-up takes the raw export, applies your rules and gives back a tidy file in seconds, the same way every time.

A note on personal data

Customer lists are personal data. Only keep the columns you actually use, delete customers you no longer have a reason to contact, and don’t email the file around. If you send it to someone to clean, make sure they treat it securely and delete it afterwards.

Need a hand?

If your spreadsheet has gone beyond a quick tidy, send me a description and a few anonymised rows. I’ll suggest the simplest fix, whether that’s a one-off clean-up or a repeatable tool.