BlogExcel and data
Excel to ERP: how to move your data without stopping work.
Moving from Excel to ERP is mostly data work: what to move, how to clean it and when to switch the source of truth. Here are six steps in the right order.
1. Gather every file in one place
Start with a list of the spreadsheets the company runs on. There are usually more than the owner thinks.
For each file, note who fills it in, how often and what it is for. Mark which file is the source of truth and which are copies.
- Item master and prices.
- Customers and suppliers with payment terms.
- Stock by warehouse.
- Open orders, deliveries and unpaid invoices.
- Recipes or bills of materials, if you manufacture.
Don’t forget the hidden sources: the notebook in the warehouse, the notes on the wall, the sales team’s personal sheets. Prices and agreements nobody else knows often live there.
The inventory of files takes a day or two and is the cheapest step in the whole project.
2. Decide what moves and what stays in the archive
Not everything has to go into the new system. Move only the data you will use from now on.
Master lists, open orders and opening stock are a must. Sales history from previous years can stay in an archive file if you don’t need it in reports.
- Must move: items, customers, suppliers, prices and opening stock.
- Must move: open orders, expected deliveries and unpaid invoices.
- Optional: sales history, old quotes, closed projects.
- Doesn’t move: obsolete items, inactive customers, working copies.
Open documents matter most. An order accepted in Excel and still in progress has to continue in the new system without a break.
If you do move history, decide how far back. For most reports, one or two years is enough.
Rule of thumb: if nobody has opened a file in a year, it probably belongs in the archive.
3. Clean the master lists before the move
Messy data in Excel becomes messy data in ERP. Cleaning is the most tedious step and the most important one.
For example, in a sheet with 1,200 rows you might find the same item as “Box 30x20”, “box 30x20 brown” and “BOX-3020”. In the system that has to be one code with one stock level.
- Give every item a unique code.
- Standardise units of measure: pieces, kilograms, metres.
- Merge duplicate customers and suppliers.
- Delete items that don’t sell and have no stock.
- Check prices against the latest invoices.
For example, a code can carry the group and size: “BOX-3020-B” for a brown 30 by 20 box. What matters is that one rule applies to all items.
Cleaning is also a good time for decisions you’ve been putting off. Such as whether two similar items are really different, or should sell under one code.
Clean a copy of the file and keep the original. That way you can always check where a number came from.
4. Build a field mapping table
Excel columns rarely match ERP fields. Build a simple table: column in the old file, field in the new system, conversion rule.
This is where the details that later cost hours get settled. How dates are written, which fields are required, what happens to empty cells.
Watch out for numbers stored as text and for decimal separators. “1.234” in one file can mean one thousand two hundred and thirty-four, and in another just over one.
Run a trial import with a small slice of the data. For example 50 items and 10 customers. Check them on screen before you move everything.
5. Pick a switch-over day and freeze the old files
The riskiest period is when people work in both Excel and ERP. They enter everything twice, the data drifts apart and nobody knows which is right.
Pick a switch-over day, ideally at the start of a week or month. The evening before, count the warehouse and enter opening stock.
If you ship a lot, pick a day with fewer shipments. Open orders, deliveries and unpaid invoices are reconciled the day before.
Tell everyone the date at least two weeks ahead. A surprise is the surest way to keep people working the old way.
From that day the old files are read-only. If anyone keeps writing in them, double entry comes back.
Example: a trading company with three warehouses
For example, a trading company with about 1,500 items and three warehouses decides to move to ERP. The numbers are hypothetical.
- Week one: inventory of files. They find eleven spreadsheets, four of which are copies.
- Weeks two and three: clean-up. Active items come down to about 1,100 and duplicate customers are merged.
- Week four: trial import of 50 items, on-screen check and fixes to the mapping table.
- Week five: stock count on Friday, full import over the weekend, go-live on Monday.
Notice that the clean-up takes the most time, not the import itself. Plan it as a separate task with an owner.
6. Check every day for the first two weeks
After go-live, set aside half an hour a day to reconcile. Compare system stock with a few real shelves, and open orders with what the sales team knows.
Log every mismatch and its cause. After two weeks you’ll see whether the problem is in the data, the configuration or the habits.
Don’t worry about the first mismatches. They are expected and show exactly where the old data was wrong.
If you’re not sure it’s time yet, look at the seven signs you’ve outgrown Excel.
How we approach it
Moving customers, items and price lists is part of the implementation, not a surprise at the end. Customers and suppliers are imported from Excel or CSV, with column mapping and a check for duplicates. The data stays yours, and we hand it over in an open format whenever you ask. See how the inventory module looks.