Design an Excel project-management workbook for this project. Project objective and deliverables: [PROJECT SCOPE] Project start date and target completion date: [DATES] Workstreams, tasks, and owners known so far: [WORK BREAKDOWN] Milestones and dependencies: [MILESTONES AND DEPENDENCIES] Reporting audience and update frequency: [REPORTING NEEDS] Excel version or platform: [EXCEL VERSION]
Create a workbook specification that a project lead can build and maintain. Do not invent task durations, owners, dates, budget figures, or completion status. Mark missing values as “TBD.” Produce: 1. Sheet-by-sheet design for a Task Register, Milestones, Risks and Decisions, and Status Dashboard. State each column name, purpose, allowed values, and who updates it. 2. A Task Register table with the exact columns needed for task ID, workstream, task, owner, start date, due date, dependency ID, status, percent complete, priority, RAG health, next action, and last updated. 3. Excel formulas using structured table references where possible for days remaining, overdue flag, RAG health, milestone completion, and dashboard counts. Explain each formula in plain English and note blanks or error handling. 4. Data-validation lists for status, priority, and RAG health, plus conditional-formatting rules for overdue and blocked work. 5. A weekly operating routine: who updates what, when blockers are escalated, and what the project lead checks before sending status. 6. A sample of five rows using clearly labeled fictional values only. Before answering, check that each dashboard metric can be traced to a source column and that every dependency has a named predecessor or is marked TBD. Ask up to 3 clarifying questions only if a required input is missing.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [PROJECT SCOPE] | State the project goal, final deliverables, and what is out of scope. | Launch a new employee onboarding program with a manager guide, HRIS workflow, training session, and first-30-day survey; payroll redesign is out of scope. |
| [DATES] | Enter the planned start date and target completion date. | Start October 6; target completion December 19. |
| [WORK BREAKDOWN] | List known workstreams, tasks, and assigned owners or state which are unassigned. | HR: draft guide—Maya; IT: configure HRIS workflow—Daniel; L&D: build training—Priya; Communications: launch announcement—unassigned. |
| [MILESTONES AND DEPENDENCIES] | List important milestone dates and tasks that cannot begin until other work finishes. | HRIS workflow approved by November 7; training cannot be finalized until the workflow is approved; pilot begins December 1. |
| [REPORTING NEEDS] | Describe who receives updates, what they need to see, and how often. | HR director receives a one-page weekly update every Friday; workstream owners need a daily task view. |
| [EXCEL VERSION] | State your Excel edition or platform, such as Microsoft 365 desktop or Excel for web. | Microsoft 365 desktop for Windows |
How to use
- List real deliverables and dependencies before asking for formulas; a tracker cannot repair an undefined scope.
- Build the Task Register as an Excel Table first, then paste in formulas and validations.
- Test overdue and RAG rules with a few fictional dates before sharing the workbook.
- Follow up with: “Give me the exact Excel steps to create the dashboard using this column structure.”
Variations
Executive dashboard
Use when leaders need a concise project view rather than task-level detail.
Design an Excel executive dashboard for [PROJECT] using these source fields: [AVAILABLE COLUMNS]. The audience is [AUDIENCE], and reporting is [FREQUENCY]. Specify no more than six metrics, including milestone health, overdue work, blockers, decisions needed, and forecast date where source data supports it. Give exact formulas or PivotTable steps compatible with [EXCEL VERSION], plus a one-page layout. Do not calculate a forecast without task dates and dependencies. Flag metrics that cannot be supported by the source data.
Risk register
Use when the project needs disciplined risk and issue tracking.
Create an Excel risk-and-issue register for [PROJECT] based on [KNOWN RISKS] and [PROJECT CONTEXT]. Produce columns, allowed values, formulas for risk score and escalation flag, conditional formatting, owner responsibilities, and a weekly review process. Distinguish risks from active issues, assumptions, and decisions. Include a five-row fictional example clearly marked as illustrative. Do not assign likelihood or impact values where evidence is missing; mark them for owner assessment. Ask up to 3 questions only if needed.
Recovery plan
Use when a project is behind schedule and needs a credible reset.
Create an Excel recovery-plan tracker for [PROJECT] using [CURRENT STATUS], [MISSED MILESTONES], [REMAINING WORK], and [CONSTRAINTS]. Produce a revised task table, dependency map, decision log, owner actions, and weekly checkpoints. Identify tasks that can run in parallel, tasks requiring scope reduction, and deadlines that cannot be changed. Do not promise recovery without showing the dependency and capacity assumptions. Flag any proposed date that lacks a responsible owner or predecessor.
Tips
- Make task IDs permanent; use them for dependencies instead of relying on row numbers that change when someone sorts the table.
- Use one status vocabulary, such as Not Started, In Progress, Blocked, Complete, and Cancelled, so counts remain meaningful.
- Do not use percent complete as a health indicator by itself; a task can be 90% complete and still blocked past its deadline.
- Put the next action and its owner in separate fields so the weekly review produces a real commitment, not a vague note.
FAQ
Can Excel replace project-management software?
It works well for a bounded project with a small team and clear update discipline. Complex resource planning, many cross-project dependencies, or live collaboration may warrant a dedicated tool.
What is the most important column in a task tracker?
The next action with an owner and due date. It turns a status report into work that someone can complete.
Should every task have a dependency?
No. Add dependencies only when one task truly cannot proceed without another; excessive links make the schedule harder to maintain.