How to build an Excel dashboard: KPIs, charts and slicers
An Excel dashboard puts the figures you check most often on a single sheet: a few key indicators at the top, two or three charts, and filter buttons (slicers) to switch region or product in one click. It is built in three layers: a data sheet formatted as a table, calculation sheets (formulas and pivot tables), and the display sheet, which holds only the result.
The example below tracks one quarter of sales. Allow about an hour to build it the first time.
1. Decide what the dashboard should show
Before opening Excel, write down the questions the page has to answer. Here:
- How much did we sell this quarter, in how many sales, with what average sale value?
- Is revenue growing from one month to the next?
- Which rep contributes most, and in which region?
Each question becomes one element of the page: KPIs for the first, a column chart by month for the second, a bar chart by rep for the third. Anything that answers none of the questions does not belong on the dashboard.
2. Put the data in a table
Sales are entered on a sheet called Data, one row per sale (amounts in pounds), with no blank rows and no subtotals:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Rep | Region | Product | Qty | Amount |
| 2 | 05/01/2026 | Emma | North | Monitor | 4 | 720 |
| 3 | 09/01/2026 | James | South | Printer | 2 | 500 |
| 4 | 14/01/2026 | Olivia | North | Keyboard | 15 | 600 |
| 5 | 21/01/2026 | Emma | South | Printer | 3 | 750 |
| 6 | 28/01/2026 | James | North | Monitor | 5 | 900 |
| 7 | 03/02/2026 | Olivia | South | Monitor | 2 | 360 |
| 8 | 10/02/2026 | Emma | North | Keyboard | 20 | 800 |
| 9 | 17/02/2026 | James | South | Keyboard | 10 | 400 |
| 10 | 24/02/2026 | Olivia | North | Printer | 4 | 1,000 |
| 11 | 03/03/2026 | Emma | South | Monitor | 6 | 1,080 |
| 12 | 10/03/2026 | James | North | Printer | 1 | 250 |
| 13 | 17/03/2026 | Olivia | South | Keyboard | 12 | 480 |
| 14 | 24/03/2026 | Emma | North | Printer | 2 | 500 |
| 15 | 31/03/2026 | James | South | Monitor | 3 | 540 |
Click inside the list, choose Insert > Table, keep My table has headers ticked and click OK. On the Table Design tab, in the Table Name box, replace Table1 with Sales.
Everything else depends on this step: formulas that refer to Sales[Amount] and pivot tables built on Sales automatically include rows added below the table. The dashboard updates without you touching a single range.
3. Calculate the KPIs
Add a sheet called Dashboard and keep a block at the top for the KPIs. The formulas use structured references to the Sales table: the column name in square brackets replaces the range F2:F15 and grows with it.
| A | B | Formula in B | |
|---|---|---|---|
| 3 | Quarter revenue | £8,880 | =SUM(Sales[Amount]) |
| 4 | Number of sales | 14 | =ROWS(Sales) |
| 5 | Average sale | £634.29 | =B3/B4 |
| 6 | Units sold | 89 | =SUM(Sales[Qty]) |
| 7 | North revenue | £4,770 | =SUMIFS(Sales[Amount],Sales[Region],"North") |
| 8 | March revenue | £2,850 | =SUMIFS(Sales[Amount],Sales[Date],">="&DATE(2026,3,1),Sales[Date],"<"&DATE(2026,4,1)) |
| 9 | February revenue | £2,560 | =SUMIFS(Sales[Amount],Sales[Date],">="&DATE(2026,2,1),Sales[Date],"<"&DATE(2026,3,1)) |
| 10 | Change, March vs February | 11.33% | =B8/B9-1 |
For a single month, SUMIFS takes two conditions on the same date column: on or after the first day of the month, and strictly before the first day of the next month. The change works out as (2,850 − 2,560) / 2,560 = 11.33%. Apply the Percentage format to B10 and a currency format to B3, B5, B7, B8 and B9.
A KPI that follows the user's choice
Type "Rep" in A12 and create a drop-down list in B12 offering Emma, James and Olivia. Then:
| A | B | Formula in B | |
|---|---|---|---|
| 12 | Rep | Emma | (drop-down list) |
| 13 | Rep revenue | £3,850 | =SUMIFS(Sales[Amount],Sales[Rep],B12) |
| 14 | Number of sales | 5 | =COUNTIFS(Sales[Rep],B12) |
| 15 | Share of total revenue | 43.36% | =B13/B3 |
Pick James in the list: B13 changes to £2,590, B14 stays at 5 and B15 shows 29.17%. To add a second condition (that rep in the North only), add another range/criteria pair: =SUMIFS(Sales[Amount],Sales[Rep],B12,Sales[Region],"North") returns 2,020 for Emma. To show a fixed detail about the chosen rep next to it, such as a quarterly target kept in a small lookup table, fetch it with VLOOKUP.
4. Set up the pivot tables
The dashboard charts are driven by pivot tables kept on separate calculation sheets. Each step is explained in detail in our pivot table guide; here are the two you need.
Pivot table 1, sales by month. Click inside the Sales table, choose Insert > PivotTable, New Worksheet, OK. Put Date in Rows and Amount in Values. If the dates stay day by day, right-click a date > Group > Months. Rename the sheet "Calc month".
| A | B | |
|---|---|---|
| 3 | Row Labels | Sum of Amount |
| 4 | Jan | 3470 |
| 5 | Feb | 2560 |
| 6 | Mar | 2850 |
| 7 | Grand Total | 8880 |
Pivot table 2, sales by rep. Same steps on a new sheet called "Calc rep", with Rep in Rows and Amount in Values.
| A | B | |
|---|---|---|
| 3 | Row Labels | Sum of Amount |
| 4 | Emma | 3850 |
| 5 | James | 2590 |
| 6 | Olivia | 2440 |
| 7 | Grand Total | 8880 |
Both pivot tables use the same source (the Sales table). That is what allows one slicer to filter both.
5. Add the charts
- Click inside the pivot table on Calc month, then choose PivotTable Analyze > PivotChart.
- Choose Column, first layout (clustered column), and click OK.
- Select the chart, cut it (Ctrl + X) and paste it (Ctrl + V) on the Dashboard sheet, below the KPIs.
- Repeat with the pivot table on Calc rep, choosing Bar: names are easier to read horizontally.
On each chart, delete the legend (with one series it adds nothing), give it a title that says what you are looking at ("Revenue by month, £") and hide the grey field buttons (right-click one of them, then the command that hides all field buttons on the chart). Chart types, axes and data labels are covered in how to make a chart in Excel.
6. Add slicers to filter
- Click inside either pivot table, then choose PivotTable Analyze > Insert Slicer.
- Tick Region and Product and click OK. Two button panels appear.
- Cut and paste them onto the Dashboard sheet, to the left of the charts.
- Right-click the Region slicer > Report Connections. Tick both pivot tables and click OK. Do the same for the Product slicer.
Now click North in the Region slicer. Both pivot tables, and therefore both charts, show the North only:
| A | B | |
|---|---|---|
| 3 | Row Labels | Sum of Amount |
| 4 | Jan | 2220 |
| 5 | Feb | 1800 |
| 6 | Mar | 750 |
| 7 | Grand Total | 4770 |
| A | B | |
|---|---|---|
| 3 | Row Labels | Sum of Amount |
| 4 | Emma | 2020 |
| 5 | James | 1150 |
| 6 | Olivia | 1600 |
| 7 | Grand Total | 4770 |
Hold Ctrl while clicking to select several items. The small button at the top right of the slicer clears the filter. To filter by period, PivotTable Analyze > Insert Timeline adds a timeline on the Date field, connected to the pivot tables in the same way.
7. Lay out the page
- Gridlines: untick View > Gridlines on the Dashboard sheet so it reads like a report.
- Layout: KPIs in a row at the top, slicers on the left, charts on the right. Hold Alt while moving objects and they snap to the cell edges.
- Colours: one colour for the data, a second one for what should stand out. To colour the change figure green or red automatically, or add data bars to a table, see conditional formatting.
- Calculation sheets: hide Calc month and Calc rep (right-click the sheet tab > Hide). Charts and slicers keep working.
- Printing: under File > Print, choose Landscape Orientation and Fit Sheet on One Page.
8. Update it every month
Type or paste new sales directly below the last row of the Sales table and it grows automatically. The KPI formulas recalculate at once. Pivot tables and their charts wait for a refresh: Data > Refresh All or Ctrl + Alt + F5. So that nobody forgets, tick Refresh data when opening the file in each pivot table's options (right-click > PivotTable Options, Data tab).
The monthly KPIs (B8 and B9) have dates typed into the formula. To move them on a month without rewriting anything, type the first day of the month you are tracking in a cell (for example 01/03/2026 in F3), then replace DATE(2026,3,1) with F3 and DATE(2026,4,1) with EDATE(F3,1).
Common problems
The slicer filters only one chart
The second pivot table is not ticked in Report Connections. If it is missing from the list, it was built on another source (a range A1:F15 instead of the Sales table, or another workbook): rebuild it from Sales.
The layout shifts at every refresh
By default a pivot table resizes its columns when it refreshes, which can push the objects next to it. In PivotTable Options, Layout & Format tab, untick Autofit column widths on update.
A KPI shows 0
The criterion matches no cell: a typo ("north" works, "North" followed by a space does not), dates entered as text, or amounts stored as text. The causes and fixes are the same as for COUNTIFS and are covered in COUNTIFS not working.
