Escaping Spreadsheets: The 5-Step Migration to a Real CRM

A 12-person sales team imported their spreadsheet directly into their new CRM. Not a cleaned, consolidated version, but the working file: the one with 11 different date formats, three separate tabs for different regions, and a "maybe" column that nobody remembered creating. The import finished in four minutes.

Three months later, they were still untangling it. Four thousand duplicate contact records. Deals linked to companies that had three different spellings. Phone numbers in the "email" column. Notes that had ended up in the wrong field because the column headers didn't match the import template.

The migration itself wasn't the hard part. The preparation they skipped was.

This guide covers the 5-step process that makes spreadsheet migrations boring — meaning they go exactly as planned. Before you start, preparing your data before you migrate anything covers the strategic decisions — what to include, what to leave behind — that shape every step below.

---

Why Spreadsheet Migrations Are Uniquely Messy

A CRM enforces structure. Spreadsheets don't. That's the entire problem.

Over months or years, a sales team's spreadsheet accumulates:

According to Gartner's sales technology research, fewer than 37% of salespeople's time is spent actually selling — and a significant portion of the rest goes to data entry and CRM management tasks that legacy spreadsheet systems make slower and more error-prone.

• Multiple date formats — "Jan 12, 2024," "2024-01-12," "1/12/24," and "last week" all in the same column
• Inconsistent naming — "Acme Corp," "Acme Corporation," "ACME," and "Acme" as four separate companies
• Structural inconsistency — Deals and contacts mixed in the same rows, or contacts in one file and deals in another with no linking field
• History in the wrong place — Notes in a "notes" column on the deal, but also in email threads, also in a separate "history" tab nobody maintains
• Multiple source files — The main spreadsheet, plus Sarah's personal tracking file, plus the CSV export from the email tool, plus the list from the conference last year

None of these are problems in isolation. Together, they mean your "one import" is actually a data reconstruction project. The teams that succeed at this are the ones who treat it that way upfront.

---

Step 1: Consolidate All Spreadsheets Into One Master File

Before you clean anything, find everything. Most teams underestimate how many spreadsheet files exist.

Where to look:

• Shared drives (Google Drive, SharePoint, Dropbox)
• Personal drives belonging to current and former sales reps
• Email attachments — search for "contact list," "leads," "prospects," "pipeline"
• CRM or tool exports — if you previously used a free trial of another CRM, export it
• Marketing tools — email platform subscriber lists, form submission exports
• Conference and event lists — often imported once and then forgotten

Create an inventory:

Merge strategy:

Combine all files into one master spreadsheet. Add a "source_file" column that records where each row came from. That's your audit trail if any records need to be traced back later. Don't delete duplicates yet. Just get everything into one place.

Use a common key to identify potential duplicates before merging. Email address is usually the best key for contacts. Company name works for accounts, but only after normalization (see Step 3). The Wikipedia article on data deduplication covers the matching and merge strategies that apply directly to contact record consolidation. The full deduplication methodology — including how to handle fuzzy matches — is in data cleaning: deduplication, normalization, and enrichment.

---

Step 2: Define Your CRM's Data Model First

Most spreadsheet migration mistakes happen because people start cleaning before deciding what the data should look like when it arrives.

Your CRM has a specific structure. Before you clean a single row, know that structure.

Standard CRM objects:

• Contact — Individual person (first name, last name, email, phone, job title)
• Account — Company or organization (name, website, industry, size)
• Deal/Opportunity — A sales process (deal name, value, stage, close date)
• Activity — A logged interaction (call, email, meeting, note)

Map your spreadsheet columns to CRM fields:

Take your master spreadsheet and create a column mapping. For every column in your spreadsheet, decide:

• Which CRM object does this belong to (Contact, Account, Deal, Activity)?
• Which field does it map to?
• If it doesn't map to any existing field, should it become a custom field?

Anything that doesn't fit a CRM field cleanly is a decision: create a custom field, put it in a notes field, or discard it. Make these decisions before cleaning — not during. Mapping fields between legacy and new systems gives you a framework for handling exactly these edge cases systematically.

---

Step 3: Clean the Master File

With your column mapping defined, clean the master file. Work through each issue category systematically.

Deduplication:

Identify duplicates using email as the primary match key. Where email is missing, use first name + last name + company name. Merge duplicates rather than deleting. The merged record keeps the most complete version of each field.

Checklist: things to clean

• Standardize date format to YYYY-MM-DD (or whatever your CRM accepts)
• Normalize phone numbers to a consistent format (+1XXXXXXXXXX or (XXX) XXX-XXXX)
• Standardize company names (pick one spelling per company; apply it consistently)
• Remove leading/trailing spaces from all text fields
• Convert all text to consistent capitalization (Title Case for names, all-caps for states)
• Check email format — look for missing @ symbols, common typos like ".cmo" instead of ".com"
• Remove duplicates (keep the most complete record)
• Resolve the "deal value" column — numbers only, no currency symbols, no commas
• Map stage values to your new CRM's exact stage names
• Check that every Deal row has a linked Contact (by email)
• Add a "migration_source" column with the value "spreadsheet_migration_2024"

