How to build a dashboard in Excel, step by step

An Excel dashboard is built on three sheets: the raw data as an Excel Table, a calculation sheet that computes each KPI, and a dashboard sheet of tiles and charts that only reads the calculation sheet. Choose four to six KPIs before you build anything, drive them from a month selector, and add a check row that proves every tile ties to the data. This guide builds one from six months of revenue, budget and gross profit.

Decide what the dashboard is for

Write down who reads it and which decision it serves before opening Excel. A monthly revenue dashboard for a sales and finance leadership meeting answers three questions: are we ahead of budget year to date, is the margin holding, and did this month improve on last. That gives the KPIs: year-to-date revenue, year-to-date budget and the variance, year-to-date gross margin, the month against budget, and the month against the prior month. Six tiles. Anything that does not serve one of the three questions belongs in a report or a list, not on the dashboard; dashboard vs report vs list covers which format fits which job. For finished layouts rather than the build, see sales dashboard examples.

The three-sheet layout

The rule on the last line is what keeps the dashboard maintainable. When a figure looks wrong there is one place to look, and when the data layout changes there is one sheet to fix.

Step 1: load the data as a Table

On the Data sheet, one row per month with four columns: Month, Revenue, Budget and Gross profit. Enter Month as the first day of each month (1/1/2026, 2/1/2026 and so on) and format it as mmm yyyy.

The margin column and the total row are shown here for reference; the Table itself holds only the four input columns. Click inside the range, press Ctrl+T, confirm that it has headers, and on the Table Design tab name it Monthly. Formulas can now use structured references such as Monthly[Revenue], which grow when new rows are added to the Table.

Step 2: compute the KPIs on Calc

The month selector. Create a name MonthList (Formulas > Define Name) that refers to =Monthly[Month]. In Calc!B1, add a Data Validation list with the source =MonthList, pick Jun 2026, and name the cell SelMonth. Name B3 YTDRev and B4 YTDBud the same way. SelMonth, YTDRev and YTDBud are named cells, which keeps the formulas below readable.

Year-to-date revenue, B3:

=SUMIFS(Monthly[Revenue],Monthly[Month],">="&DATE(YEAR(SelMonth),1,1),Monthly[Month],"<="&SelMonth)

Year-to-date budget, B4: the same formula on Monthly[Budget]. The first condition keeps the sum inside the selected year once the Table holds more than one; with 2026 only, =SUMIFS(Monthly[Revenue],Monthly[Month],"<="&SelMonth) returns the same total.

Variance, B5 and B6:

=YTDRev-YTDBud
=(YTDRev-YTDBud)/YTDBud

Year-to-date gross margin, B7, is summed gross profit over summed revenue:

=SUMIFS(Monthly[Gross profit],Monthly[Month],">="&DATE(YEAR(SelMonth),1,1),Monthly[Month],"<="&SelMonth)/YTDRev

The month against budget, B8 and B9:

=XLOOKUP(SelMonth,Monthly[Month],Monthly[Revenue])-XLOOKUP(SelMonth,Monthly[Month],Monthly[Budget])
=B8/XLOOKUP(SelMonth,Monthly[Month],Monthly[Budget])

The month against the prior month, B10:

=XLOOKUP(SelMonth,Monthly[Month],Monthly[Revenue])/XLOOKUP(EDATE(SelMonth,-1),Monthly[Month],Monthly[Revenue])-1

XLOOKUP needs Excel 2021 or Microsoft 365. In Excel 2016 or 2019, use INDEX and MATCH:

=INDEX(Monthly[Revenue],MATCH(SelMonth,Monthly[Month],0))/INDEX(Monthly[Revenue],MATCH(EDATE(SelMonth,-1),Monthly[Month],0))-1

With Jun 2026 selected, Calc shows:

The gross margin is 925,555 / 2,655,000 = 34.86%. June against May is 489,000 / 471,000 − 1 = 3.82%, an $18,000 increase.

Step 3: KPI tiles

On the Dashboard sheet, each tile is a small block of merged or bordered cells: a label, the value, and the comparison under it. The value cell holds nothing but a reference, such as =Calc!B3. For a tile you can move freely, insert a text box, click its border, and type =Calc!B3 in the formula bar.

