How to Prepare a Customer List in Excel for CRM Import

Rolling out a CRM or an email marketing tool usually starts with the same challenge: importing the customer base that until now lived in one or more spreadsheets. If the data arrives messy, duplicated or inconsistently formatted, the new system will inherit all those problems from day one. This guide explains how to prepare your customer list in Excel so the import is clean and your CRM starts off on the right foot.

Step 1: gather every source

Customer information is rarely in a single file. It is usually spread across:

• The sales team’s list.
• Exports from the invoicing software.
• Online store contacts.
• Each salesperson’s personal sheets.

Gather all the files and merge them into one with the Merge Excel tool on our Excel tools page. Before that, add a “Source” column to each file so you know where each contact came from; it will help you resolve duplicates.

Step 2: define the CRM fields

Check which fields your CRM supports and which are required. Typically:

Field	Example	Notes
First name	Ana	Separate from last name
Last name	Garcia
Email	ana@company.com	Usually required and unique
Phone	+15551234567	With international prefix
Company	Company Inc.
Country	United States	Value from a list
Created date	2026-09-30	ISO format

Rename your file’s columns to match the CRM fields. Many tools map columns automatically when the names match.

Step 3: split combined fields

If the full name is in a single column and the CRM wants first and last names separately, use Data > Text to Columns with a space as the separator or, in Excel 365, the TEXTSPLIT function. Check compound names by hand. The same applies to addresses that mix street, zip code and city.

Step 4: normalize the data

Apply the basic normalization techniques:

• Spaces: TRIM on every text column.
• Names: PROPER so they all start with a capital letter.
• Emails: LOWER and no spaces.
• Phones: a single format with a prefix.
• Countries and states: unified values according to the list the CRM accepts.
• Dates: YYYY-MM-DD format.

Step 5: validate email addresses

A mistyped email is a lost contact and, in email marketing, a bounce that hurts your sender reputation. At least check:

• That they contain exactly one @: =LEN(C2)-LEN(SUBSTITUTE(C2,"@",""))=1.
• That there is a dot after the @.
• Typical domain typos: “gmial.com”, “hotmal.com”, “gmail.con”.

Step 6: remove duplicates

With the data normalized, remove duplicates using the email address as the key. First, sort by last activity date or preferred source, so the most reliable record is kept. Use Data > Remove Duplicates or our online duplicate remover.

If two duplicate records hold complementary information (one has a phone, the other a company), merge them manually before deleting.

Step 7: check consent

If you are going to use the list to send marketing communications, make sure you have a legal basis for it, such as the contact’s consent. Add a column with the consent status and date, and do not import people who have not given it into campaigns. This step is as important as the technical cleanup.

Step 8: remove blank rows and unnecessary data

Delete blank rows, internal columns you will not migrate and notes that have no field in the CRM. Fewer columns means fewer mapping mistakes.

Step 9: export to CSV

Most CRMs import CSV files. Export with our Excel to CSV tool and make sure you use:

• UTF-8 encoding, to keep accented characters.
• The delimiter the CRM expects (usually a comma).
• A single header row.

Step 10: test with a sample

Before importing thousands of contacts, import a test file with 10 or 20 rows. Check that fields map correctly, accents display properly and dates and phone numbers look as expected. Fix whatever is needed and then import the full file.

Common import mistakes

• Mismapped columns: the phone number ends up in the company field because the column names did not match. Review the CRM’s mapping screen carefully.
• Dates read the wrong way round: 03/04 is imported as April 3 instead of March 4. Always use the ISO format to avoid it.
• Broken accents: the file was not saved as UTF-8. Export it again with the right encoding.
• Repeated contacts inside the CRM: the database already had some contacts and the import created copies. Turn on the option to update existing records by email address if your CRM offers it.
• Forgotten tags and lists: if you want to segment later, add a column with the tag or segment before importing; it is much faster than assigning them one by one inside the CRM.

Frequently asked questions

Conclusion

A well-prepared customer import saves weeks of fixes inside the CRM. Gather your sources, map the fields, normalize, validate emails, remove duplicates, check consent and test with a sample before importing everything. Our free tools support you at every step, from merging files to exporting the final CSV.