Sheet2ChartHow to Build a Finance Dashboard in Excel
A finance dashboard in Excel centers on one comparison: budget vs. actual, by category, with a variance percentage — plus a cash-flow view if the audience needs to see runway or liquidity, not just P&L performance.
Structure the source table around one join
The most common mistake is keeping budget and actuals in separate tables that have to be looked up against each other every time. Instead, build one Data table with a row per category per period, with both a Budget column and an Actual column side by side. This makes variance a one-line formula instead of a cross-sheet lookup.
Budget vs. actual with variance
Add a Variance % column to the same table:
=(Actual - Budget) / BudgetFormat it as a percentage, and add conditional formatting (Icon Sets or a two-color scale) so categories over budget on expense lines — or under target on revenue lines — are visually obvious without reading every row.
Build a PivotTable from this table with Category in Rows and Budget/Actual both in Values, then a clustered-column PivotChart. This is the chart most finance reviews open first.
Cash flow with a waterfall chart
If the dashboard needs to show cash position over a period, lay out a small table: starting balance, then one row per inflow/outflow category, then ending balance. Select it and insert Excel’s built-in Waterfall chart (Insert > Charts > Waterfall, available since Excel 2016) — mark the starting-balance and ending-balance rows as “Total” in the chart’s data formatting so they anchor as flat bars instead of floating like the inflow/outflow bars.
Trend by month or quarter
Add a PivotChart with Period (grouped by month or quarter) on the axis and Actual as the value, split by Category. This shows whether a variance is a one-off or a pattern — a single-period snapshot can’t tell the difference.
A slicer for period and category
One slicer on Period and one on Category, both connected to every PivotTable via Report Connections, lets a reviewer isolate Q3 or a single cost center without touching a formula.
Excel vs. a live finance dashboard
| Excel | Sheet2Chart | |
|---|---|---|
| Budget/actual join | One table, maintained by hand each period | Handled from the uploaded columns directly |
| Variance calculation | Formula per row, copied down each refresh | Computed automatically |
| ”Why is marketing over budget in Q3?” | Filter the PivotTable, trace rows manually | Ask the question, see the exact SQL and filters behind the answer |
Excel is a strong fit here when one person owns the budget/actual table and refreshes it each close. It gets heavier once several stakeholders need to query variance themselves, or the join between budget and actuals spans more than one source system.
See the finance use case for a version built for teams that want to ask variance questions directly instead of filtering a PivotTable.
Frequently asked questions
What's the correct formula for budget variance?
Variance % = (Actual − Budget) ÷ Budget. Keep the sign meaningful: for expense categories, a positive variance means overspending; for revenue categories, a positive variance means beating target. Label the column clearly so the sign is not ambiguous.
How do I build a waterfall chart for cash flow in Excel?
Excel 2016 and later has a built-in Waterfall chart type (Insert > Charts > Waterfall). Feed it a table of cash-flow line items (starting balance, inflows, outflows, ending balance) and mark the starting/ending rows as "totals" in the chart data so they anchor instead of floating.
Should budget and actuals live in the same table?
Keep them as two columns in one table, joined by category and period — not two separate tables. That makes a single variance formula per row possible instead of a manual lookup across sheets.