Dedicated project management software is powerful, but for many teams it is also expensive, slow to roll out, and one more login to manage. Excel, on the other hand, is already on almost every office laptop, and most people know their way around it. With the right structure and a handful of formulas, a plain spreadsheet can become a working project plan, with dates that update themselves, a visual Gantt chart, and a status summary your manager can read in ten seconds.
This guide builds that plan step by step. Every formula is ready to copy, and each step explains why it matters, not just which button to press.
Quick answer: To create a project plan in Excel, (1) define the goal and scope, (2) list tasks with a work breakdown structure, (3) add columns for owner, start date, duration, end date, predecessor, and status, (4) calculate end dates with WORKDAY, (5) build a Gantt chart using conditional formatting, and (6) track progress with COUNTIF and a simple summary dashboard.
Why Use Excel for Project Planning?
Excel is not the right tool for every project, but it is a strong choice in many situations.
Where Excel Works Well
- No extra cost or setup. Most organisations already have Microsoft 365 or an Office licence.
- Complete flexibility. You decide the columns, formulas, and layout instead of adapting to someone else's workflow.
- Easy to share. Clients, vendors and senior managers can open the file without training or a new account.
- Works with your data. Budgets, resource lists and reports often live in Excel already, so linking them is simple.
When to Move Beyond Excel
Excel struggles when many people edit the same plan at once, when a project has hundreds of interdependent tasks, or when you need automatic resource levelling. At that point, tools such as Microsoft Project, Smartsheet, or Jira are worth the investment. For small and mid-sized projects with up to roughly 100 tasks and a single plan owner, Excel is usually enough.
Step 1: Define the Project Goal and Scope
A plan is only as good as the clarity behind it. Before opening a spreadsheet, write down what the project must deliver and what it will not.
Write SMART Objectives
A SMART objective is Specific, Measurable, Achievable, Relevant, and Time-bound. "Improve the website" is vague. "Launch the redesigned website with 25 pages and a working enquiry form by 15 January" gives everyone the same target.
Create a Project Summary Sheet
Add a first sheet named Summary containing the project name, sponsor, project manager, start date, target end date, budget and a three-line scope statement. Anyone who opens the file sees the big picture first.
Step 2: Break the Work into Tasks and Milestones
Most schedules go wrong here, because tasks are too big to estimate or track.
Build a Work Breakdown Structure (WBS)
Split the project into phases, then split each phase into tasks small enough to finish in one to ten working days. Number them hierarchically, for example 1.0 Planning, 1.1 Kick-off meeting, 1.2 Requirements sign-off. The numbering keeps related tasks together and makes dependencies easier to reference.
Mark Your Milestones
Milestones are checkpoints with zero duration, such as "Design approved" or "Go-live." Enter them as tasks with a duration of 0 and a phase label of "Milestone." They give stakeholders clear moments to review progress.
Start from a Built-In Template (Optional)
If you prefer a head start, open Excel and go to File > New, then search for "Gantt" or "project plan." Microsoft provides several free templates, including a simple Gantt chart project planner. Templates save time, but building your own, as shown below, means you understand every formula and can adapt it later.
Step 3: Set Up the Plan Columns
Create a sheet named Plan. Starting in row 4 (leaving space for a title), add these headers:
ColumnHeaderWhat it holdsAIDWBS number, e.g. 2.3BTaskShort action-based name, e.g. "Draft homepage copy"CPhasePlanning, Design, Build, Test, Launch or MilestoneDOwnerOne person accountableEStartStart dateFDurationWorking daysGEndCalculated end dateHPredecessorID of the task that must finish firstIStatusNot Started, In Progress, Completed or On HoldJ% Complete0% to 100%
Convert the Range to an Excel Table
Select the headers and data, then press Ctrl + T. An Excel Table expands automatically when you add rows, copies formulas down, and makes filtering by owner or status a one-click job.
Add Drop-Down Lists for Consistency
Select the Status column, go to Data > Data Validation, choose List, and enter Not Started,In Progress,Completed,On Hold. Do the same for Owner and Phase. Consistent values are essential because the formulas in later steps count exact text.
Step 4: Build an Automatic Timeline
A good plan recalculates itself. When one task slips, every dependent date should move with it.
Create a Holiday List
On a sheet named Settings, list your company's holidays in one column, including national holidays and regional festivals your team observes. Select the list and name it Holidays using the Name Box (left of the formula bar).
Calculate End Dates with WORKDAY
In cell G5, enter:
=WORKDAY(E5, F5-1, Holidays)
This skips weekends and your listed holidays. The -1 counts the start day as the first working day, so a one-day task starts and ends on the same date. If your team works Saturdays, use WORKDAY.INTL with weekend code 11 (Sunday only) instead.
Link Tasks with Dependencies
Type start dates manually for tasks that have no predecessor. For a task that can only begin after another finishes, calculate its start date from the predecessor's end date. For example, in E6:
=WORKDAY(XLOOKUP(H6, $A$5:$A$100, $G$5:$G$100), 1, Holidays)
This finds the predecessor's end date and sets the start to the next working day. XLOOKUP Requires Excel 2021 or Microsoft 365. In older versions, use INDEX with MATCH: =WORKDAY(INDEX($G$5:$G$100, MATCH(H6,$A$5:$A$100,0)),1,Holidays).
Step 5: Create a Gantt Chart with Conditional Formatting
A Gantt chart turns a list of dates into a picture of the project. Conditional formatting is the cleanest way to build one in Excel, because the bars update automatically when dates change.
Add a Date Header Row
In L4, enter the project start date. In M4, enter =L4+1 and drag right for as many days as the project needs. Format the row as dd-mmm and narrow the columns to about 3 characters wide. For long projects, use =L4+7 to show weeks instead of days.
Apply the Bar Rule
Select the grid from L5 across and down to the last task. Go to Home > Conditional Formatting > New Rule > Use a formula and enter:
=AND(L$4>=$E5, L$4<=$G5)
Choose a fill colour. Each task now shows a bar from its start date to its end date. The dollar signs matter: they lock the date row and the start and end columns so the rule works across the whole grid.
Add Visual Signals
Highlight Today
Add a second rule, =L$4=TODAY(), with a red border. It draws a "today" line through the chart.
Flag Overdue Tasks
Select the task rows and add the rule =AND($G5<TODAY(), $I5<>"Completed") with a light red fill. Late work stands out immediately.
Colour Completed Work
Add =AND(L$4>=$E5, L$4<=$G5, $I5="Completed") in green, placed above the bar rule in the rule list so it takes priority.
Step 6: Assign Owners and Balance Workload
Every task needs one accountable owner, even if several people help. Shared ownership usually means nobody follows up.
Check Workload with COUNTIFS
On the Summary sheet, list team members and count their active tasks:
=COUNTIFS(Plan!$D$5:$D$100, A10, Plan!$I$5:$I$100, "In Progress")
If one person has eight tasks in progress and another has one, rebalance before the deadline does it for you.
Step 7: Track Progress and Build a Status Dashboard
Senior stakeholders rarely read a full task list. Give them a small summary at the top of the Summary sheet.
Key Formulas for Tracking
- Tasks completed:
=COUNTIF(Plan!I5:I100, "Completed") - Total tasks:
=COUNTA(Plan!B5:B100) - Overdue tasks:
=COUNTIFS(Plan!G5:G100, "<"&TODAY(), Plan!I5:I100, "<>Completed") - Weighted % complete:
=SUMPRODUCT(Plan!F5:F100, Plan!J5:J100) / SUM(Plan!F5:F100)
The weighted figure is more honest than a simple task count, because finishing a 10-day task matters more than ticking off a 1-day one.
Set a Weekly Review Rhythm
Update the plan at a fixed time each week, for example, Friday afternoon. Ask each owner three questions: what was finished, what is blocked, and has any end date changed? Then save a dated copy or note major changes in a Change Log sheet so you can explain slippage later.
Case Study: Planning a Website Launch in Excel
This is a composite example based on a typical small-agency project. Figures are illustrative.
A five-person digital agency in Noida needed to deliver a 25-page corporate website in eight weeks. The team had no budget for project software, and the client wanted weekly visibility.
The project manager built the plan exactly as described above: 42 tasks across five phases, four milestones, and a holiday list that included Diwali week. Dependencies were linked with WORKDAY and XLOOKUP. In week three, the client delayed content approval by four working days. Because downstream dates were formula-driven, the team updated one cell and immediately saw that the go-live date had moved, and that developer tasks now overlapped. They shifted two front-end tasks to a second developer, recovered three of the four days, and shared the updated Gantt as a PDF with the client the same afternoon.
The site launched one day after the original target. The client later asked the agency to use the same Excel format for the next project.
Common Mistakes to Avoid
- Typing end dates by hand. Manual dates do not move when plans change. Use formulas.
- Tasks that are too large. "Build website" cannot be tracked. "Build contact form" can.
- Ignoring holidays and leave. A plan that assumes everyone works through festivals will slip.
- Inconsistent status text. "Done," "done," and "Completed" break your formulas. Use drop-down lists.
- Updating only when someone asks. A plan that is two weeks stale is worse than none, because people trust it.
Take Your Planning Skills Further
Excel teaches the mechanics of scheduling. Formal project management training teaches the thinking behind it, such as critical path analysis, risk planning and stakeholder management. To formalise these skills, explore our PMP certification training or the entry-level CAPM course. For the Project Management Institute's own guidance, see the PMBOK Guide. Microsoft's documentation explains each function used here, including WORKDAY.
Frequently Asked Questions
Can Excel create a Gantt chart?
Yes. Excel has no dedicated Gantt chart type, but you can build one with conditional formatting on a date grid, as shown in this guide, or with a stacked bar chart where the first series is hidden. Conditional formatting is easier to maintain because it updates automatically.
Is Excel good for project management in large teams?
Excel works well for small to mid-sized projects with one person maintaining the plan. For large teams with many simultaneous editors, hundreds of tasks or resource levelling, dedicated tools such as Microsoft Project or Smartsheet are a better fit.
Are there free project plan templates in Excel?
Yes. In Excel, go to File > New and search for "Gantt" or "project plan" to find Microsoft's free templates. They are a quick start, but a plan you build yourself is easier to customise and troubleshoot.
How do I calculate working days between two dates in Excel?
Use =NETWORKDAYS(start_date, end_date, Holidays). It counts weekdays between the two dates, including both ends, and excludes any dates in your holiday list.
How often should a project plan be updated?
At least once a week, at a fixed time, and immediately after any major change in scope, resources or deadlines.
Final Thoughts
A project plan in Excel does not need to be complicated to be useful. Clear tasks, one owner per task, formula-driven dates and a visual timeline cover most of what small and mid-sized projects need. Build it once, keep it updated every week, and it will tell you about problems while there is still time to fix them.