Project Dashboard Template in Excel: Progress, Milestones, Budget and Risks on One Page
Build a project dashboard template in Excel: actual vs planned progress, late milestones, earned value with CPI and SPI, open high risks and a RAG status, all fed from your project plan without retyping.
A project dashboard template in Excel summarizes a project on one page: actual progress against plan, milestones met or late, money spent compared with work completed, open high risks and an overall red, amber or green status. It does not replace the project plan; it reads from it. Tasks, dates and costs stay on working tabs, and the dashboard holds only formulas and charts. This guide covers which indicators to show, how to structure the workbook and how to read the numbers.
The indicators that matter
| Indicator | Calculation | Question it answers |
|---|---|---|
| Actual progress | Completed effort ÷ total effort (weighted) | Where are we really? |
| Planned progress | Effort due by today ÷ total effort | Where should we be? |
| Milestones | Met, upcoming, late | Are we holding the key dates? |
| Spend | Actual cost ÷ budget | How much have we used? |
| Cost performance index (CPI) | Earned value ÷ actual cost | Is completed work costing what we planned? |
| Schedule performance index (SPI) | Actual progress ÷ planned progress | Are we behind? |
| High risks | Open risks above the threshold score | What could derail us? |
Workbook structure
- Tasks: phase, owner, start, finish, effort in days, % complete, status, planned and actual cost. This is the plan you already keep in the project management template.
- Milestones: planned date, actual date, status.
- Risks: the risk register with likelihood, impact and score.
- Dashboard: one printable page with formulas and charts only.
Format each tab as a named Table (Tasks, Milestones, Risks) and put the reporting date in a cell named StatusDate, defaulting to =TODAY().
Formulas
- Weighted actual progress:
=SUMPRODUCT(Tasks[Effort],Tasks[PctComplete])/SUM(Tasks[Effort]) - Planned progress:
=SUMIFS(Tasks[Effort],Tasks[Finish],"<="&StatusDate)/SUM(Tasks[Effort]) - Overdue tasks:
=COUNTIFS(Tasks[Finish],"<"&StatusDate,Tasks[Status],"<>Done") - Late milestones:
=COUNTIFS(Milestones[Planned],"<"&StatusDate,Milestones[Actual],"") - Earned value:
=ActualProgress*Budget; CPI:=IFERROR(EarnedValue/SUM(Tasks[ActualCost]),""); SPI:=IFERROR(ActualProgress/PlannedProgress,"") - Open high risks:
=COUNTIFS(Risks[Score],">="&HighThreshold,Risks[Status],"Open")
The RAG status
=IF(OR(SPI<0.85,CPI<0.85,HighRisks>=3),"Red",IF(OR(SPI<0.95,CPI<0.95,LateMilestones>0),"Amber","Green"))
The thresholds are examples. Agree them at kickoff and keep them fixed; changing them mid-project makes the status meaningless.
Example: launching a customer portal
| Indicator | As of June 30 |
|---|---|
| Planned / actual progress | 45% / 40% |
| SPI | 0.89 |
| Budget / actual cost | $48,000 / $21,500 |
| Earned value | $19,200 |
| CPI | 0.89 |
| Late milestones | 0 |
| Open high risks | 2 |
| Status | Amber |
The project is a little behind and a little over cost per unit of work, both by about 11%, with no milestone missed yet. That is exactly when a dashboard earns its keep: the steering meeting can decide now whether to trim scope for the first release or add budget, before amber turns red.
Useful charts
- An S-curve of cumulative planned and actual progress by week.
- Columns of budget versus actual cost by phase.
- A list of the next five tasks and the next four weeks of milestones, pulled with FILTER.
To present the key dates, add the project timeline template; to make ownership clear, the RACI matrix template. The day-to-day schedule stays in the Gantt chart. More guides are in Project Management.
Good habits
- Update % complete the day before the status meeting, not during it.
- Measure progress by verifiable deliverables: 0%, 50% or 100% is often more honest than an estimated 80%.
- Write one sentence of explanation and one action next to every amber or red indicator.
- Save a frozen copy at each status meeting so trends are visible.
Frequently asked questions
What should a project dashboard show?
Actual and planned progress, milestones, spend and earned value, and open high risks. Five or six indicators are enough for a small business project.
What is earned value?
The share of the budget that matches the work actually done: actual progress × budget. Compared with actual cost, it shows whether work is costing more or less than planned.
How often should it be updated?
Before each status meeting, usually weekly or every two weeks.
Does it work in Google Sheets?
Yes. SUMPRODUCT, SUMIFS, COUNTIFS and FILTER all work the same way.