How to import contacts from Excel to a CRM (no mess)

To import contacts from Excel to a CRM, clean the sheet until each row is one contact and each column holds one kind of data. Put phone numbers in international format, merge duplicates, save the sheet as a CSV file, map each column to a CRM field and test with 20 rows before you import the rest. The import itself takes minutes. Most of the work is the clean-up.

These steps work for any CRM. They assume a real sales sheet, with merged cells, colour codes, three tabs and history in the notes column.

What to move and what to leave behind

Move these:

• Every open deal, with its stage, value, owner and next step.
• Current customers (or clients) and anyone you have spoken to in the last 12 months.
• Do-not-contact requests, clearly marked, so nobody messages those people by mistake.

Leave these:

• Rows with no phone number and no email. You cannot follow them up.
• Test rows, totals rows, blank rows and formula columns.
• Old leads that never replied and that you have no reason to keep.

That last point is a legal one too. The UK’s Information Commissioner’s Office says personal data must not be kept for longer than it is needed. Nigeria has its own Data Protection Act 2023. Check the rules where you sell.

Clean the sheet first, step by step

Each step fixes a problem that an import tool will not fix for you.

1. Work on a copy. Microsoft’s data cleaning guide starts the same way, with a backup copy of the original data in a separate workbook.
2. Keep one header row, in row 1. Delete title rows (“Q3 pipeline, Lagos office”), blank rows and totals rows.
3. Unmerge cells, then fill down. Say “Delta Freight” is merged across three contact rows. After you unmerge, the name sits on the first row only. Copy it down to the other two.
4. Turn colour into a column. Green for won and red for “call back” disappear on export. Microsoft says of text formats such as CSV: “all formatting will be removed”. Filter by each colour and type its meaning into a new “Status” column.
5. Put everything on one tab. A CSV file saves only the active sheet. Before you stack the tabs, add a “Source tab” column (“March event”, “Amaka’s leads”) so each tab’s meaning is kept.
6. Keep one piece of data per cell. Split “Tolu, Baobab Foods” into a name and a company. Move a second phone number into its own column.
7. Pull the history out of the notes. Keep the notes column. Beside it, add “Next step” and “Next step date”, and fill them in for open deals.
8. Replace owner initials with full names. “AM”, “TL” and “S” become the names of the CRM users who will own those records.
9. Make dates unambiguous. 03/04/2026 is 3 April in London and Lagos, and 4 March to software set to US dates. Use 2026-04-03, or the format your CRM asks for.

If you would rather start from a clean layout than repair an old one, download the free Spreadsheet-to-Pipeline Template (Excel) and copy your cleaned rows into it.

Fix phone numbers and duplicates

Phone numbers

A CRM that sends WhatsApp messages needs every number in one format. WhatsApp’s help centre defines it as a plus sign (+), then the country code, the city code and the local number, with any leading 0 removed. Meta’s developer documentation explains what goes wrong otherwise. If the plus sign is left out, the country calling code of your own business number is added to the front of the customer’s number, and the message may go undelivered or to the wrong person. (Both pages checked on 2 October 2026.)

Say your WhatsApp business number is a UK one and a Lagos customer is saved as 0803 000 0000. The UK code 44 can be put in front of it, and the message goes to the wrong number or nowhere.

1. Format the phone column as Text before you edit it. Microsoft confirms that Excel removes leading zeros automatically and turns large numbers into scientific notation (such as 1.23E+15). Text formatting stops that. It does not bring back zeros that have already gone.
2. Add a “Country” column. Do not guess from the first digits. UK mobiles start 07 and so do some Nigerian mobiles (0703 and 0706, for example). Both are 11 digits long.
3. Remove spaces, brackets and hyphens with Find and Replace.
4. Swap the leading 0 for the country code. For a Nigerian row that starts with 0, with the number in cell B2, use the formula below in a new column. Change +234 to +44 for UK rows. Then paste the results back as values.
5. Add the plus sign where it is missing. 2348030000000 becomes +2348030000000.
6. Fix the “+44 (0)” pattern. After step 3 it reads +4407700900123. Replace “+440” with “+44”, and “+2340” with “+234”.

="+234"&MID(B2,2,20)

Duplicates

Excel’s Remove Duplicates tool only catches values that are spelled the same way. “Delta Freight Ltd”, “Delta Freight Limited” and “Delta Frieght” are three rows to Excel and one customer to you.

