ERP Data Migration: What to Fix Before You Switch

You’ve sat through the demos, compared vendors, and maybe even signed a contract. The ERP is coming. But here’s the part nobody warned you about: the hardest thing about your ERP data migration isn’t the new system. It’s the data you’re bringing from your old spreadsheets.

Most implementation problems don’t start on go-live day. They start weeks earlier, when someone tries to load years of spreadsheet data into a structured system — and discovers that “data” is a generous term for what they actually have.

Why Spreadsheet Data Doesn’t Just “Move Over”

Spreadsheets are forgiving. An ERP is not.

In a spreadsheet, you can type “Acme Corp,” “ACME Corp.,” “Acme Corporation,” and “acme” in the same column, and everything still works. You can leave fields blank when you don’t feel like filling them in. You can encode critical business logic in a formula that references cells across three tabs — and nobody will complain until the person who built it leaves.

A University of Hawaii study found that 88% of spreadsheets contain at least one error. At a small scale, those errors get caught by the person who built the sheet. At 30 or 40 people, they compound in ways nobody notices — until you try to move that data into a system that expects consistency.

The core problem: ERPs require structured, consistent data. Every customer record needs the same fields filled in the same way. Every product needs a single, canonical name. Every transaction needs to link to the right entities. Your spreadsheets were never built to meet that standard, because they didn’t need to.

This gap — between what your spreadsheets contain and what your new system requires — is where most implementation failures begin.

What Does ERP Data Migration Actually Involve?

ERP data migration is the process of moving your business data from its current home — spreadsheets, email inboxes, shared drives, maybe your ops manager’s memory — into a structured system. For companies coming from spreadsheets, it typically involves five stages:

Audit — Identify every spreadsheet, database, and informal data source your business uses. You’ll find more than you expected.
Clean — Fix the inconsistencies, duplicates, and gaps that spreadsheets tolerate but systems don’t.
Map — Decide which spreadsheet columns correspond to which fields in the new system. Some data maps directly. Some doesn’t exist in the new structure. Some critical data isn’t captured in any spreadsheet today.
Load — Move the cleaned data into the new system, usually in batches.
Validate — Verify that what ended up in the system matches what should be there. This step catches problems that cleaning missed.

The sequence matters. Companies that skip the cleaning stage and jump straight to loading data learn an expensive lesson: garbage in, garbage out applies to ERPs just as much as spreadsheets. The system just makes the garbage harder to ignore.

The Data Audit — Finding What You Actually Have

Before you can clean anything, you need to know what you’re working with. Most businesses underestimate how scattered their data really is — a problem we covered in depth in our piece on data silos.

Start by mapping every data source in your operation:

The obvious spreadsheets — Your main financial tracker, client list, inventory sheet, and project log
The forgotten spreadsheets — That one-off reconciliation file, the pricing calculator your sales lead built in 2022, the PTO tracker in someone’s personal Google Drive
Email threads — Pricing agreements, customer preferences, supplier terms, and informal approvals that never made it into a file
Shared drives and cloud folders — Documents and PDFs that contain data not captured in any spreadsheet
People’s heads — The unwritten rules, customer histories, and process knowledge that exist only as key-person dependencies

For each source, document four things:

What kind of data it contains (customers, transactions, products, financials)
Who owns or maintains it
When it was last updated
Whether other files or processes depend on it

This audit usually surfaces uncomfortable truths. You’ll find three different versions of your customer list. Your “master” product catalog will have 40% more entries than anyone thought. Critical pricing data will turn up in email attachments.

Those discoveries are the whole point. Finding them now — before migration — is dramatically cheaper than finding them after go-live.

Five Data Problems to Fix Before You Migrate

Once your audit is done, you’ll likely find these problems in every spreadsheet-dependent business. Fix them before migration, not after.

1. Duplicate records

The most common issue. The same customer appears as “Johnson & Sons,” “Johnson and Sons,” “J&S Industries,” and “johnson & sons.” The same vendor has three entries with different contact details. The same product has slightly different names across departments.

In a spreadsheet, duplicates are annoying. In an ERP, they break reporting, create billing errors, and make every downstream process unreliable.

What to do: Export your customer, vendor, and product lists. Sort them. Look for near-matches. Pick one canonical name and merge the rest. This is tedious, manual work — and it’s the single highest-value cleanup task you can do.

2. Inconsistent formatting

Phone numbers with dashes, dots, parentheses, or nothing. Addresses in different formats. Dates in MM/DD/YYYY, DD/MM/YYYY, and plain text. Currency amounts with and without symbols.

ERPs need consistent formats to sort, filter, and calculate correctly. A date field with mixed formats will either throw import errors or — worse — silently misinterpret your data.

What to do: Pick a standard format for each field type and enforce it across your data. Most spreadsheet tools can help with bulk reformatting. Do it before export, not after import.

3. Missing and incomplete records

Fields left blank because “everyone knows” the answer. Transaction records without dates. Customer entries missing email addresses or tax IDs.

Some of those “optional” fields in your spreadsheet become required fields in an ERP — especially anything related to billing, tax, or compliance. If you discover this during migration, you’ll need to track down the missing information under time pressure.

What to do: Get the list of required fields from your new system. Check your existing data against that list. Fill gaps now, while the people who know the answers are available and not in the middle of a system transition.

4. Business logic hidden in formulas

Your pricing spreadsheet calculates margins using a formula that references a hidden tab with discount tiers. Your inventory sheet auto-calculates reorder points based on a moving average someone built three years ago. Your financial tracker uses VLOOKUP chains that nobody fully understands.

