Spreadsheet to dashboard: moving off Excel reports

Why spreadsheet reporting breaks

Spreadsheets are excellent at calculation and terrible at being a shared, repeated report. Three things go wrong, usually in this order:

1. Versions. The report is emailed, someone edits their copy, and by Thursday there are five versions with different totals. The question in every meeting becomes "which file are you looking at?"
2. Formulas. The workbook accumulates lookups and hidden sheets that only its author understands. A pasted block overwrites a formula, a renamed column breaks a lookup, and the error is found a month later when a director queries a total. Audits of business spreadsheets routinely find errors in most of them; your own experience probably agrees.
3. Size. Once the transaction tab passes a few hundred thousand rows, the file takes minutes to open, recalculation freezes, and someone starts deleting history to keep it working. The report now covers 18 months because that is what fits.

Underneath all three is a single cause: the spreadsheet is doing two jobs — holding the data and presenting it — and it is only good at the second when the first stays small.

What to keep in spreadsheets

Moving to dashboards is not a ban on spreadsheets. Some things belong there because a person types them and they change by discussion, not by transaction:

• Targets and quotas — sales targets by rep by month, store plans, KPI thresholds.
• Budgets and forecasts — the annual budget by cost centre, the reforecast, the cash forecast.
• Reference lists and mappings — which product codes roll up to which category, which cost centre belongs to which department, exchange rates for the month.
• One-off analysis — the pricing scenario, the what-if. Do it in the sheet; if it becomes monthly, move it.
• Manual adjustments — accruals and reclassifications finance wants to apply on top of system figures, kept in a sheet where each line has an owner and a reason.

The rule: if the numbers come from a system, the system feeds the dashboard directly. If a person decides the numbers, they live in a sheet — and the sheet feeds the dashboard too.

Connecting Sheets and Excel alongside your databases

The step most people miss is that a spreadsheet can be a data source like any other. Connect the targets workbook from Google Sheets or the budget file from Excel, and connect the accounting system, the CRM or the database beside them. Then relate the sheet to the system data by the column they share — rep name, store code, month, cost centre — and every chart can show actual (from the system) next to plan (from the sheet).

A worked example: the sales targets sheet has 12 reps × 12 months. The CRM has closed deals by rep and close date. Relate them on rep and month, and the attainment chart is a single calculated field — closed value divided by target — that updates whenever either side changes. When the sales director changes a target in the sheet, the dashboard reflects it on the next refresh with nobody re-pasting anything.

Three rules for a sheet that feeds a dashboard: one table per tab starting at cell A1 with a header row; no merged cells, subtotals or blank rows inside the table; and one owner who is the only person who edits it. Refresh hourly or nightly; a targets sheet rarely needs more.

Rebuild the report once

Do not migrate the workbook. Rebuild the report it produces, once, from the sources. In order:

1. Inventory the reports, not the files. Which spreadsheets do people actually open or receive weekly? Usually 3 to 6 matter out of 30.
2. For each, write down every number on it and where it comes from — which system, which table, which sheet. This is the map for connecting sources and usually reveals two numbers that come from nowhere anyone can name.
3. Turn each formula into a calculated field, defined once with a name and a description. Most spreadsheet formulas are sums, ratios and lookups; the lookups become relationships and the ratios become formulas using the 138 functions available. The complex nested ones are usually three simple ones in a trench coat.
4. Build the page as the report is read: headline numbers top left with comparison, one trend, one breakdown, a table for look-ups.
5. Run both in parallel for one cycle. Produce the spreadsheet as usual and the dashboard beside it, and reconcile every number. Differences are almost always a definition (refunds in or out) or a stale lookup in the sheet — fix the definition, document it, move on.

Budget an afternoon per report for steps 2–4 and one reporting cycle for step 5. After the parallel run, retire the workbook: rename it "ARCHIVE" and stop sending it.

Replace email attachments with scheduled reports

The last spreadsheet habit to break is the attachment. The Friday email with "sales_wk36.xlsx" is where versions come from. Replace it with a scheduled delivery of the dashboard page — as a PDF or image, at the same time on the same day, by email, Slack, WhatsApp or Telegram — with a link to the live page for anyone who wants to click into a number.

This also fixes a problem the spreadsheet never could: the same file went to everyone, so everyone saw every column and every branch. With permissions on the data, one schedule sends each manager only their branch and hides the margin column from everyone outside finance, from one dashboard and one schedule. See scheduled reports.

Keep the download available. People still want the rows in a spreadsheet for their own analysis, and a CSV or Excel export from the dashboard gives them a fresh, permission-filtered copy rather than the shared master file.

The objections you will hear, and the answers

• "I need to adjust the numbers before they go out." Put the adjustments in a connected sheet with an owner and a reason per line. They are applied automatically and visible, rather than silent.
• "The spreadsheet does something the tool can’t." Usually a formula. Ask what it calculates in words; it is almost always expressible as a calculated field. If it genuinely is not, keep that one sheet and connect it.
• "People like Excel." They like the numbers in Excel, and the export gives them that. What they do not like is the Thursday version argument.
• "We don’t have time to rebuild." An afternoon per report, for the 3 to 6 that matter. The workbook is costing more than that every month.