A good production dashboard answers three questions quickly: Are we on target? Where are we behind? Why? You can build a useful one in Excel in under an hour.
Step 1 - One clean data sheet
Enter data as a simple list, one row per line per hour. Never merge cells in a data sheet.
| Date | Line | Hour | Target | Output | Operators | SAM |
|---|---|---|---|---|---|---|
| 29-Sep | Line-1 | 8-9 | 110 | 96 | 38 | 12.5 |
| 29-Sep | Line-1 | 9-10 | 110 | 112 | 38 | 12.5 |
Select it and press Ctrl + T to make it a Table named Prod.
Step 2 - Add calculated columns
- Variance:
=[@Output]-[@Target] - Efficiency:
=[@Output]*[@SAM]/([@Operators]*60)
Format efficiency as a percentage.
Step 3 - Pivot table
Insert → PivotTable from Prod. Put Line in Rows, Hour in Columns, Output in Values. Add a second pivot with Target and Output by Line.
Step 4 - Charts
- A clustered column chart of Target vs Output by line.
- A line chart of efficiency by hour, to show slow hours.
Step 5 - Slicers and colour
Add slicers for Date and Line so managers can click instead of filtering. Use conditional formatting on variance: red when behind target, green when ahead.
Tip: Keep the data sheet, pivots and dashboard on separate sheets. People type into the data sheet only; the dashboard updates with Data → Refresh All.
Download the free Hourly Production Report template from the Knowledge Hub downloads.