Those formulas encode business rules that need to be replicated in your ERP configuration — or replaced by the ERP’s own logic. If you don’t document them before migration, you’ll lose institutional knowledge. This is a variant of the process mapping challenge: understanding what you do today before you try to do it differently.

What to do: Walk through each critical spreadsheet with the person who built it. Document what each formula does in plain language. Then decide: does the ERP handle this natively, does it need custom configuration, or should the business rule change entirely?

5. Stale data that shouldn’t migrate at all

Customers you haven’t done business with since 2019. Products you no longer sell. Employees who left two years ago. Test entries and sample data that were never deleted.

Migrating dead data clutters your new system from day one. It inflates record counts, muddies reports, and makes it harder for your team to find what they actually need.

What to do: Set a threshold. If a customer hasn’t transacted in 24 months, archive the record instead of migrating it. If a product has been discontinued, leave it behind. You can always bring historical data over later — but starting clean makes the new system immediately more useful.

What to Migrate and What to Leave Behind

The instinct is to bring everything — every record, every transaction, every historical data point. That instinct is expensive and usually wrong.

Think about your data in three categories:

Active data — always migrate:

Current customers, vendors, and contacts
Open transactions (unpaid invoices, pending orders, active projects)
Current product or service catalog with pricing
Employee records and organizational structure
Active contracts and agreements

Historical data — migrate selectively:

Completed transactions from the last 1-2 years (useful for comparisons)
Financial records required for audit compliance (check retention requirements with your accountant)
Customer interaction history that’s genuinely useful for ongoing relationships

Dead data — archive or discard:

Inactive customers and vendors with no recent activity
Discontinued products and services
Superseded versions of documents and records
Test entries and temporary data

According to McKinsey, teams lose up to 20% of their workweek searching for internal information or tracking down colleagues who can help. Starting your new system with only relevant, clean data means your team spends less time sifting and more time working from day one.

The general rule: migrate what you need to operate on day one, archive what you might need for reference, and leave behind what nobody will search for again.

Building a Migration Timeline That Won’t Derail

Data migration takes longer than anyone expects. Here’s a realistic timeline framework for a growing business moving from spreadsheets:

Weeks 1-3: Audit and inventory. Map all data sources, assign ownership, identify gaps. This can happen in parallel with other implementation prep.

Weeks 3-6: Data cleaning. The bulk of the effort. Your team does the unglamorous work of deduplicating records, standardizing formats, filling gaps, and documenting business logic. Don’t delegate this entirely to the vendor — your people know the data.

Weeks 5-7: Mapping and test loads. Work with your implementation partner to map cleaned data to the new system’s structure. Load a test batch and review results. You’ll find problems you missed during cleaning — that’s expected.

Weeks 7-9: Validation and parallel running. Run your new system alongside your spreadsheets. Compare outputs. Make sure the numbers match. This overlap catches discrepancies before they become real problems.

Weeks 9-10: Final migration and go-live. Load the production-ready data. Verify one last time. Cut over.

Two things make this timeline realistic instead of aspirational:

First, data cleaning is not a one-person job. The people who own the data — your sales team for customer records, your finance team for financial data, your ops team for product and inventory — need to be involved. They’re the ones who know that “J&S Industries” and “Johnson & Sons” are the same company.

Second, getting your team involved in data cleanup gives them ownership of the new system before it launches. That’s not a side benefit — it’s a core part of change management. People adopt systems they helped build.

Frequently Asked Questions

What data needs to be migrated when moving to an ERP?

At minimum, migrate active customer and vendor records, open transactions, your current product or service catalog, and employee data. Financial records required for compliance should also move. Historical data older than 1-2 years can usually be archived rather than migrated, which reduces complexity and cost.

How long does data migration from spreadsheets take?

For a growing business with 10-50 employees, expect 8-12 weeks from audit to go-live. The biggest variable is data cleaning — businesses with years of accumulated spreadsheet data and multiple data owners should plan for the longer end. The actual data loading is usually the fastest step.

Should I clean data before or after migrating to an ERP?

Before. Always before. Migrating dirty data means spending your first months in the new system fixing records instead of learning the system. Cleaning beforehand also costs less — spreadsheet-based cleanup tools are simpler than trying to bulk-edit records inside an ERP.

Can I migrate data myself or do I need a consultant?

For small businesses with straightforward data — one customer list, one product catalog, basic financials — self-migration with vendor guidance is feasible. If you have complex data relationships, multiple interconnected spreadsheets, or critical financial data, an implementation partner reduces risk significantly.

What happens to my old spreadsheets after migration?

Keep them as read-only archives for at least 12 months. They serve as a reference if questions come up about historical data and as a safety net during the transition. After a year, most businesses find they never open them again. Don’t delete them — just move them to cold storage.

How Tier2 Keel Supports Data Migration

The data preparation process described above is built into how Tier2 approaches every implementation. With 11+ years of consulting experience — helping businesses move from spreadsheets, legacy systems, and disconnected tools — Tier2 treats data migration as a first-class part of the project, not an afterthought.

Tier2 Keel is designed for the kind of growing business making this transition. It enforces the data consistency your spreadsheets never could: canonical customer records, linked transactions, and business rules that live in the system rather than in formulas nobody understands.

Because Tier2 started as a consulting firm before building its own products, the team understands that software is only half the equation. The other half is getting your data — and your people — ready for the change.

See how Keel works or talk to our team about your migration.

Pick one spreadsheet — your most critical one — and run it through the five-problem checklist above. Count the duplicates, the blank fields, the inconsistent formats. That single exercise will tell you more about your migration readiness than any vendor demo.