Format the values in the tile, not on Calc: $#,##0,"K" shows 2,655,000 as $2,655K. Add conditional formatting to the variance cells so a positive revenue variance is green and a negative one red. On a cost KPI the rule flips, because spending under budget is favorable.

Step 4: the trend chart

One chart, one question: is revenue tracking budget month by month? Select the Month, Revenue and Budget columns of the Table and choose Insert > Recommended Charts, then All Charts > Combo. Show Revenue as clustered columns and Budget as a line, on the same axis. Microsoft's guide to creating a chart covers changing the chart type of one series. Because the chart reads the Table, a July row extends it without editing the source.

If there is a second question, such as whether the margin is holding, give it a second small chart. Do not add a secondary axis to the first.

Step 5: slicers and interactivity

There are two ways to make the dashboard respond to a click, and they do not mix.

Formula tiles with a dropdown. The SUMIFS tiles above respond to SelMonth. Put a copy of the dropdown on the Dashboard sheet, or link a cell there to it, and the reader changes the month in one place.

Pivot tiles with slicers. Build a PivotTable from Monthly on Calc, read each tile from it with a reference or GETPIVOTDATA, and add a slicer: click in the PivotTable, then Insert > Slicer. One slicer can drive several PivotTables through Report Connections, as long as they share the same data source. Add a Region or Product column to the data and a slicer on it filters every tile.

A slicer on the Table itself hides rows but does not change SUMIFS, which counts hidden rows. Use the dropdown for formula tiles and slicers for pivot tiles.

Step 6: the check row

Under the tiles on Calc, add a row that recomputes each figure a different way and shows the difference. With the last month selected, year-to-date revenue must equal the Table total:

=YTDRev-SUM(Monthly[Revenue])

With Jun 2026 selected this returns 0. For any month, this recomputes year to date without SUMIFS:

=YTDRev-SUMPRODUCT((YEAR(Monthly[Month])=YEAR(SelMonth))*(Monthly[Month]<=SelMonth)*Monthly[Revenue])

Do the same for budget and gross profit, then add one cell that turns red if any difference is not 0. Hide the row if you like; do not delete it. A dashboard that nobody can reconcile gets questioned in the meeting instead of used.

Where it goes wrong

• Twenty KPIs on one sheet. Nobody reads it. Four to six, each tied to a decision.
• Averaged margins. The average of the six monthly margins is 34.8%; the true year-to-date margin is 34.9%. Divide summed gross profit by summed revenue, as in gross profit margin.
• Fixed ranges. A chart or formula pointed at B2:B7 stops at June. Point everything at the Table.
• Pie and 3D charts. They hide the comparison the reader needs. Columns against a budget line show it.
• Pivots not refreshed. A pivot-based dashboard shows last month's numbers until someone clicks Data > Refresh All before sending it.

When the dashboard needs an explanation

A tile shows what moved, not why. Revenue is 2.1% ahead of budget, but the dashboard cannot say which customers or products produced the $55,000, and the questions that follow go into the monthly pack and a week of emails. That is also why a dashboard on its own gets glanced at while a list of accounts gets worked.

Covirage computes the same KPI tiles from the uploaded files with deterministic tools, reconciles them to the data, and adds the written explanation of what moved and why that an Excel tile cannot give; the external AI model writes the explanation and never does the arithmetic. See board reporting.

title: "How to build a dashboard in Excel, step by step" description: "A step-by-step guide to building a business dashboard in Excel that updates when the data changes: three sheets for data, calculations and the dashboard, KPI tiles driven by SUMIFS and a month selector, a revenue-against-budget combo chart, slicers, and a check row that proves every tile ties to the data. Worked on six months of revenue, budget and gross profit." seoTitle: "Excel dashboard: how to build one, step by step" metaDescription: "Build an Excel dashboard on three sheets: data, calculations and dashboard. KPI tiles, a trend chart and slicers, each number checked to the data." date: 2026-09-30 category: guides solution: board-reporting keywords: ["excel dashboard", "create dashboard using excel", "business dashboard in excel", "creating a dashboard in excel", "how to make a dashboard in excel", "excel kpi dashboard", "interactive dashboard excel"] answer: "Build an Excel dashboard on three sheets: Data (the raw rows as an Excel Table), Calc (SUMIFS or a pivot table computing each KPI) and Dashboard (tiles and charts that only reference Calc). Pick four to six KPIs first, add a month selector, chart the trend, add slicers, and check every tile against the data total. Refresh by pasting new rows into the Table." faq:

