From Excel to a data foundation: a 30-day plan

Why move away from Excel at all?

Excel is not the problem. It is a fine spreadsheet and for a quick analysis it remains unbeatable. It only becomes a problem when Excel is where your business numbers live: exporting every month, pasting, updating formulas and hoping nobody shifted a column.

In our article what is a data foundation we explain what such a foundation is: sources, connections, a data layer and fixed definitions. This article is about practice. How do you get there from a folder full of spreadsheets, without a big IT project and without day-to-day work grinding to a halt?

Not sure you are ready? Read Excel or Power BI first. It lists the signs that it is time to switch.

What do you need before you start?

Three things, and none of them is technical.

1. One question. Pick the question that costs the most time today or causes the most debate. For example: "What is our gross margin per customer per month?" or "How many hours do we log per project versus what we invoice?" One question keeps the project small.
2. An owner. Someone who knows the question, can decide on definitions and frees up a few hours a week. Often the controller or the owner of the business.
3. Access. A user with read rights on the systems you need, plus the existing spreadsheets. Sorting out who can access what beforehand saves a week of waiting.

With those three in place, the clock can start.

Week 1: which spreadsheets do you have and where does the data come from?

The first week is detective work. You collect every file related to your chosen question and work out, file by file, where the data comes from.

• Which tabs are really used, and which are leftovers?
• Which columns come from an export (accounting, CRM, time tracking) and which are typed in by hand?
• Which formulas do the actual work? That is where your hidden definitions live.
• Who updates the file, how often, and what happens when that person is away?

By the end of the week you have a simple overview: for each data item, its source, its owner and how it ends up in Excel today. Check straight away whether the source systems offer an API. Many standard packages do, and sometimes there is already a ready-made integration.

Pitfall: dismissing manual lists. A list a colleague keeps because the system cannot do it often holds exactly the information you need. It needs a proper home, not deletion.

Week 2: how do you define what a number means?

In week 2 you take the logic out of the formulas and write it down in plain language. This is the week that pays off most, and the week people most like to skip.

For a margin question you would answer, for example:

• Which date leads: invoice date, delivery date or posting date?
• How do credit notes count: in the month of the credit note or in the month of the original invoice?
• What is a customer: every account number, or do branches of the same company belong together?
• Which costs count towards margin and which do not?

Record every definition in one document, with an example. Have the owner sign off. It sounds formal, but it stops you from starting over in week 4 because someone meant something else by "margin".

Pitfall: treating the Excel result as sacred. If the new number differs from the old one, the new one is not automatically wrong. Often the spreadsheet turns out to have contained a small error for years. Agree on how you will investigate differences before they appear.

Week 3: building connections and the data layer

Only now does the technology come in. In week 3 you make sure data flows automatically from the sources into one place, outside Excel and outside the report.

1. Fetch. One connection per source that pulls the required data through the API at a fixed time. No more exports.
2. Store. The data goes into a database or data layer. That also builds up history, something many systems do not keep themselves.
3. Clean and join. This is where the definitions from week 2 are applied: aligning dates, merging customers, assigning credit notes correctly.
4. Give manual lists a home. Whatever genuinely stays manual gets a fixed input format, for example one shared list with fixed columns, loaded just like everything else.
5. Build in checks. Does the total match the source? Did the refresh succeed? Who gets alerted when it fails?

This is what we mean by data engineering. Why outside the report? Because a dashboard that has to do all the logic itself becomes slow, and because you will want to reuse the same clean data for other reports or automations later.

Pitfall: trying to connect every source at once. Connect only what the one question needs. The rest comes later, and goes faster because the foundation is already there.

Week 4: report on top, compare and hand over

In the final week you put a report on the data layer, for example in Power BI, and lay it next to the old spreadsheet.

• Compare. Run the old spreadsheet and the new report over the same month. Explain differences using the definitions from week 2.
• Put it to use. Give the people who need the numbers access and ask after a few days what they are missing. A rough version with real data gets better feedback than a polished design with invented numbers.
• Hand over. Document how the refresh works, where the definitions live, who gets alerts and who may make changes.
• Make the spreadsheet read-only. Only once the new report holds up. As long as both exist side by side, someone will keep updating the old file.

Which figures go into that first report depends on your question. For inspiration, see the 10 KPIs every SME dashboard needs. For building the report itself, see our dashboarding service.

An example: margin per customer from three spreadsheets

A fictional but recognisable example. An installation company tracks margin per customer in three files: a revenue export from accounting, an hours list from the planning tool and a manual list of purchase costs per project. Merging them takes a day every month, and the result differs every time someone does it.

• Week 1 reveals that the purchase list is kept by hand because supplier invoices are not booked against a project number.
• Week 2 the managing director decides that invoice date leads and that a credit note falls back into the month of the original invoice.
• Week 3 accounting and planning are connected. The purchase list gets fixed columns and is loaded daily.
• Week 4 a margin report per customer is live. The first comparison shows a difference with the old spreadsheet, traced back to a formula that skipped a tab.

The result is not a complete data warehouse, but one question that is answered automatically and traceably every morning. The next question builds on it.

What do you have after 30 days, and what not yet?

You do have: one business question answered automatically, written definitions, working connections to the sources it needed, a data layer with history and a handover someone else can continue with.

Not yet: all of your company's reporting. That is not the goal either. The foundation grows one question at a time. Because the first sources are already unlocked and the way of working is in place, every next step goes faster.

Connecting several sources to your accounting? Also read connect Power BI to your accounting. And to see how we run such a project, have a look at our approach.