Playbook: clean up and organize a customer list

Most customer lists are a pile. Some names are in capitals. Some have two phone numbers in one cell. Maria Gonzalez is in there three times, once as "Maria G." There's a row that just says "Mike, plumber."

You can fix that in an afternoon. It's dull work and it makes every later job easier, from reminders to emails to figuring out who your best customers are.

Before you touch anything

Make a copy. Right-click the sheet tab, duplicate it, and work on the copy. Or save a new version of the file with today's date in its name. Cleaning goes wrong sometimes, and you want to be able to go back.

Then look at the list. Scroll through a hundred rows and notice what's wrong. Write down the problems. Usually they fall into a few kinds: inconsistent capitalization, extra spaces, mixed phone formats, duplicates, and blank cells.

Set up the columns

One row per person. One piece of information per column. A good starting layout:

If a cell has two phone numbers, split them into two columns. If "Name" holds first and last together, split it. In Google Sheets and Excel, look for "Split text to columns" under the Data menu.

The last three columns are worth adding even if they're empty now. Knowing where someone came from and whether they've agreed to messages saves you from trouble later.

Fix the small stuff with formulas

Do these in a helper column next to the messy one, then copy and paste the results back as values.

• =TRIM(A2) removes extra spaces at the start, end, and middle. Stray spaces are the top cause of duplicates that look different but aren't.
• =PROPER(A2) turns "MARIA GONZALEZ" into "Maria Gonzalez." Careful, though. It turns "McDonald" into "Mcdonald" and "de la Cruz" into "De La Cruz." Look through the results, and fix those by hand.
• =LOWER(A2) for email addresses. Email isn't case-sensitive, and mixed case makes matching harder.
• For phone numbers, strip everything but digits, then pick one format. If you need help writing the formula, an AI tool can do that well. Check the output on twenty rows before applying it to the rest.

Find the duplicates

Both Google Sheets and Excel can do this.

In Google Sheets, select the columns, go to Data, then Data cleanup, then Remove duplicates. In Excel, it's Data, then Remove Duplicates.

Before you let it delete, ask what counts as a duplicate. Same email? Same phone? Same name? Matching on name alone is risky because two different people can share one. Matching on email or phone is safer.

A cautious way to do it:

1. Sort by email, then scan for rows that look alike next to each other
2. Mark suspected duplicates with a note in an extra column, such as "dup of row 14"
3. Look at each pair before you delete. Keep the row with more information
4. For anything unsure, leave both

It's better to leave a few duplicates than to merge two real people.

Deal with blanks and junk

Some rows will have a name and nothing else. Decide a rule. Maybe you keep anyone with a phone or email and move the rest to a separate tab called "No contact info." Don't delete them. A name with no details can still be useful to someone.

Some rows are junk: a test entry, "asdf," a competitor's name. Remove those, once you're sure.

Respect who asked off

If someone asked to be taken off your messages, keep them on the list with "No" in the permission columns. Don't delete them. If you delete them and later add them back from another source, you'll message someone who asked you not to.

Only email or text people who have agreed to hear from you. In the US, there are rules for both. If you're not sure where you stand, ask a lawyer or your email or texting service.

Stop the mess coming back

Cleaning once isn't enough. New entries will get typed in the old way.

• Use a form (Google Forms works) so new customers go straight into the sheet in the right columns
• Add dropdowns for fields with a fixed set of answers, like "Source"
• Pick one person who owns the list. If everybody edits it, nobody's responsible
• Review it twice a year. Ten minutes. Remove bounced emails and dead numbers

Mistakes people make

• Cleaning the original. See "make a copy."
• Over-trusting the formula. Always read through a sample of the output.
• Merging different people. Two Mike Smiths are probably two people.
• Making it fancier than it needs to be. If the list does its job with eight columns, stop at eight.

When the list is clean, you're ready for jobs like automating appointment reminders. Without it, they'd have been a mess.