From project timelines to budget tracking — the Excel techniques project managers use to keep projects on track.
A simple Gantt chart uses conditional formatting to shade cells between a start and end date.
-- In each cell of the timeline grid, use:
=AND(column_date >= task_start_date, column_date <= task_end_date)
-- If TRUE, conditional formatting shades the cell blueSet up columns as dates across the top (one column per day or week), tasks down the side, and apply conditional formatting with this formula to each row.
-- Days remaining until deadline
=NETWORKDAYS(TODAY(), deadline_date)
-- Deadline N working days from start
=WORKDAY(start_date, working_days)
-- Is a task overdue?
=IF(AND(status<>"Complete", end_date-- Variance
=actual - budget
-- Variance %
=(actual - budget) / budget * 100
-- % of budget spent
=actual / budget * 100
-- Budget remaining
=budget - actual-- Hours allocated vs available
=SUMIFS(hours_col, person_col, "Sarah", month_col, "October")
-- Utilisation rate
=allocated_hours / available_hours * 100
-- Flag over-allocated resources
=IF(utilisation > 100%, "OVER", "OK")-- Count tasks by status
=COUNTIF(status_col, "Complete")
=COUNTIF(status_col, "In Progress")
=COUNTIF(status_col, "Not Started")
=COUNTIF(status_col, "Overdue")
-- % complete
=COUNTIF(status_col,"Complete") / COUNTA(status_col) * 100Apply three conditional formatting rules to a status column: red for "Overdue", amber for "At Risk", green for "On Track". Your project dashboard becomes a visual RAG report automatically.
Practice these formulas in the Sales & Operations track — 100 exercises covering targets, pipelines, and reporting. Free to start.
Start the Sales & Operations track free →