Skip to content

How to Clean Up a Customer Spreadsheet for CRM Import

A messy spreadsheet can cause duplicate records, failed imports and bounced emails the moment you load it into a CRM or mailing tool. The fix is a systematic clean-up before you import. This guide walks you through the process step by step, with a practical checklist you can use for any customer list.

GigBlend8 min readUpdated

Why a clean spreadsheet matters before importing

CRMs and email marketing platforms are not forgiving. A stray comma, a phone number formatted three different ways or a missing email address can stop rows importing, or leave you with a database you cannot trust. Mailing tools generally skip or reject rows where the email address is missing or badly formatted, and most CRM import tools ask you to match each column to a field, so columns you cannot match will not come across.

Cleaning the spreadsheet first also protects your sender reputation. If you import a list with old, misspelled or fake email addresses into a mailing tool, your bounce rate rises and future emails are more likely to land in spam folders. Time spent tidying the data before you import saves far more time untangling problems later.

Start with a backup, then work on a copy

Before you change anything, save the original file exactly as it is. Give it an obvious name, such as customers_original_Nov2026.xlsx, and keep it somewhere safe. If you accidentally delete a column or merge the wrong cells, you want a clean fallback.

Then create a working copy. This is the file you will edit. Open it and take a quick look at what you have: how many rows, how many columns, and which columns actually contain useful data. If the spreadsheet has several sheets, note which ones hold customer data and which hold reference notes, old price lists or something a colleague added years ago and forgot about. Delete any sheet that is not customer data in your working copy.

Fix the column headers

Every column needs a clear, consistent header that a CRM or mailing tool can recognise. Most import tools ask you to map your columns to their fields, and that is far easier when your headers are descriptive and consistent.

A few practical tips:

  • Give every column a header. If a column has no header, add one now, even if it is just "Notes".
  • Use plain, descriptive names. "First Name" or "First_Name" are both fine; "CustName1" and "Name_OLD" are not.
  • Remove columns you will never use. If someone added a "Favourite Colour" column for a campaign that never happened, delete it.
  • Check for merged cells in the header row. Merged cells commonly cause import problems; unmerge them and repeat the header text in each cell instead.

Standardise the data in each column

This step takes the most time, but it also prevents the most problems. Work through each column one at a time and make every entry follow the same format.

Names. Split full names into separate first name and last name columns. Excel's Text to Columns feature or Google Sheets' Split text to columns can do this, or you can use a formula. Capitalise the first letter of each name. Move titles like Mr, Mrs and Dr out of the name fields into a separate title column if your CRM has one.

Email addresses. Convert all email addresses to lowercase. Remove spaces before or after the address; the TRIM function in Excel and Google Sheets handles this quickly. Scan for obvious typos such as "gmial.com", "hotnail.com" or missing @ symbols. You will not catch every typo, but fixing the obvious ones now prevents hard bounces later.

Phone numbers. Pick one format and apply it to every number. For UK numbers, decide whether you are using 07123 456789, +44 7123 456789 or 07123456789, and stick to it. Remove any text notes that have crept into the phone column, such as "ask for Dave" or "works 9–5". If those notes are useful, move them to a separate Notes column.

Postcodes and addresses. UK postcodes should be written in capitals with a space before the last three characters: SW1A 1AA, not sw1a1aa. If you have full addresses in a single column, consider splitting them into separate columns for address line 1, town or city, county and postcode. Most CRMs handle separate address fields better than one block of text.

Dates. If your spreadsheet contains dates, pick one format. DD/MM/YYYY is the usual UK format. Mixed date formats are a common cause of import errors, because a tool cannot tell whether 03/04/2026 means 3 April or 4 March. Check which date format your CRM expects before you import.

Find and remove duplicate rows

Duplicate customers create duplicate records in your CRM, which leads to confused contact histories, double-sent emails and a database you cannot rely on. Excel has a Remove Duplicates tool on the Data tab, and Google Sheets has one under Data, then Data cleanup.