• q: "Can you make a dashboard in Excel?" a: "Yes. Excel has everything a management dashboard needs: Tables for data, SUMIFS or pivot tables for calculations, charts, conditional formatting and slicers for filtering. It suits a monthly reporting package for a team; it strains when many people need live, permissioned views."
• q: "What should an Excel dashboard include?" a: "Four to six KPIs tied to the decisions the reader makes, each against a comparison such as budget or last year, one trend chart, and a filter for the period. Add a check that every figure reconciles to the underlying data."
• q: "How do I make an Excel dashboard update automatically?" a: "Keep the data in an Excel Table and point every formula and chart at the Table, so new rows are included. Pivot tables need Refresh All. If the data comes from a file export, load it with Power Query so one refresh reloads it."
• q: "Is Excel or Power BI better for dashboards?" a: "Excel is quicker to build and everyone can open it; Power BI handles larger data, scheduled refresh and shared access with row-level security. Many finance teams start in Excel and move when refresh or distribution becomes the bottleneck."

An Excel dashboard is built on three sheets: the raw data as an Excel Table, a calculation sheet that computes each KPI, and a dashboard sheet of tiles and charts that only reads the calculation sheet. Choose four to six KPIs before you build anything, drive them from a month selector, and add a check row that proves every tile ties to the data. This guide builds one from six months of revenue, budget and gross profit.

Decide what the dashboard is for

Write down who reads it and which decision it serves before opening Excel. A monthly revenue dashboard for a sales and finance leadership meeting answers three questions: are we ahead of budget year to date, is the margin holding, and did this month improve on last. That gives the KPIs: year-to-date revenue, year-to-date budget and the variance, year-to-date gross margin, the month against budget, and the month against the prior month. Six tiles. Anything that does not serve one of the three questions belongs in a report or a list, not on the dashboard; dashboard vs report vs list covers which format fits which job. For finished layouts rather than the build, see sales dashboard examples.

The three-sheet layout

The rule on the last line is what keeps the dashboard maintainable. When a figure looks wrong there is one place to look, and when the data layout changes there is one sheet to fix.

Step 1: load the data as a Table

On the Data sheet, one row per month with four columns: Month, Revenue, Budget and Gross profit. Enter Month as the first day of each month (1/1/2026, 2/1/2026 and so on) and format it as mmm yyyy.

The margin column and the total row are shown here for reference; the Table itself holds only the four input columns. Click inside the range, press Ctrl+T, confirm that it has headers, and on the Table Design tab name it Monthly. Formulas can now use structured references such as Monthly[Revenue], which grow when new rows are added to the Table.

Step 2: compute the KPIs on Calc

The month selector. Create a name MonthList (Formulas > Define Name) that refers to =Monthly[Month]. In Calc!B1, add a Data Validation list with the source =MonthList, pick Jun 2026, and name the cell SelMonth. Name B3 YTDRev and B4 YTDBud the same way. SelMonth, YTDRev and YTDBud are named cells, which keeps the formulas below readable.

Year-to-date revenue, B3:

=SUMIFS(Monthly[Revenue],Monthly[Month],">="&DATE(YEAR(SelMonth),1,1),Monthly[Month],"<="&SelMonth)

Year-to-date budget, B4: the same formula on Monthly[Budget]. The first condition keeps the sum inside the selected year once the Table holds more than one; with 2026 only, =SUMIFS(Monthly[Revenue],Monthly[Month],"<="&SelMonth) returns the same total.

Variance, B5 and B6:

=YTDRev-YTDBud
=(YTDRev-YTDBud)/YTDBud

Year-to-date gross margin, B7, is summed gross profit over summed revenue:

=SUMIFS(Monthly[Gross profit],Monthly[Month],">="&DATE(YEAR(SelMonth),1,1),Monthly[Month],"<="&SelMonth)/YTDRev

The month against budget, B8 and B9:

=XLOOKUP(SelMonth,Monthly[Month],Monthly[Revenue])-XLOOKUP(SelMonth,Monthly[Month],Monthly[Budget])
=B8/XLOOKUP(SelMonth,Monthly[Month],Monthly[Budget])

The month against the prior month, B10:

=XLOOKUP(SelMonth,Monthly[Month],Monthly[Revenue])/XLOOKUP(EDATE(SelMonth,-1),Monthly[Month],Monthly[Revenue])-1

