How to Create a Dashboard in Excel (a simple one that works)

A dashboard is one sheet that answers a few questions at a glance. Build it in this order.

1. Decide the questions

Three to five, e.g. total sales this month, sales by region, trend by month, top 5 products.

2. Put the data in a Table

One row per record, one header row, no blank rows. Select it › Ctrl+T. New rows are then picked up automatically. Excel Tables.

3. One pivot table per question

Insert › PivotTable on a sheet called Calc. One pivot for region, one for month, one for top products. Make a pivot table.

4. KPI cards

On a sheet called Dashboard, big numbers pulled from the pivots or straight from the table:

=SUM(Sales[Amount])
=COUNTA(Sales[Order])

Put each in a large, bold cell with a label above. Or link a text box to it.

5. Charts

Click a pivot › PivotTable Analyze › PivotChart. Cut and paste the chart onto the Dashboard sheet. Column for comparisons, line for trends. Which chart type.

6. Slicers that filter everything

1. Click a pivot › Insert Slicer › Region.
2. Right-click the slicer › Report Connections › tick every pivot.

One click now filters all charts. Slicers.

7. Tidy

• View › untick Gridlines and Headings.
• Line things up to the grid (hold Alt while dragging).
• Refresh: Data › Refresh All after new data.