Before you remove anything automatically, decide what counts as a duplicate. Two rows with the same email address are almost certainly the same person. Two rows with the same name but different email addresses might be different people, or the same person who changed their email. When in doubt, mark the row for review rather than deleting it. A column labelled "Duplicate?" with Yes or No, or a fill colour, works well.

Fill in gaps and flag missing data

Once you have standardised and de-duplicated the data, scan each column for empty cells. A missing first name is not the end of the world, but a missing email address means a mailing tool cannot use that record at all.

Decide what to do with each gap:

  • Missing email addresses: delete the row or move it to a separate "unusable" sheet. You cannot email someone without an email address, and a blank email field will either be skipped or create a useless record.
  • Missing names: if you have the email address and the customer is worth keeping, leave the name blank rather than guessing. You can fill it in later.
  • Missing phone numbers: this matters more for a CRM used by a sales team than for an email list. If phone numbers are important to your work, flag the rows and follow up separately.
  • Missing postcodes: if you plan to group customers by location, a missing postcode limits what you can do. Flag it and decide later whether it is worth asking the customer.

A practical checklist before you import

Use this checklist to review your spreadsheet before you load it into any CRM or mailing tool:

  1. Have you saved the original file and created a separate working copy?
  2. Does every column have a clear, descriptive header with no merged cells?
  3. Are all email addresses lowercase, trimmed of extra spaces and free of obvious typos?
  4. Are all phone numbers in one consistent format?
  5. Are all UK postcodes in capitals with the space in the right place?
  6. Are all dates in the same format?
  7. Have you removed or flagged duplicate rows?
  8. Have you dealt with rows that have missing email addresses?
  9. Have you deleted any columns and sheets you do not need?
  10. Have you saved the file in a format your target tool accepts, such as CSV or XLSX?

Work through each item in order. If the answer to any question is no, go back and fix it before you continue.

Need copy for your business as well?

Cleaning a spreadsheet is not complicated, but it is repetitive, and the steps above are designed so you can do it yourself. If your business also needs written copy, GigBlend ([gig-blend.com](/)) is a UK service where AI agents write business copy and documents, and a second AI model checks the work before delivery. There is no human writer. Its Basic website copy costs £40.95 all-in for a landing page written from the facts you give, delivered in 2 days with one revision and refunded in full if late. You can see a real, unedited sample at gig-blend.com/samples to judge the quality before you order, and order at gig-blend.com/gigs/website-copy.

Questions people ask

Can I clean a spreadsheet with thousands of rows by hand?
You can, but you will need to work differently. For large lists, use built-in tools like Remove Duplicates, Find and Replace and Flash Fill in Excel, or the equivalents in Google Sheets. Sorting each column A to Z and Z to A is a quick way to spot problems, because blank cells, odd formatting and unusual values tend to collect at the top or bottom. For very large lists, filtering each column to show only blanks, or only cells that do not match your expected format, helps you clean in bulk.
What file format should I save the spreadsheet in before importing?
CSV (comma-separated values) is the most widely accepted format for CRM and mailing tool imports. Some tools also accept Excel files: HubSpot's import tool, for example, accepts .csv, .xlsx and .xls files with a single sheet. Others, such as Salesforce's Data Import Wizard, expect CSV. Check your tool's import help pages to confirm. If you save as CSV from Excel, only the active sheet is saved, so make sure you are on the right tab before you export.
What if my spreadsheet contains data I collected without consent?
This is a legal question, not just a formatting one. Under UK GDPR you need a lawful basis to hold and use someone's personal data, and the Privacy and Electronic Communications Regulations (PECR) set extra rules for marketing emails and texts. In most cases you need the person's consent to send them marketing emails, with a limited exception for existing customers who have been given a simple way to opt out. If you collected email addresses by scraping websites or bought a list, do not import them into a mailing tool. The Information Commissioner's Office (ICO) has guidance on direct marketing at ico.org.uk ↗. When in doubt, only import contacts who have clearly agreed to hear from you.

More guides

How to Clean a Customer Spreadsheet for a CRM · GigBlend