1. Match on something that does not vary. Use the cleaned phone number, or the email address in lower case. Names are too easy to spell two ways.
2. Highlight before you delete. On the phone column, then the email column, use Conditional Formatting, Highlight Cells Rules, Duplicate Values. Microsoft gives similar advice: filter for unique values first and check the result before you remove any duplicates.
3. Merge by hand. Keep the latest stage and the named owner. Join the notes from both rows into one cell.
4. Sort by company name and read down the list. This catches spelling variants.
5. Switch on the CRM’s duplicate check for the import. Many import tools can match on email or phone and update the existing record. Choose that over “create new”.

Validity’s State of CRM Data Management in 2024 surveyed 631 CRM administrators in the US, UK and Australia. Nearly 25% said less than half of their CRM data could be deemed accurate and complete.

Map columns to CRM fields

Mapping tells the CRM where each column goes. Do it by hand, one column at a time. Automatic matching can guess wrong on headers such as “Status” or “Ref”.

Set up two things before you map. First, create any custom fields in the CRM (for example “Course of interest” for Atlas Schools), or there is nowhere to send that column. Second, agree your stage names. Our guide to sales pipeline stages for small teams can help if “Status” currently holds 14 different phrases.

Many CRMs store contacts, companies and deals as separate records. One sheet row may create all three, so read your CRM’s import guide.

Run a test import of 20 rows

Choose the 20 most awkward rows, and skip the tidy ones at the top. Include:

• one Nigerian and one UK phone number
• a name with an accent or an apostrophe (Zoë, O’Brien, Adébáyọ̀)
• a row with very long notes and a row with no email
• a known duplicate pair
• a won deal, a lost deal and an open deal

Save those rows as a CSV file (choose the UTF-8 option if your Excel offers one, so accented names are kept). Tag the batch “test-import” so you can delete it afterwards. Then check in the CRM:

1. 20 rows went in and 20 records came out (or 19, if the duplicate pair merged as planned).
2. Every phone number starts with +.
3. Stage, owner and value sit in the right fields.
4. Dates read correctly. 3 April has not become 4 March.
5. Notes and names are complete, accents included.

Make corrections in the sheet, delete the test batch and run it again. When all 20 are right, import the rest and compare the record count with your row count.

Keep the old sheet as a read-only backup

1. Rename it with the date, for example “Pipeline ARCHIVE 2 October 2026”.
2. Change sharing so everyone can view it and nobody can edit it.
3. Add a line in row 1: “Frozen on 2 October 2026 at 09:00. The live pipeline is in the CRM.”
4. Tell the team the switch time. After it, every update goes into the CRM only.

Avoid running both for “a few weeks”. Reps update whichever one is open, and neither ends up complete. Use your first pipeline review meeting after the switch to check that every open deal has an owner and a next step date.

Frequently asked questions

How do I avoid duplicate contacts when importing from Excel?

Remove duplicates in the sheet first, matching on phone number in international format or on email in lower case. Then switch on your CRM’s duplicate check during the import and set it to update existing records.

What format should my Excel file be in?

CSV is the safest choice, because it is plain text with no formatting to misread. Use one tab, one header row in row 1, no merged cells and no formulas (paste them as values).

What data should I not migrate?

Leave out rows you cannot contact (no phone and no email), test and totals rows, formula columns and old leads you have no reason to keep. Do carry across do-not-contact requests.

Do I need IT support to switch?

For a small sales team, usually not. The import needs the person who knows the sheet best. You may want help from whoever manages your email and phone accounts when you connect those channels afterwards.

What happens to my spreadsheets after switching?

Keep the final version as a read-only archive, dated and clearly labelled, for at least one full sales cycle. Nobody updates it after the switch time. You will still use spreadsheets for one-off analysis.

Sources

• WhatsApp Help Center, About international phone number format (checked 2 October 2026)
• Meta for Developers, WhatsApp Cloud API, Phone Numbers (checked 2 October 2026)
• Microsoft Support, Top ten ways to clean your data
• Microsoft Support, Merge and unmerge cells
• Microsoft Support, Save a workbook to text format (.txt or .csv)
• Microsoft Support, Keeping leading zeros and large numbers
• Validity, The State of CRM Data Management in 2024
• Information Commissioner’s Office, Principle (e): Storage limitation (checked 2 October 2026)
• Nigeria Data Protection Commission, Resources
• Wikipedia, Telephone numbers in Nigeria
• Wikipedia, Telephone numbers in the United Kingdom