Data · · 2 min read
From Excel to a dashboard: first steps to organise your data
If your reports depend on copying and pasting between sheets, there's a better way. These are the steps I follow to move to a reliable dashboard.
Excel is an excellent tool, but when a report is put together every week by copying and pasting between files, the risk of errors grows and time disappears. Moving to a dashboard doesn't have to be a huge project.
1. Define the questions
Before opening any tool, I write down with the client the questions the dashboard must answer. For example: how much did we sell this month compared with last month? Which product has the best margin? If a number doesn't help decide anything, it doesn't go on the front page.
2. Find where each figure comes from
For each metric I identify its source: the invoicing system, a particular sheet, a form. Ideally you always read from the original source, not from copies.
3. Clean and unify
This is where most of the work is:
- Unify date and currency formats.
- Fix names written in several ways ("Malabo", "malabo", "MALABO").
- Remove duplicates.
- Define what each term means, for example what exactly counts as a "sale".
4. Build a data model
I organise the information into related tables (sales, products, customers, dates). With a good model, adding a new chart later takes minutes.
5. Design the dashboard
A few big numbers at the top, simple charts below and filters to dig deeper. I prefer a dashboard you understand in ten seconds to a spectacular one nobody knows how to read.
6. Automate the refresh
The last step is making the data refresh on its own, daily or as needed. That's the moment the team stops "making the report" and starts using it.
I've spent years working with operations data in the energy sector, and the lesson is always the same: a useful dashboard starts with organised data, not pretty charts.