How to Build a KPI Dashboard in Excel from Scratch

The build, stage by stage

A complete one-page KPI dashboard tracking sales, staffing and customer metrics: actual vs plan, order volumes, new hires and terminations, sick days, wait times, satisfaction and Net Promoter Score. The workbook is structured the professional way, inputs then calculations then dashboard, and a region slicer filters everything with one click. These are the main stages; the finished file is in the free download.

1. Set up the inputs

An assumptions sheet holds the settings (such as the year and the plan figures) and the raw data sheets hold the transactions. Nothing on the dashboard is typed by hand: everything is calculated from these inputs.

2. Summarise sales with SUMIFS

A calculations sheet uses SUMIFS to total sales by month, department, type and region, both actual and plan. SUMIFS adds up a column only where several conditions are all true, which makes it the workhorse of most dashboards.

3. Count orders with COUNTIFS

COUNTIFS counts the number of orders that meet the same kinds of conditions, giving order volumes by month and region.

4. Staffing metrics

More SUMIFS formulas total new hires, terminations and sick days for each month, ready for the HR charts.

5. Customer metrics with SUMPRODUCT

For grid-shaped tables, such as Net Promoter Score and wait times by month and region, SUMPRODUCT pulls the right value with several criteria at once, where SUMIFS gets awkward. Customer and employee satisfaction scores are calculated the same way.

6. Lay out the dashboard and add icons

The key figures are moved onto the dashboard page, and conditional formatting with custom icon sets shows at a glance whether each KPI is on or off target.

7. Charts

Column charts compare actual and planned sales; a PivotTable ranks salesperson performance; stacked column charts show hires, terminations and sick days; and doughnut charts display the satisfaction scores.

8. Add a region slicer

Finally, a slicer for region is connected to the PivotTable and the calculations, so one click filters the whole dashboard to a single region.