Blog · Board and management reporting
Six Excel dashboard examples for finance and sales teams: the monthly P&L, sales against target by region, cash and receivables, pipeline, a KPI scorecard and product and customer mix. For each, the tiles, the chart, the breakdown and the exception list, with the SUMIFS formulas behind the tiles and the two ways to make it interactive.
The Excel dashboard examples worth copying all answer one reader's question on one screen: four to six KPI tiles, one trend chart, one breakdown against target, and a short exception list. The six layouts below cover the monthly P&L, sales against target, cash and receivables, pipeline, a KPI scorecard and product and customer mix. Every tile is a SUMIFS on a data sheet, and slicers on a PivotTable make the whole thing interactive.
This page is the gallery. The step-by-step build, from loading the data to the check row, is in how to build a dashboard in Excel. For dashboards designed around one sales reader at a time, from the chief executive to the rep, see sales dashboard examples.
Four parts, in this order on the screen:
| Part | What it shows | Typical build |
|---|---|---|
| KPI tiles | Four to six headline numbers against target or prior year | One SUMIFS per tile on the Calc sheet |
| Trend | The main measure over 13 months | Line or column chart on a Calc range |
| Breakdown | The total split by the dimension that explains it | Bar chart: actual against target |
| Exceptions | The few rows that need action | A filtered or sorted table, ten rows at most |
Three rules separate a dashboard someone uses from one they glance at. One reader: a controller and a regional sales director need different screens. One question: "are we on budget this month?" is a dashboard; "everything about sales" is a report, and dashboard vs report vs list explains when each is right. One screen: no scrolling.
Every example below uses three sheets: Data (the raw rows as an Excel Table), Calc (the formulas) and Dashboard (tiles and charts that only reference Calc). The exception list matters most, because the list gets worked and the dashboard gets glanced at.
Reader: the CFO or controller. Question: is the month on budget, and if not, which lines?
| Element | Content |
|---|---|
| Tiles | Revenue, gross margin %, operating cost, EBITDA, each with actual, budget and variance |
| Trend | 13 months of revenue and EBITDA, actual against budget, so the same month last year is on screen |
| Breakdown | Operating cost by department, actual against budget |
| Exceptions | Cost lines over budget by more than a set threshold, largest first |
The gross margin tile is gross profit summed over revenue summed, never an average of monthly percentages. Thirteen months rather than twelve puts the current month next to the same month a year earlier.
Reader: the sales director. Question: which regions are behind target this month? Month of June, figures in USD thousands:
| Region | Actual (USD k) | Target (USD k) | Variance (USD k) | Variance % | Prior year (USD k) | Growth % | Attainment % |
|---|---|---|---|---|---|---|---|
| Northeast | 1,840 | 1,750 | +90 | +5.1% | 1,620 | +13.6% | 105.1% |
| Southeast | 1,320 | 1,400 | −80 | −5.7% | 1,290 | +2.3% | 94.3% |
| Midwest | 960 | 900 | +60 | +6.7% | 880 | +9.1% | 106.7% |
| West | 1,510 | 1,600 | −90 | −5.6% | 1,430 | +5.6% | 94.4% |
| Southwest | 720 | 700 | +20 | +2.9% | 610 | +18.0% | 102.9% |
| Total | 6,350 | 6,350 | 0 | 0.0% | 5,830 | +8.9% | 100.0% |
The headline tile says $6,350,000 against a $6,350,000 target: exactly on target. Underneath, Southeast is 5.7% short and West 5.6% short, covered by Northeast, Midwest and Southwest. That is why the breakdown bar sits directly under the tile: the tile alone would tell the director there is nothing to do.
The Data sheet has one row per Month, Region, Type (Actual or Target) and Amount. The month sits in C2, the region names in B5:B9, and the actual for a region is:
=SUMIFS(Data[Amount],Data[Type],"Actual",Data[Region],B5,Data[Month],$C$2)
Target in D5 is the same with "Target". SUMIFS adds the cells that meet every condition, so one formula pattern fills the whole table. The rest:
Variance: =C5-D5
Variance %: =IFERROR((C5-D5)/D5,0)
Growth %: =C5/F5-1
Attainment %: =C5/D5
The check: the five regional actuals sum to the total tile, 1,840 + 1,320 + 960 + 1,510 + 720 = 6,350, and so do the targets. Put both sums in a check cell next to the tile and flag any difference. The SUMIFS guide covers the criteria syntax in full.
Reader: the treasurer or credit manager. Question: is cash coming in as expected?
| Element | Content |
|---|---|
| Tiles | Cash balance, DSO, receivables overdue more than 60 days, collections this month |
| Trend | DSO over 13 months against the standard payment terms |
| Breakdown | Receivables aging as a stacked bar: current, 1-30, 31-60, 61-90, over 90 days |
| Exceptions | The top ten overdue customers by amount, with days overdue and last payment date |
The aging buckets must add to the receivables balance in the ledger; put that check in the Calc sheet.
Reader: the head of sales. Question: is there enough pipeline to make the quarter?
| Element | Content |
|---|---|
| Tiles | Open pipeline, pipeline coverage against the remaining quarter target, win rate, deals slipped this month |
| Trend | Win rate by month over the last four quarters |
| Breakdown | Pipeline by stage, as a bar per stage |
| Exceptions | Deals whose close date moved out of the quarter, by value |
Coverage is open pipeline for the quarter divided by the target still to be won, not by the full quarter target.
Reader: the leadership team. Question: which of our eight measures are off track? Choosing those eight is the hard part; KPI vs metric vs measure helps decide which earn a place.
| Column | Content |
|---|---|
| KPI | Name and definition link |
| Actual, Target | This month |
| Direction of good | Up or down: DSO and cost per order are good when they fall |
| Status | Green, amber or red by a written rule |
| Trend | A sparkline of 13 months |
Status uses conditional formatting: an icon set, or a formula rule such as =E5<-0.05 for red when the variance is more than 5% adverse. For a KPI where lower is better, flip the sign of the variance before the rule reads it.
Reader: commercial finance. Question: where does revenue come from, and how concentrated is it?
| Element | Content |
|---|---|
| Tiles | Revenue, top-10 customer share, number of active customers, revenue per customer |
| Trend | Share of revenue by product line over 13 months, as a stacked column |
| Breakdown | A Pareto chart of customers: revenue bars sorted largest first, cumulative share as a line |
| Exceptions | Top customers whose revenue fell more than a threshold against last year |
Share of total is each row over the grand total: =C5/SUM($C$5:$C$24). The shares must add to 100%.
Two ways, and the choice depends on how the Calc sheet is built.
PivotTables with slicers. Build each chart on a PivotTable, then insert a slicer for Region or Product and a timeline for the date. One slicer can drive several PivotTables: per Microsoft's slicer guide, select the slicer, then Slicer > Report Connections and tick each PivotTable built on the same source. Best when the reader wants to filter freely.
A drop-down cell driving SUMIFS. Data > Data Validation > List on one cell, such as the month in C2 or a region, and every SUMIFS references that cell. Best when the layout is fixed, the tiles mix actuals and targets, or the dashboard has to print the same way every month.
Pick one per dashboard. Mixing the two leaves some tiles filtered and others not.
Excel dashboards like these are rebuilt by hand every month. Covirage's tools compute each tile from your own ledger and sales files and check that the regions add up to the total; the external AI model writes the explanation, never the numbers. Need the board version, with every figure cited to the rows behind it? See board reporting. To start from a workbook, see the KPI dashboard template and the financial dashboard template; for charts that follow a pivot table, pivot chart in Excel.
A title stating the period and data date, four to six KPI tiles with comparison to target or prior year, one trend chart, one breakdown by the dimension that explains the total, and a short exception list. Anything else goes on a separate report tab.
Build the charts on PivotTables and add slicers and a timeline (PivotTable Analyze > Insert Slicer), connecting each slicer to every PivotTable via Report Connections. Without PivotTables, use a Data Validation drop-down cell that every SUMIFS formula references.
For one team, a monthly refresh and under a few hundred thousand rows, yes. It struggles when many people need their own scoped view, when data arrives from several systems, or when the refresh is weekly and manual. That is the point to look at a reporting tool.