XLOOKUP needs Excel 2021 or Microsoft 365. In Excel 2016 or 2019, use INDEX and MATCH:

=INDEX(Monthly[Revenue],MATCH(SelMonth,Monthly[Month],0))/INDEX(Monthly[Revenue],MATCH(EDATE(SelMonth,-1),Monthly[Month],0))-1

With Jun 2026 selected, Calc shows:

The gross margin is 925,555 / 2,655,000 = 34.86%. June against May is 489,000 / 471,000 − 1 = 3.82%, an $18,000 increase.

Step 3: KPI tiles

On the Dashboard sheet, each tile is a small block of merged or bordered cells: a label, the value, and the comparison under it. The value cell holds nothing but a reference, such as =Calc!B3. For a tile you can move freely, insert a text box, click its border, and type =Calc!B3 in the formula bar.

Format the values in the tile, not on Calc: $#,##0,"K" shows 2,655,000 as $2,655K. Add conditional formatting to the variance cells so a positive revenue variance is green and a negative one red. On a cost KPI the rule flips, because spending under budget is favorable.

Step 4: the trend chart

One chart, one question: is revenue tracking budget month by month? Select the Month, Revenue and Budget columns of the Table and choose Insert > Recommended Charts, then All Charts > Combo. Show Revenue as clustered columns and Budget as a line, on the same axis. Microsoft's guide to creating a chart covers changing the chart type of one series. Because the chart reads the Table, a July row extends it without editing the source.

If there is a second question, such as whether the margin is holding, give it a second small chart. Do not add a secondary axis to the first.

Step 5: slicers and interactivity

There are two ways to make the dashboard respond to a click, and they do not mix.

Formula tiles with a dropdown. The SUMIFS tiles above respond to SelMonth. Put a copy of the dropdown on the Dashboard sheet, or link a cell there to it, and the reader changes the month in one place.

Pivot tiles with slicers. Build a PivotTable from Monthly on Calc, read each tile from it with a reference or GETPIVOTDATA, and add a slicer: click in the PivotTable, then Insert > Slicer. One slicer can drive several PivotTables through Report Connections, as long as they share the same data source. Add a Region or Product column to the data and a slicer on it filters every tile.

A slicer on the Table itself hides rows but does not change SUMIFS, which counts hidden rows. Use the dropdown for formula tiles and slicers for pivot tiles.

Step 6: the check row

Under the tiles on Calc, add a row that recomputes each figure a different way and shows the difference. With the last month selected, year-to-date revenue must equal the Table total:

=YTDRev-SUM(Monthly[Revenue])

With Jun 2026 selected this returns 0. For any month, this recomputes year to date without SUMIFS:

=YTDRev-SUMPRODUCT((YEAR(Monthly[Month])=YEAR(SelMonth))*(Monthly[Month]<=SelMonth)*Monthly[Revenue])

Do the same for budget and gross profit, then add one cell that turns red if any difference is not 0. Hide the row if you like; do not delete it. A dashboard that nobody can reconcile gets questioned in the meeting instead of used.

Where it goes wrong

• Twenty KPIs on one sheet. Nobody reads it. Four to six, each tied to a decision.
• Averaged margins. The average of the six monthly margins is 34.8%; the true year-to-date margin is 34.9%. Divide summed gross profit by summed revenue, as in gross profit margin.
• Fixed ranges. A chart or formula pointed at B2:B7 stops at June. Point everything at the Table.
• Pie and 3D charts. They hide the comparison the reader needs. Columns against a budget line show it.
• Pivots not refreshed. A pivot-based dashboard shows last month's numbers until someone clicks Data > Refresh All before sending it.

When the dashboard needs an explanation

A tile shows what moved, not why. Revenue is 2.1% ahead of budget, but the dashboard cannot say which customers or products produced the $55,000, and the questions that follow go into the monthly pack and a week of emails. That is also why a dashboard on its own gets glanced at while a list of accounts gets worked.

Covirage computes the same KPI tiles from the uploaded files with deterministic tools, reconciles them to the data, and adds the written explanation of what moved and why that an Excel tile cannot give; the external AI model writes the explanation and never does the arithmetic. See board reporting. For finished layouts to copy, see Excel dashboard examples and the KPI dashboard template.