How to build a business dashboard in Excel (and when it's time to stop)

To build a business dashboard in Excel, keep it to three tabs - your raw data in an Excel Table, a calculations tab, and a dashboard tab built from PivotTables, PivotCharts and slicers. Put no more than 6–8 numbers on it, the ones that tell you whether you're making money, getting paid and winning more work. That works fine until the data lives in several systems and someone has to rebuild it every month. At that point a dashboard linked to Xero and your other systems, which updates itself, makes more sense.

For a business of 10–100 people, the dashboard needs to answer four questions. Are we selling enough, are we making money on it, are we getting paid, and have we got the capacity to do the work? Six to eight numbers pretty much covers it. More than ten and nobody reads it.

• Revenue vs target (month and year to date). In construction, that's applications for payment certified vs forecast.
• Gross margin %, by service line - or by job or division if you're in construction.
• Cash in the bank, ideally with a 4–8 week forecast.
• Debtor days and overdue invoices: how long customers take to pay, and what's past due by age band. In construction, include retentions held.
• Pipeline and win rate: quotes sent, won and lost, plus your win rate over the last 90 days.
• Jobs or projects over budget: cost to date against budget, flagged red or amber. Service businesses: projects over their hours estimate.
• Utilisation: billable hours as a share of hours available, or labour booked against capacity for the next few weeks.

Pick the ones you'd act on. If a number going red wouldn't change what you do on Monday, leave it off.

1. Data tab. Paste each export (sales invoices, aged debt, job costs, quotes) onto its own tab and turn it into an Excel Table with Ctrl+T. Give each Table a name (Invoices, Jobs, Quotes). Tables grow as you paste new rows, so anything built on them picks up the new data.
2. Calcs tab. Do the arithmetic here rather than on the dashboard - margin %, debtor days, win rate, forecast cost per job. One formula per KPI, in a labelled cell the dashboard can point at.
3. Dashboard tab. Insert a PivotTable from each Table (Insert > PivotTable), then a PivotChart from that. The big-number tiles are just cells linked to the Calcs tab. Keep it to one screen.
4. Slicers and flags. Add a slicer for month and one for division or service line (Insert > Slicer), and use Report Connections so one slicer filters every PivotTable built on the same data. Then add conditional formatting - red for jobs over budget and debts over 60 days, amber for anything close.
5. Refresh routine. Once a month (or week), paste fresh exports over the old rows, then Data > Refresh All. Write the steps down on a Notes tab, because whoever built it won't always be the one refreshing it.

For a specialist contractor, the finished tab might have six tiles across the top (revenue vs target, gross margin, cash, debtor days, win rate, utilisation), month and division slicers down the left, a revenue-by-month chart against target in the middle, an aged-debt table on the right and a jobs-against-budget table along the bottom. A service business swaps the jobs table for projects against hours estimate.

Debtor days = (Trade debtors ÷ Credit sales for the period) × Days in the period

=ROUND(Calcs!B4 / Calcs!B5 * 90, 0)

PivotTables. Summarise thousands of rows by month, customer or job without a single formula, and re-cut them by dragging fields. Most of a dashboard is PivotTables.

XLOOKUP. Pulls a value from another Table by matching a key - a job's budget by job number, say. It matches exactly by default and lets you say what to show when there's no match. It's in Microsoft 365, Excel 2021 and 2024, but not Excel 2016 or 2019.

=XLOOKUP(A2, Jobs[Job No], Jobs[Budget], "Not found")

INDEX and MATCH. The older way of doing the same lookup, and it works in every version of Excel. MATCH finds the row (the 0 means exact match) and INDEX returns the value from that row.

=INDEX(Jobs[Budget], MATCH(A2, Jobs[Job No], 0))

Power Query. Data > Get Data records your clean-up steps once (deleting columns, fixing dates, combining a folder of monthly exports) and replays them every time you refresh. It takes a lot of the work out of the monthly rebuild. Our guide to automating monthly Excel reports walks through it.

• Xero: open a report (Profit and Loss, Aged Receivables, Account Transactions) and use Export to download it as Excel, Google Sheets or PDF. QuickBooks and Sage have similar Excel exports.
• Bank: the cash balance from your accounting software's bank reconciliation, or a CSV statement from online banking.
• CRM or quotes: HubSpot, Pipedrive and most CRMs will export deals to CSV. If you quote from spreadsheets, the quote log is your pipeline.
• Job or project system: budgets, cost to date and % complete, usually as a CSV report.
• Timesheets: hours by person and job, for utilisation.

Every one of these is a manual download. Five sources means five exports, five pastes and five chances to grab the wrong date range, every time you refresh. Third-party connectors in the Xero App Store can push Xero data into Excel on a schedule, but they only cover Xero, not your job system or timesheets.

An Excel dashboard is a good first version. It usually stops working for the same five reasons:

• The monthly rebuild. Someone spends a morning or more exporting, pasting and checking before anyone sees a number. So the dashboard is always weeks old, and the month that person is busy, it slips.
• Data in several systems. Xero has the invoices, the CRM has the quotes and the job system has the costs. Excel only joins them up if the job numbers and customer names match exactly, and they rarely do.
• Version chaos. "Dashboard v3 FINAL (2).xlsx" gets emailed round, and two directors turn up to the same meeting with two different margin figures.
• No alerts. A job going over budget or a customer passing 60 days only shows up when someone opens the file and goes looking.
• One person understands it. The workbook depends on whoever built it, so when they're off (or leave) the dashboard stops.

If two or more of those sound familiar, more formulas won't fix it. The problem is that the numbers are being carried into Excel by hand.

Streamlined Analytics builds systems that link Xero (or QuickBooks or Sage) to your CRM, job system and timesheets, and put the numbers on one live dashboard. It refreshes from source on its own, so there's no template to maintain and no monthly rebuild. Everyone reads the same figures, and you can click from a KPI down to the invoices or jobs behind it.

• Built around your KPIs: the same 6–8 numbers above, worked out the way you work them out, not an off-the-shelf template.
• Alerts, if you want them: an email when a job goes over budget or a debt passes your limit, so nobody has to go looking.
• Nothing replaced: your team carries on using Xero and the tools they know.
• You own it: fixed-price builds start from £3,000, with optional care plans from £250 a month. See pricing.

You can have a click around a working example first: the live dashboard demo turns a messy spreadsheet into a filterable dashboard you can ask questions of in plain English. More on how it works on our data analytics and dashboards page. Weighing up Power BI instead? Read our Power BI alternatives for SMEs, and construction firms may also want construction reporting software.