Skip to content
caadoo
de

Replacing Excel with an internal tool, step by step

Plenty of companies depend on one Excel file that nobody dares to touch, and the usual plan is to replace it with new software in one go someday. That plan tends to break things. This article shows how to tell when a spreadsheet has become a system, what to pin down before you change anything, and how to move it into a small internal tool step by step.

By T. Krause7 min read

Take a sheet metal company with 45 people. Every quote it sends is priced in a file called Calculation_2019.xlsx. It holds material prices, surcharges, machine hours and a hidden tab with discounts for three large customers. The head of sales built it. Two colleagues in the back office use it daily, and each of them keeps a private copy "just in case".

One of those copies still has last year's steel price in it. For six weeks, quotes go out with a margin nobody intended. Someone notices during the quarterly review, which is too late for every order already signed.

Nobody here was careless. The file grew because it worked, and at some point it stopped being a spreadsheet and became the company's pricing system without anyone deciding that. The obvious reaction is to replace it with proper software. I would argue for something slower and much less exciting.

Photo: Unsplash

How to tell a spreadsheet has become a system

Excel is fine. A sheet that one person keeps for their own analysis is a good tool, and nobody should build software to replace it. The trouble starts when the file's job changes underneath it. Four signs tell you that has happened.

More than one person edits it. With one owner, the file has one memory. Once three people add rows and change values, nobody can say who is responsible for which number. Excel keeps no reasons. Why the surcharge for stainless dropped from 18 to 15 percent is written down nowhere.

Copies travel by email. Every copy that gets sent, edited and sent back is another version of the truth. Someone merges them by hand, and changes get lost in the merge. File names that end in "final", "new" or somebody's initials give it away.

There is a macro only one person understands. A macro is a program with no documentation, no tests and, often, one person who can change it. When that person is on holiday, the process waits. When they retire, it stops.

The file decides about money. A price calculation sets what goes into the quote. An order list sets what gets produced. A capacity plan sets which delivery date sales promises. A typo in any of these costs margin or a customer, and it often surfaces weeks later.

There is a quick test. Suppose the file is corrupted tomorrow morning and the last backup is a week old. If the honest answer is "we stop working", you are looking at a system.

Pin it down before you touch it

The expensive mistake is to start rebuilding straight away. A file that grew over years contains rules nobody wrote down, and you need to know them before you replace anything.

Write down what the file decides. Forget the columns for a moment and ask what decision someone makes with it. "We set the quote price." "We promise a delivery date." Usually there are two or three decisions. Everything else in the file feeds them or is clutter.

List the inputs and the outputs. Which values does someone type in, where do they come from, and who types them? Which numbers does someone read off and copy somewhere else? That list becomes the plan for forms and connections later. It also shows which inputs already live in another system, your ERP for example, and are only being copied across by hand.

Freeze the formulas. Save a read-only copy of today's version and protect the formula sheets in the working file. Then collect ten to twenty real cases with known results, such as last quarter's orders with the price that actually went out. That set is your test bench. Anything that later replaces part of the file has to produce the same results for those cases, or someone has to explain why the new answer is the right one.

Decide who may do what. Who can change prices? Who can create orders, and who can only read them? In the file, everyone can do everything. The answers become the permissions in the new tool, and they are often the part that starts the longest discussion.

What the move looks like for the usual suspects

The move happens in pieces. The first piece is almost always data entry. The calculation follows once it is understood and tested. Reporting often stays in Excel for good.

The order list. The pain here sits in the input, so orders get a form with required fields and dropdowns, and the data lands in a small database. Each record has a status, and the tool logs who changed what and when. Anyone who still wants to filter and sort in Excel can. Excel's Power Query can load data straight from a database, which turns the sheet into a view of the data instead of another copy.

The capacity plan. Its inputs usually come from the order list and the ERP already. Step one is that nobody retypes orders into the plan. The scheduling macro moves only once someone understands it. Until then it keeps running, now with clean inputs.

The price calculation. This is where the money is, so this is where the risk is. Material prices, surcharges and hourly rates move into a table in the database that two named people can change, each change with a date and a reason. The calculation itself stays in Excel for now and reads its prices from there. Once that base has run for a few months without surprises, it makes sense to bring the calculation into the tool as well. Quotes can then take their prices from that table, as described in how to automate quoting.

If your company is in Germany and has a Betriebsrat, the elected works council that has co-determination rights on many workplace matters, bring it in early. A tool that logs who changed what can count as a technical monitoring device under section 87, paragraph 1, number 6 of the Works Constitution Act. According to the Federal Labour Court, it is enough that the tool is capable of monitoring staff, whether or not you intend to use it that way. An early conversation is cheaper than a halt halfway through.

Why not rebuild everything at once

Replacing the whole file with a new application in one project sounds tidy. It fails in predictable places.

The unwritten rules show up too late. Somewhere in the file sits a special case for a large customer, or a rounding rule someone added years ago. In a full rebuild you find it when the new system produces a wrong price in production. In a stepwise move you find it when your test bench turns red.

People have to relearn everything on the same day. Someone who has used a file for years knows its quirks. A new input form is a small change. A new system with new terms and new screens is a big one, and it lands on a day that is already busy.

There is no way back. Switch the file off on a fixed date and you have nothing to fall back on when something breaks. Move piece by piece and old and new run side by side for a while. When the numbers differ, the old number stands until someone finds the cause.

Some of it belongs in Excel. Reports, one-off calculations and the pivot table for the monthly meeting are fine where they are. I would leave them in Excel and feed them from the database. The goal is a spreadsheet that reads data instead of storing it.

The order I recommend is plain. Write down the decisions, freeze the formulas, collect the test cases, then move data entry, then the master data such as prices, and the calculation last. Each step takes a few weeks and is useful on its own, even if the next one never happens.

That file exists because someone solved a real problem with the tool they had. It deserves a move with a plan. Replace it in one go and you also replace the rules nobody knew about, and you find out which ones mattered from a customer.

Topics

  • Excel
  • Internal tools
  • Databases
  • Process digitalization
  • Mittelstand
All articles

Keep reading

  • 16 min read

    E-invoice automation for XRechnung and ZUGFeRD

    You can automate e-invoice intake in Germany from the inbox to the archive, because every supplier who switches to XRechnung or ZUGFeRD sends the invoice as finished data. This article goes through it step by step: what the software takes over, what stays with a person, and what no automation fixes.

  • 8 min read

    Shared inbox triage with AI: a person hits send

    Every company has an info@ or service@ address that someone sorts by hand each morning, with orders buried between newsletters and complaints waiting behind invoices. A language model can read, classify and draft replies there, as long as a person approves every send. This is how to set it up, what to log, and what data protection requires in Germany.

  • 7 min read

    What to automate first: count for one week

    Most companies pick their first automation by whatever sounds best in the management meeting. The better answer comes from a tally sheet your team keeps for one week. This piece covers how to count, how to choose, and how to fit the brief for the first build into a single paragraph.

End of call

You can hang up now.

Or book 15 minutes. I'll ask you the questions I'd ask on the phone, and tell you at the end whether it fits. If it doesn't, we've both lost a quarter of an hour instead of a month.

Or call. Cold, if you like. My AI assistant picks up and I call you back. +49 5136 9414999

Analytics

With your consent I use Google Analytics to see which pages get read. Without it, no analytics code loads. Your answer is stored locally in your browser.

Privacy notice