Project Management Template in Excel: Task List, Timeline, Budget and Status Dashboard
How to build a project management template in Excel: project summary, task list with owners and dates, weighted progress, overdue flags, budget vs actual with earned value, risk and issue log, and a one-page status dashboard.
Quick answer: a practical project management template in Excel is a workbook with five tabs: a project summary (goal, scope, dates, owner), a task list (task, owner, start, end, status, % complete), a timeline view, a budget tab (planned vs actual cost) and a risks & issues log. A small dashboard pulls the key numbers together: overall progress weighted by task duration, overdue tasks, budget variance and a red/amber/green status. Everything below uses standard Excel formulas that also work in Google Sheets.
This template suits small and mid-sized projects such as a website launch, an office move or a product release. For very large programs with hundreds of dependent tasks, dedicated scheduling software is a better fit.
The five tabs at a glance
| Tab | What it holds | Who updates it |
|---|---|---|
| Summary | Goal, scope, out of scope, sponsor, project manager, start and target end date, budget | Project manager, at kickoff |
| Tasks | Every task with owner, dates, status, % complete, dependency | Task owners, weekly |
| Timeline | A bar view of the task dates | Updates automatically |
| Budget | Planned cost by category, actual cost to date | Project manager, monthly |
| Risks & Issues | What could go wrong, what already went wrong, owner, action | Whole team |
Step 1: The task list
Convert the range to a table (Ctrl+T) named Tasks with these columns:
| Column | Field | Formula or input |
|---|---|---|
| A | ID | 1, 1.1, 1.2… (phase.task) |
| B | Task | Start with a verb: “Write homepage copy” |
| C | Owner | One name, not a team |
| D | Start | Date |
| E | End | Date |
| F | Duration (workdays) | =NETWORKDAYS([@Start],[@End],Holidays) |
| G | Status | Dropdown: Not started, In progress, Blocked, Done |
| H | % complete | 0%, 25%, 50%, 75%, 100% |
| I | Depends on | ID of the task that must finish first |
| J | Overdue? | =IF(AND([@End]<TODAY(),[@Status]<>"Done"),"Overdue","") |
| K | Due this week? | =IF(AND([@Status]<>"Done",[@End]>=TODAY(),[@End]<=TODAY()+7),"Due soon","") |
Holidays is a named range with your public holidays, so durations count real working days. Keep % complete to a few fixed steps: “63% done” is false precision and hard to verify.
Checking dependencies
To flag a task that starts before its predecessor ends, add a column:
=IF([@[Depends on]]="","",IF([@Start]<=XLOOKUP([@[Depends on]],Tasks[ID],Tasks[End]),"Check dates",""))
This does not reschedule anything automatically, but it catches the most common planning error: a test phase that starts before development finishes.
Step 2: Progress that means something
A simple average of % complete treats a two-day task the same as a three-week task. Weight progress by duration instead:
=SUMPRODUCT(Tasks[Duration (workdays)],Tasks[% complete])/SUM(Tasks[Duration (workdays)])
Example: website redesign
| Task | Duration (days) | % complete | Weighted days done |
|---|---|---|---|
| Kickoff and requirements | 5 | 100% | 5 |
| Design mockups | 10 | 100% | 10 |
| Content writing | 8 | 50% | 4 |
| Development | 15 | 20% | 3 |
| Testing | 5 | 0% | 0 |
| Launch | 2 | 0% | 0 |
| Total | 45 | 22 |
Weighted progress: 22 / 45 = 48.9%. A simple average would say (100 + 100 + 50 + 20 + 0 + 0) / 6 = 45%. The difference grows as task lengths become more uneven.
Step 3: The timeline
For a quick visual, add week-start dates across row 4 of a Timeline tab and use conditional formatting with this rule on the grid:
=AND(H$4<=$E5,H$4+6>=$D5)
Each cell lights up when its week overlaps the task’s start and end dates. A second rule can color the bar red when the task is overdue. For a full walkthrough, including a stacked-bar chart version and milestones, see the guide to building a Gantt chart in Excel.
Step 4: Budget vs actual
On the Budget tab, list cost categories (staff time, contractors, software, hardware, travel, contingency) with three columns: planned, actual to date, and remaining forecast. Variance is =Planned-(Actual+Forecast); a negative value means you expect to overspend.
To compare cost with progress, use two earned-value numbers:
- Earned value (EV) = total budget × weighted % complete.
- Cost performance index (CPI) = EV / actual cost. Below 1.0 means you are getting less work done per dollar than planned.
In the website example, with a total budget of $18,000 and $9,400 spent: EV = 18,000 × 22/45 = $8,800; CPI = 8,800 / 9,400 = 0.94. The project is about 49% done but has used 52% of its budget. That is not a crisis, but it is an early signal worth discussing before development, the biggest task, gets further along.
If team members log hours, a timesheet template feeds the staff-time actuals directly.
Step 5: Risks and issues log
| Field | Example |
|---|---|
| Type | Risk (might happen) or Issue (already happening) |
| Description | “Content not approved before development starts” |
| Likelihood (1–5) | 3 |
| Impact (1–5) | 4 |
| Score | =Likelihood*Impact → 12 |
| Owner and action | “Marketing lead: approval meeting by Friday” |
| Status | Open / Mitigated / Closed |
Sort by score and review the top five in every status meeting. Record decisions from those meetings in a meeting minutes template so actions do not get lost.
Step 6: The status dashboard
One small block at the top of the Summary tab answers “how is the project doing?”:
| Metric | Formula |
|---|---|
| Progress (weighted) | From Step 2 |
| Planned progress today | =MIN(1,MAX(0,NETWORKDAYS(Start,TODAY(),Holidays)/NETWORKDAYS(Start,End,Holidays))) |
| Overdue tasks | =COUNTIF(Tasks[Overdue?],"Overdue") |
| Blocked tasks | =COUNTIF(Tasks[Status],"Blocked") |
| CPI | From Step 4 |
| Open risks with score ≥ 12 | =COUNTIFS(Risks[Status],"Open",Risks[Score],">=12") |
Then derive one overall status with clear rules, for example:
=IF(OR(OverdueTasks>=3,CPI<0.9,Progress<PlannedProgress-0.15),"RED",IF(OR(OverdueTasks>=1,CPI<0.97,Progress<PlannedProgress-0.05),"AMBER","GREEN"))
Agree on the thresholds at kickoff. A status rule written down in advance prevents the classic “everything is green until the week before launch”.
Common mistakes
- Tasks too big: anything longer than two weeks hides problems. Split it.
- Shared ownership: “Design team” owns nothing. Put one name per task.
- No baseline: copy the original end dates into a hidden “Baseline end” column at kickoff, so you can see how far dates have slipped.
- Updating only before steering meetings: a weekly 10-minute update keeps the sheet trustworthy.
- Several copies by email: keep one file in shared storage (OneDrive, SharePoint, Google Drive).
FAQ
Is Excel good enough for project management?
For projects with up to roughly 50–100 tasks and a small team, yes. Once you need automatic rescheduling of dependent tasks, resource leveling or many people updating at once, a dedicated tool becomes easier.
Should I use Google Sheets instead?
Google Sheets is a good choice when several people update tasks at the same time. NETWORKDAYS, SUMPRODUCT, XLOOKUP and conditional formatting all work there.
How often should the template be updated?
Task status weekly, budget actuals monthly, risks at every status meeting. The dashboard updates itself from those inputs.
More guides: Project Management.