Excel to ERP migration: a practical guide

Recognise the point where Excel stops scaling

Spreadsheets fail gradually and then suddenly. The gradual phase looks like small annoyances: two versions of the stock file, a formula that breaks when a row is inserted, a customer's balance that has to be checked in three sheets. The sudden phase is a corrupted workbook before a shipment, a departed employee whose macros nobody understands, or an audit that cannot be reconciled.

The clearest signals that it is time to move are structural rather than technical. More than one person needs to edit the same data at the same time. Stock, orders and money live in separate files that no longer agree. Approvals happen outside the file — by phone or chat — and are not recorded anywhere. Reports are assembled by hand and are stale on arrival. Any one of these is manageable; two or three together mean the business has outgrown the tool.

Start with what Excel is genuinely good at. Analysis, quick modelling and one-off calculations remain its strength. Being the shared, concurrent, auditable system of record for a growing company is not.

• Concurrent editing by several people on the same data
• Stock, orders and money in separate files that disagree
• Approvals and history that live in chats and memory, not in the file

Inventory the spreadsheets and the logic hidden inside them

Before any migration, list every spreadsheet that runs the business: what it holds, who owns it, who edits it, how often, and which other files it feeds. Most companies are surprised by the number. Include the ones on individual laptops and in personal Google Drive folders — those are usually the most critical and the least protected.

Then document the logic. Formulas, lookups, macros and manual rituals ('every Monday Rahim copies column F into the summary sheet') encode business rules that the ERP will need to reproduce or deliberately replace. Write those rules down in plain language, and note which are genuine policy and which are workarounds for a spreadsheet limitation.

This inventory becomes the scope of the migration. It also tells you which spreadsheets can simply be retired, which become ERP reports, and which contain master data that must be migrated with care.

Clean and standardise before you map

Data cleaning is the longest and least glamorous phase of any Excel to ERP migration, and skipping it is the most common cause of a rocky go-live. Typical problems: the same customer spelled four ways, item codes that mean different things in different sheets, units of measure mixed within one column, dates in three formats, and 'notes' fields carrying information that should have been structured — sizes, colours, payment terms.

Work through master data first: items or SKUs, customers, suppliers, warehouses, chart of accounts. Agree a naming convention and a single owner for each master. De-duplicate ruthlessly and archive what is dead. Then reconcile balances: opening stock per location, receivables and payables per party. These figures must tie to the accounts before they enter the ERP, because every report afterwards will build on them.

Do the cleaning in a copy of the data, keep a log of every rule you applied, and involve the people who created the spreadsheets — they know why row 4,312 looks strange.

Map fields, decide what history to bring, and rehearse the import

With clean data, mapping is straightforward: each spreadsheet column maps to an ERP field, a lookup or nothing. Document the mapping in a simple table so it can be reviewed by the business, not only by the technical team. Decide explicitly what happens to columns that have no home — usually they become a note, a custom field, or they are dropped after confirmation.

History is a judgment call. Master data and opening balances always migrate. Open orders, unpaid invoices and current stock lots always migrate. Years of closed transactions are useful for reporting but expensive to clean; a common compromise is to import a summarised history and keep the original spreadsheets read-only for reference.

Rehearse the import at least once on a test environment with the full data set. Compare totals — item counts, stock value, receivables — between the spreadsheets and the ERP. Fix the mapping, repeat, and only then plan the real cut-over.

• One mapping table, reviewed by business owners
• Master data, balances and open transactions always migrate; closed history selectively
• At least one full rehearsal with reconciled totals before go-live

Go live in phases and run parallel briefly

A phased go-live lowers risk. Start with the area whose spreadsheet hurts most — often inventory or sales — and move it into the ERP while the rest continues in Excel for a few more weeks. Choose a cut-over moment with low activity, freeze the spreadsheets at that point, import, and switch.

Run the old and new in parallel for a short, defined period — a week or two — comparing key figures daily. Parallel running is tiring, so keep it short and stop as soon as the numbers agree consistently. Continuing indefinitely 'just in case' is how teams end up maintaining both.

Then retire the spreadsheets visibly. Make them read-only, move them to an archive folder and announce it. Excel does not go away — it becomes the analysis layer that reads from the ERP — but the system of record must be unambiguous.

Keep the flexibility your team liked about Excel

The biggest objection to leaving Excel is loss of flexibility, and it is a fair one. A good ERP migration answers it in three ways: exports and live views so that analysts can still pull data into spreadsheets; role-based screens that are as quick to use as the sheet they replace; and a clear change process so that new fields or reports can be added in days, not months.

Training should be by role and in the team's language, with the migrated data on screen. People trust a system faster when they see their own customers and items in it. Plan hypercare after each phase — someone answering questions the same day and a visible list of small improvements.

Finally, treat the migration as the moment to introduce a few habits that spreadsheets never enforced: mandatory fields, approval steps and an audit trail. These are the reasons you migrated; make sure they are switched on from day one.