How to Build an Excel Dashboard

An Excel dashboard is a single worksheet that summarizes key figures with tiles, charts and tables, driven by data in the same workbook and refreshed with a few clicks. For many finance teams it is the right first dashboard: everyone already has Excel, no new software or budget is needed, and the logic is visible. The risk is that a dashboard built on pasted values and tangled formulas becomes fragile and hard to hand over. This guide shows how to build an Excel dashboard in eight steps with a clear structure: a data tab, a calculation tab and a dashboard tab, with tables, Power Query, PivotTables, formulas for KPI tiles, charts, slicers and checks. It includes a worked KPI tile calculation with checked arithmetic. It is part of business analytics for finance and operations, follows data visualization for finance and dashboard best practices, and leads to Looker Studio for finance reporting and self-service BI. The dashboard supports the monthly cycle in budgeting and forecasting.

Step 1: Plan on paper

Write the purpose, the reader and the five to ten questions the dashboard should answer. Sketch the layout: a row of KPI tiles at the top, two or three charts below, and a table at the bottom. Decide the period selector (month, year-to-date) and the dimensions to filter by (entity, department, product). Planning prevents the common problem of building charts and then looking for a purpose.

Step 2: Organize the workbook

Use at least three tabs.

Add a fourth tab for definitions and notes, and a fifth for checks. Never put calculations on the dashboard tab that readers will not understand. Keep the dashboard tab for presentation only.

Step 3: Prepare the data as tables

Data should be in a flat layout: one row per record, one column per field, a header row and no blank rows or merged cells. Format each block as an Excel Table (Ctrl+T) and give it a name, such as tblSales. Tables expand automatically as rows are added and allow structured references in formulas. Typical fields for a revenue analysis are date, month, customer, product, region, units, revenue and cost. Add a month-start date column so that you can group and filter by period.

If the data arrives from an accounting or order system export, use Power Query (Get & Transform Data) to import and clean it: remove blank rows, set data types, split or merge columns, and append new files. Power Query records each step, so the process repeats with a single Refresh. This is the biggest improvement you can make to a manual workbook, because it replaces repeated copy-and-paste with a repeatable query.

Step 4: Build the calculations

Use PivotTables for summaries, such as revenue by month and product, and use formulas for KPI tiles and comparisons. Useful formulas include:

• =SUMIFS(tblSales[Revenue], tblSales[Month], $B$2) for revenue in the selected month.
• =SUMIFS(tblSales[Revenue], tblSales[Month], EDATE($B$2,-12)) for the same month last year.
• =IFERROR(C5/D5-1, 0) for percentage change, with a guard against division by zero.
• =SUMIFS(tblSales[Revenue], tblSales[Month], ">="&DATE(YEAR($B$2),1,1), tblSales[Month], "<="&$B$2) for year-to-date.
• =INDEX(Targets[Revenue], MATCH($B$2, Targets[Month], 0)) for the target in the selected month.

Put the selected month in one cell, such as B2 on the Calc tab, linked to a drop-down on the dashboard. All formulas refer to that cell, so changing the month updates the whole workbook.

Step 5: A worked KPI tile

Suppose the selected month is June. Revenue is $4.35 million, the budget is $4.80 million and the same month last year was $4.10 million (hypothetical). The tile shows:

• Actual: $4.35M
• Variance to budget: 4.35 - 4.80 = -$0.45M, which is 4.35 / 4.80 - 1 = -9.4 percent
• Change from last year: 4.35 / 4.10 - 1 = +6.1 percent
• Status: red if below 95 percent of budget; here the ratio is 4.35 / 4.80 = 90.6 percent, so the tile is red.

Check: 0.45 / 4.80 = 0.09375; 4.35 / 4.10 = 1.0610; 4.35 / 4.80 = 0.90625. The story on the tile is that revenue grew year on year but missed the plan, which means the plan was ambitious or events happened that need explanation. Add a text cell with the comment, linked to a cell where the analyst types the reason.

For the status color, use conditional formatting with a rule based on the ratio of actual to target. For measures where lower is better, such as days sales outstanding, invert the ratio. Keep the thresholds in cells on the Calc tab so that you can change them without editing rules.

Step 6: Add charts

Build charts from PivotTables or from named ranges on the Calc tab. For a revenue trend, use a line chart with actual and budget series. For category comparisons, use a sorted bar chart. For a variance bridge, use the built-in waterfall chart in recent versions of Excel, or build one with stacked columns. Format deliberately: remove gridlines and the chart border, set a consistent font, label the axes with units, write a title that states the finding, use one accent color and gray for comparisons. Link chart titles to cells so that they update with the selection. See data visualization for finance.

Step 7: Add slicers and navigation

Slicers are buttons that filter PivotTables and charts. Insert slicers for the dimensions that matter, such as region and product line, and connect them to all PivotTables that feed the dashboard (Report Connections). Add a timeline slicer for dates. Use a drop-down (data validation) for the report month if you use formulas. Group the controls at the top or the side, and label them. Do not add more filters than readers need.

Step 8: Add checks and protect

Create a Checks tab with simple tests whose result shows OK or Check:

• Total revenue on the dashboard equals the sum of the data table.
• Revenue by region sums to total revenue.
• The number of months in the data matches the expected number.
• No blank values in key fields, and no negative units where not expected.
• Total revenue for the month matches the general ledger figure you enter as a control total.

Show the overall result on the dashboard in a small cell. Then protect the formula cells and the dashboard sheet (with a password you record) so that users do not overwrite formulas, and leave the slicers and drop-downs usable. Save a version history, with a date in the file name or via a document library.

Refreshing each month

A routine of five steps keeps the workbook healthy. Put the new export in the source folder. Refresh all queries (Data > Refresh All). Refresh the PivotTables if they don't refresh automatically. Check the Checks tab. Update the commentary. Then save a copy for the record and publish the dashboard as a PDF if readers need a fixed view. With Power Query and tables, the monthly update can take minutes.

Limits of Excel dashboards

Excel dashboards work well for a modest number of users, a few hundred thousand rows and monthly or weekly refresh. They struggle with very large data, simultaneous editing, row-level security and scheduled refresh without a server. If you need those, consider a business intelligence tool or a database behind the workbook. A well-built Excel dashboard is a good prototype for a later move: the definitions, layout and checks carry over. See best BI tools for small business.

Common mistakes

1. Merging cells and mixing data with presentation.
2. Hard-coding numbers in formulas.
3. Pasting values, which breaks the link to the data.
4. Too many colors and decorations.
5. No checks.
6. A volatile web of formulas that only the author understands. Document each formula's purpose on the Calc tab.
7. No version control.

Want help building one?

If you would like an Excel or Power BI dashboard built around your own systems, with automated refresh and reconciliation checks, contact us. It is a core part of our Analytics and Planning Automation work.

FAQs

How do I make a dashboard in Excel?

Organize data in tables, build calculations with PivotTables and formulas, create charts and KPI tiles on a separate dashboard sheet, add slicers and checks, and protect the formulas.

What is Power Query used for in a dashboard?

To import and clean data in repeatable steps, so that refreshing the workbook updates everything without manual copying.

How many rows can an Excel dashboard handle?

Excel can hold over a million rows per sheet, but dashboards become slow well before then. Use summarized data or the data model for larger sets.

How do I make an Excel dashboard update automatically?

Use Power Query and tables with Refresh All. For fully automated refresh without opening the file, a server or a BI tool is needed.

Should I use Excel or a BI tool?

Excel is a good start for a small team and moderate data. A BI tool helps with sharing, security, larger data and scheduled refresh.