During an ERP roll-out meeting, a managing director asked the question everyone had in mind: “When will we be completely rid of Excel?” Our answer, “perhaps never”, caused a stir among the management team. With hindsight, we stand by it. The goal is not to eliminate Excel, but to integrate Excel with the ERP properly, so that each does what it does well.
This article explains what must stay in the ERP, what Excel can legitimately handle, the four ways to make them communicate, and the pitfalls of double data entry, which turn a good idea into a source of daily errors.
Why Excel outlives the ERP
An ERP is designed to record transactions: an order, a receipt, a manufacturing order, a stock movement. It does this well, with checks and a history. But planning, simulation, analysis and new products that do not yet have an item record are not transactions. They are lines of reasoning, often provisional, that change shape from one week to the next. Excel is the natural tool for this reasoning, which is why the files reappear a few months after every roll-out.
We developed this point in the article on the limits of ERP systems for planning. The finding holds: Excel and the ERP are not competitors; they do not do the same job.
What must stay in the ERP
A simple rule avoids most conflicts: the ERP is the master of everything that commits the company; Excel only holds what helps people decide.
| In the ERP, and only there | In Excel, fed by the ERP |
|---|---|
| Item master, bills of materials, routings | Bill of materials simulations for a new product |
| Stock and stock movements | Projected stock, stock cover, turnover analyses |
| Customer and supplier orders | Sales forecasts, S&OP, MPS |
| Released manufacturing orders | Proposed manufacturing orders, load scenarios |
| Batches, traceability, quality | Dashboards, KPIs, monthly reviews |
As soon as data from the right-hand column is used to act (releasing a manufacturing order, placing a purchase order), it must go back into the ERP through a controlled import, not by re-keying.
Four ways to integrate Excel with an ERP
1. Exporting from the ERP to Excel
This is the most common flow, with three levels of maturity: a manual export from an ERP screen (fragile, it depends on one person), a scheduled extraction that drops a file into a shared folder every night, and a direct connection through Power Query, ODBC or an API, which refreshes the workbook in one click. In all three cases, the Excel file never modifies the import tab: it only reads it.
2. Importing from Excel into the ERP
Most ERP systems accept CSV files or import templates to create items, manufacturing orders or purchase orders. Good practice: an output tab in exactly the format the ERP expects, checks before export (existing reference, positive quantity, date within the horizon), and a log of what has been imported. Recent ERP systems, Odoo for example, also expose an API that lets you push this data without going through a file.
3. Locked templates
When several people feed the same file, sales forecasts for example, a template with protected cells, drop-down lists and data validation reduces errors. The template is version-controlled, and only one person is responsible for changing it.
4. Checking and reconciliation
This is the most neglected flow. A tab compares what the ERP knows (stock, manufacturing orders, purchase orders) with what the file has decided, and highlights the differences: a proposed manufacturing order never released, a purchase order placed outside any proposal, stock that differs between the two. Without this check, the two systems slowly drift apart, and nobody notices before the stocktake.
The pitfalls of double data entry
Double entry is not a choice: it creeps in when the flows above do not exist. Its symptoms are well known:
- the planner works from yesterday's stock, or last week's;
- a reference typed from memory releases the manufacturing order for the wrong item;
- the quantities in the ERP and in the file do not match, and the Monday meeting is spent deciding “who is right”;
- the person who does the morning copy-paste becomes indispensable, and their absence blocks planning;
- each team keeps “its own” file, and the company ends up with three sales forecasts.
The cost of these symptoms far exceeds that of a connector or a scheduled export. This is the first job to tackle, before trying to replace Excel with the ERP.
Method: audit the Excel files before and after the ERP
When a company relies on Excel files for its planning, a precise assessment is essential, before the ERP roll-out and again afterwards.
- List. A summary table lists every file: name, location, data source, owner, frequency of use, who reads it.
- Qualify. For each file, what does it do that the ERP does not? Is it data entry (it creates data), a calculation (it transforms ERP data) or reporting (it presents data)?
- Compare with the ERP's functions. Overlaps are identified: whatever the ERP can do disappears from Excel.
- Decide for each file. Delete it, keep it as it is, keep it but feed it from the ERP, or replace it with a professional tool.
- Build the architecture around the core. Once the ERP is live, every need it does not cover is handled by an integrated file (flows 1 to 4), never by an isolated one.
The structuring effect should not dictate the organisation
The ERP's structuring effect is often put forward to justify dropping one file or another. It should not decide the internal organisation: the ERP serves the company, not the other way round. If a reorganisation is needed, it should be treated as a project in its own right, independently of the system implementation. And if a file exists because the organisation has a good reason to work that way, the right reflex is to integrate it, not to ban it.
When Excel reaches its limits
Properly integrated, Excel files are a strength: they have often made the ERP roll-out possible by complementing its functions. But they have limits, and these must be acknowledged: recurring bugs, recalculation times, several users getting in each other's way, macros nobody maintains. At that stage, a professional alternative is needed for the file in question, without calling the others into question. In the article how far to go with an Excel MRP we describe the signs that this moment has come.
What we see in the field: Excel and ERP living together
Companies that handle this coexistence well have three things in common. They have named an owner for each file. They have automated the feed from the ERP, even modestly. And they have accepted that a planning file may disappear the day a tool does better, without seeing it as a failure. Conversely, management teams that decreed the end of Excel ended up with hidden files, less well kept than before. The question is not “Excel or ERP”, but “which data is the master, and how does it flow”.
Conclusion: Excel and the ERP, each in its place
Integrating Excel with an ERP means accepting that the two coexist, under clear rules: the ERP is the master of transactions, Excel handles analysis and decisions, and data flows through controlled channels, never by re-keying. A file audit, automated exports, controlled imports and a reconciliation tab are often enough to turn this coexistence into a strength.
For planning and forecasting files that have outgrown their limits, Planiprod (MPS, MRP, load and capacity) and Planiprev (sales forecasting and projected stock) take their data from your Excel workbooks or read the ERP every morning, and leave the ERP as the master of manufacturing orders and purchasing. Find out how Planiprod reads your Excel MPS, or get in touch for a demo.