An Excel dashboard puts the key numbers and charts of a report on one screen. Here is a simple way to build one.
1. Decide the questions
Write down 4 to 6 KPIs the reader cares about, such as total sales, growth, top products and sales by region.
2. Prepare the data
Keep raw data on its own sheet, as an Excel Table (Ctrl + T), with clean headers and no blanks.
3. Calculate the KPIs
Use SUMIFS, COUNTIFS or PivotTables on a separate calculation sheet. Keep each KPI in its own labelled cell.
4. Add charts
- Column or bar chart to compare categories.
- Line chart for a trend over time.
- Avoid 3D charts and too many colours.
5. Add slicers
With PivotTables, insert slicers (PivotTable Analyze, Insert Slicer) so users can filter by region, product or month, and connect them to all PivotTables.
6. Lay it out
- KPI numbers at the top, charts below.
- Align everything on a grid and turn off gridlines (View tab).
- Use one or two colours and clear titles.
7. Test it
Check each number against the raw data, and test the slicers.