Excel for Project Managers: Trackers, Timelines and Budgets
Excel for project managers: task trackers, working-day timelines, a simple Gantt chart with conditional formatting, budget vs actual and RAG flags.
By Ayoub Farid · · 1 min read
Project tools come and go, but a well-built Excel tracker is still the fastest way to plan, share and report on a project. Here's what to build and the formulas behind it.
Task tracker
Columns: Task, Owner, Start, Duration (days), End, Status, % Done.
- End date (working days):
=WORKDAY(C2,D2-1,Holidays). See NETWORKDAYS. - Days left:
=E2-TODAY() - Late flag:
=IF(AND(E2<TODAY(),F2<>"Done"),"Late","") - Count by status:
=COUNTIF(F2:F100,"In progress"). See COUNTIF.
Simple Gantt chart
Put dates across the top (H1 onwards), then select the grid and add a conditional formatting rule with this formula:
=AND(H$1>=$C2,H$1<=$E2)Mixed references make it work for every cell: the date row is locked, the task columns are locked. See mixed references.
Budget vs actual
| Line | Budget | Actual | Variance | % used |
|---|---|---|---|---|
| Design | 12,000 | 9,400 | =B2-C2 | =C2/B2 |
Actuals from a cost log: =SUMIF(Log!$B:$B,A2,Log!$D:$D).
RAG status
=IFS(G2>=1.1,"Red",G2>=0.95,"Amber",TRUE,"Green")where G2 is % of budget used (or schedule slip). Colour with conditional formatting.
Progress
- Weighted completion:
=SUMPRODUCT(D2:D50,G2:G50)/SUM(D2:D50)(duration × % done). See weighted average.
Frequently asked questions
Can you manage a project in Excel?
Yes, for small and medium projects: task trackers, timelines, budgets and status reports work well.
How do I make a Gantt chart in Excel?
Use conditional formatting with =AND(date>=start,date<=end) across a date grid, or a stacked bar chart.
How do I calculate project end dates excluding weekends?
=WORKDAY(start,duration-1,holidays).
What Excel skills do project managers need?
Date functions, COUNTIF/SUMIF, conditional formatting, budget tracking and clear reporting.