The last item is important. Tagging imported records with a migration source field lets you filter for imported data later, run a batch cleanup pass if needed, and distinguish pre-migration records from ones the team adds after go-live.

What to do with blanks:

Decide upfront which fields are required in the CRM (usually: Contact email and Account name). Records missing required fields will fail import. Either find the missing values, move those records to a "needs review" tab, or accept that they won't be imported in this pass.

---

Step 4: Do a Test Import With 50 Records

Don't import everything at once. Run a test import with 50 records that represent the full messiness of your data, including edge cases.

How to pick the 50 records:

• 10 clean, straightforward records
• 10 records with unusual characters in names (accents, apostrophes, hyphens)
• 10 records with no company linked
• 10 records with a Deal row alongside the Contact
• 10 records from the most problematic source file

What to check after the test import:

1. Did all 50 records import without errors?
2. Do Contact names display correctly (no encoding issues)?
3. Are Contacts linked to the right Accounts?
4. Do Deal records exist and show correct stage and value?
5. Do Activity records (notes, last contact dates) appear on the right Contacts?
6. Does the "migration_source" field appear correctly on all records?

If any of these checks fail, diagnose the cause and fix the master file before running the full import. Common causes:

• Date format mismatch between your spreadsheet and the CRM's expected format
• Stage names in the spreadsheet that don't exactly match the CRM's stage options
• Character encoding issues (common with Excel files containing special characters)
• Field length limits — some CRM fields cap at a certain character count

Fix, re-run the 50-record test, and repeat until it's clean. This step should take 1-3 iterations. Each iteration takes 15-30 minutes. It's not wasted time — it's the time that makes the full import boring. For higher-stakes migrations, testing with a shadow import extends this concept to a full production-like environment before cutover.

---

Step 5: Full Import and Immediate Verification

Once your 50-record test runs clean, run the full import.

Import settings to verify before running:

• Duplicate handling: set to "skip" or "merge," not "create new" — you already deduped
• Field mapping: confirm the column-to-field mapping looks identical to what worked in the test
• Owner assignment: decide whether all imported records default to one owner or use a spreadsheet column for assignment
• Error handling: use the CRM's error log option so any failed rows are reported, not silently skipped

Post-import verification:

Run these checks immediately after the import completes:

What to do with import errors:

The CRM will report rows that failed — usually as a CSV file of the failed records with an error reason column. Fix those rows and run a follow-up import for just the failed records. Don't ignore them.

---

The 5-Step Summary

Most of the time is in Step 3. That's correct. The import itself should take minutes. The preparation is the work.

---

Common Pitfalls

Importing directly from the original spreadsheet. The original file has never been standardized. It fails in predictable ways. Always work from a copy, always clean first.

Mixing contacts and leads in one file. If your CRM separates Contacts from Leads (or Contacts from Prospects), importing a mixed file creates orphaned records. Separate them before import.

Not tagging imported records. Six months after migration, you'll want to run a data quality pass on just the imported records. Without the "migration_source" tag, you can't distinguish them from new records. Harvard Business Review's research on data governance emphasizes that data provenance tracking — knowing where each record came from — is a foundational practice for maintaining CRM data quality over time.

Declaring success before verifying. Import completion doesn't mean import success. Row counts and sample spot-checks take 30 minutes and catch problems before the sales team encounters them.

---

What to Do Next

Once the import is verified, two things matter immediately.

First, set up duplicate detection rules in your new CRM before the team starts adding records. Spreadsheet migrations almost always produce some duplicates that the dedup pass missed. Built-in duplicate detection catches new ones before they accumulate.

Second, if you're also migrating deal history or notes from email threads, read handling historical activities, notes, and emails before adding that data to the same CRM. A McKinsey study on digital transformation in sales found that companies that successfully transition from spreadsheet-based to CRM-based selling improve pipeline visibility and forecasting accuracy by as much as 40%.

And if you want to run a test import before committing to the full migration, testing the migration with a shadow import walks through a more structured validation process for higher-stakes migrations.

For the foundational data decisions — what to migrate, what to leave behind, and how to scope the whole project — preparing your data before you migrate anything covers the decisions that shape everything downstream.

---

Learn More

• Preparing your data before you migrate anything
• Data cleaning: deduplication, normalization, enrichment
• Testing the migration with a shadow import
• Handling historical activities, notes, and emails
• CRM data model design: custom fields, objects, and relationships
• Day-one tool setup for new sales reps