From spreadsheets to dashboards

If your Monday starts with exporting three reports, pasting them into a master spreadsheet, and fixing the formulas that broke over the weekend, you are doing by hand what a dashboard does automatically. This guide shows you how to get from that copy-paste routine to a small set of dashboards you can trust — without hiring a data team or building infrastructure you don’t need yet.

The goal is not a prettier chart. It is one place where the numbers are current, consistent, and the same no matter who asks. That is the whole game.

Why this matters

Spreadsheet reporting fails in predictable ways. Two people answer the same question and get different totals. A report takes half a day to assemble, so nobody runs it more than once a month. Someone edits a cell, and now the year-to-date figure is quietly wrong for the next six months. None of this is a spreadsheet flaw — spreadsheets are excellent — it is a symptom of using a spreadsheet as a database, a report, and a data pipeline all at once.

A dashboard splits those jobs apart. The data lives in your systems. A tool pulls it, applies your definitions once, and shows it. When the underlying data changes, the dashboard changes — no re-paste, no re-formula. For a business spending a few hours a week assembling reports, that is typically 50 to 150 hours a year back, plus the harder-to-price benefit of arguing about the business instead of arguing about the numbers.

What “one source of truth” actually means

The phrase gets thrown around, so here is the plain version. Right now your revenue number probably lives in three places: the accounting system, a sales spreadsheet, and someone’s head. “One source of truth” means you decide, for each number, which system is the authority — and everything reads from there.

You do not need a fancy platform to start. You need a decision:

• Revenue and margin — the authority is your accounting or enterprise resource planning (ERP) system, not the sales spreadsheet.
• Pipeline and won deals — the authority is your customer relationship management (CRM) tool, if you have one, otherwise the one spreadsheet everyone agrees to use.
• Hours, jobs, or tickets — the authority is whatever operational system your team actually works in daily.

Write these down. This list is the foundation of every dashboard you will build, and getting it agreed on is 80% of the work.

Pick a few KPIs that matter — not forty

The most common dashboard mistake is putting every number you can find onto one screen. A useful dashboard answers a small number of questions the business runs on. Aim for five to eight key performance indicators (KPIs) on your first dashboard, each tied to a decision someone makes.

Good KPIs share three traits: someone owns them, they change decisions, and they can be computed from data you already have. Examples across industries:

Business type	A KPI worth watching	The decision it drives
Professional services (law, accounting, design)	Utilization rate (billable hours ÷ available hours)	Whether to hire or shift workload
Construction or trades	Job margin vs. estimate	Which jobs to bid and how to price
Light manufacturing or distribution	Days of inventory on hand	When to reorder, what to discount
Dental or medical clinic	Chair or provider utilization	Scheduling and staffing
Multi-location retail	Sales per square foot by location	Where to invest or cut
Nonprofit	Cost per program outcome	Where funding goes furthest

If a number is interesting but nobody acts on it, leave it off the first dashboard. You can always add it later.

Choosing a tool without overbuilding

You have three sensible tiers. Match the tier to where you are, not where you imagine being in three years.

• Starter — the tool you already own. If your data lives in Google Workspace, Google Looker Studio is free and connects to Google Sheets and many databases directly. If you live in Microsoft 365, Excel’s built-in charts and PivotTables on a live-connected table will get a small team surprisingly far. Start here if you have one or two data sources and a handful of KPIs.
• Standard — a dedicated business intelligence (BI) tool. Microsoft Power BI is the common choice for small and medium businesses, especially on Microsoft 365, and a per-user license is modest. It connects to accounting systems, databases, and spreadsheets, refreshes on a schedule, and lets you define a metric once and reuse it. The Microsoft Power BI documentation is a solid, honest place to understand what it does before you buy. Google Looker Studio scales up here too.
• Advanced — a data warehouse behind the tool. Only when you have several systems that must be joined, millions of rows, or a real need for history that your source systems don’t keep. This is the point to get help. (See [[The modern data stack, end to end]] for what that looks like.)

Most small businesses never need the third tier for their first year of dashboards. If a vendor’s first suggestion is a data warehouse and you have three systems and a spreadsheet, slow down.

What you can do yourself vs. when to get help

You can almost certainly do yourself: connect one or two clean sources, agree on KPIs, build a first dashboard, and set it to refresh. This is genuinely a weekend project.

Bring in help when:

• Your numbers won’t reconcile — the accounting total and the dashboard total disagree and you can’t find why. That is usually a definition problem or a duplicate-data problem, and it is worth an expert’s afternoon rather than your month.
• You need data from a system with no easy export or connector, or the connection needs to be secure and audited.
• You are joining several systems and the logic is getting complicated enough that the dashboard itself is becoming the thing that breaks. That is the warehouse conversation.

Getting your systems to hand data to each other cleanly is its own skill — [[Making your software talk to each other]] covers the integration side, which is where dashboard projects most often get stuck.

Your first dashboard in a weekend

A concrete plan you can run this week. Set aside two focused half-days.

Saturday morning — decide and clean:

1. List the five to eight KPIs for your first dashboard. Write the exact formula for each, in words. “Job margin = invoiced amount minus recorded costs, per job.”
2. For each KPI, name the one authoritative source system. If two systems could answer it, pick one and note it.
3. Export or connect one source and look at the raw data. Fix the obvious mess — consistent date formats, no blank required fields, one row per record. Clean data now saves hours later.

Saturday afternoon — connect and build:

1. Pick your tool from the tiers above. Connect your first source.
2. Build one KPI end to end — get the number to match what you’d get by hand. If it doesn’t match, stop and reconcile before adding anything else. A dashboard that is 90% right is worse than no dashboard, because people stop trusting it.
3. Add the rest of your KPIs one at a time, checking each against a known-good figure.

Sunday — refresh and share:

1. Set the dashboard to refresh on a schedule (daily is plenty for most businesses).
2. Share it with one or two people and ask them to try to break your numbers. Fix what they find.
3. Stop. Resist adding more until people have used this version for two weeks.

Watch-outs that bite people

• The number that’s almost right. A dashboard people don’t trust is worse than the spreadsheet you replaced, because now there are two sources of disagreement. Reconcile every KPI to a figure someone already believes before you show it to anyone.
• Dashboard sprawl. Six months in, you have twenty dashboards and forty KPIs and nobody knows which is current. Keep a short list of official dashboards and delete experiments.
• Refresh you forgot to check. Connections break — a password changes, an export format shifts — and a dashboard can show stale data while looking perfectly current. Put a “last refreshed” timestamp on every dashboard and check it.
• Building the warehouse first. Don’t buy infrastructure to solve a problem you don’t have yet. Start with one dashboard on real data. The need for more will make itself obvious.

Your next step

If you can get one dashboard reconciled and refreshing this weekend, you have already replaced a recurring chore with something that runs itself. When the numbers won’t reconcile, or you need several systems joined into one trustworthy view, that is exactly the work our [[Data and BI]] practice does — we get the definitions right once and hand you dashboards your team can run without us. Tell us which report is eating your week and we’ll point you at the shortest path to a dashboard you trust.