Excel for project managers

Q: Can Excel be used for project management?

Yes, for small and medium projects it works well: a task list with owners, dates and % complete, formulas for status and overdue counts, and a status report built from them. Dedicated tools are better for dependencies, many people editing at once, and resource leveling across projects.

Q: How do I calculate % complete for a whole project in Excel?

Weight each task by its effort: =SUMPRODUCT(hours,pct_complete)/SUM(hours). A plain AVERAGE of the % complete column counts a 2-hour task the same as an 80-hour one.

Q: How do I calculate working days between two dates in Excel?

Use =NETWORKDAYS(start,end,holidays), where holidays is an optional range of dates to skip. It counts both the start and end dates. For weekends other than Saturday and Sunday, use NETWORKDAYS.INTL.

Q: How do I make a RAG status in Excel?

Use IFS with the Red test first, then Amber, then TRUE for Green, for example =IFS(overdue>2,"Red",overdue>0,"Amber",TRUE,"Green"). Then add conditional formatting (Format only cells that contain: Specific text) to color each word.

For project managers who run real projects in a spreadsheet, or export from a PM tool every Friday to build the status report. Excel answers the questions sponsors actually ask: what is late, how done are we really, who is overloaded, how many working days are left, and whether the budget will last. Every formula below runs on a website relaunch tracker, reviewed on Monday November 2.

Project and program managers
Team leads running their own projects
PMO analysts building status reports
Agency producers and account managers

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	Task	Owner	Start	Due	Est. hours	% complete	Actual hours	Status date
2	Kickoff and requirements	Maya	Oct 5, 2026	Oct 9, 2026	16	1	18	Nov 2, 2026
3	Content audit and sitemap	Jon	Oct 7, 2026	Oct 16, 2026	24	0.9	22	Hourly rate
4	Wireframes	Priya	Oct 12, 2026	Oct 23, 2026	40	1	46	95
5	Copywriting	Jon	Oct 19, 2026	Oct 30, 2026	36	0.75	30	Holiday
6	Visual design	Priya	Oct 26, 2026	Nov 6, 2026	60	0.6	41	Nov 26, 2026
7	CMS setup	Luis	Oct 26, 2026	Nov 4, 2026	30	0.5	18	Focus hours per day
8	Redirect map for SEO	Jon	Nov 2, 2026	Nov 6, 2026	12	0.25	4	6
9	Page build	Luis	Nov 9, 2026	Nov 27, 2026	80	0	0
10	Stakeholder review	Maya	Nov 30, 2026	Dec 2, 2026	6	0	0
11	QA and accessibility testing	Maya	Nov 30, 2026	Dec 4, 2026	24	0	0
12	Launch	Luis	Dec 7, 2026	Dec 8, 2026	8	0	0

What Excel does for project management

Give every task a status from its due date

A status column people trust is calculated, not typed. IFS checks conditions in order and returns the first one that is true: done first, then overdue, then due within a week, and TRUE at the end catches everything else.

The status date in H2 matters. Using a fixed review date instead of TODAY() means the report you send on Monday still says the same thing when someone opens it on Wednesday. Lock it as $H$2 before you copy the formula down.

=IFS(F5=1,"Done",D5<H2,"Overdue",D5-H2<=7,"Due this week",TRUE,"On track")

Result: Overdue

Overdue: copywriting was due on October 30 and is 75% done. Visual design, due Friday at 60%, would show "Due this week".

Count what is overdue

Overdue means two things at once: the due date has passed and the task is not finished. COUNTIFS takes both conditions. Joining the comparison to the date cell with & ("<"&H2) is how you compare against a cell instead of a typed date.

Without the % complete condition, every finished task from last month would count as overdue too.

=COUNTIFS(D2:D12,"<"&H2,F2:F12,"<1")

Result: 2

2 overdue tasks, both Jon's: the content audit (90%) and copywriting (75%). Those two are where the next conversation should start.

Measure % complete by effort, not by task count

A plain AVERAGE of the % complete column treats a 6-hour review the same as an 80-hour build. Weight each task by its estimated hours instead: SUMPRODUCT multiplies hours by % complete and adds the results, which gives the hours of work done, and dividing by total hours turns that into a percentage.

The two are close here (47% against 45%), but on a project where the small tasks are finished and the big ones have not started, the simple average can look far better than reality.

=SUMPRODUCT(E2:E12,F2:F12)/SUM(E2:E12)

Result: 0.47202380952 shows as 47% with Percent format

47% complete: 158.6 of the 336 estimated hours are done.

See remaining work per person

Remaining hours for a task are its estimate times the part not yet done, (1 - % complete). SUMPRODUCT with an owner test adds that up for one person. Compare it with what they can actually do before the deadline.

Capacity is working days times focus hours per day (6 in H8, because nobody spends 8 hours a day on one project). Luis has 26 working days to launch, about 156 focus hours, so his load fits, but almost all of it is the page build in November.

=SUMPRODUCT((B2:B12="Luis")*E2:E12*(1-F2:F12))

Result: 103

103 hours left for Luis: half of CMS setup (15), the page build (80) and launch (8). Maya has 30, Priya 24 and Jon 20.4.

Count the working days left (and remember holidays)

NETWORKDAYS counts Monday-to-Friday days between two dates, including both ends. Its third argument is a list of holidays to skip. Here H6 holds Thanksgiving, which falls inside the build phase; leave it out and the plan quietly assumes a day nobody will work.

To go the other way, from a start date and a number of working days to a finish date, Excel has WORKDAY(start, days, holidays). The practice sandbox on this page does not run WORKDAY, so the examples here use NETWORKDAYS. If your team does not work Monday to Friday, NETWORKDAYS.INTL and WORKDAY.INTL let you choose the weekend days.

=NETWORKDAYS(H2,D12,H6)

Result: 26

26 working days from Monday November 2 to the December 8 launch, counting both days and skipping Thanksgiving. Without the holiday it would say 27.

Check budget burn with earned value

"53% of the budget is spent" means nothing on its own. The useful question is what you got for it. Earned value is the work done (hours weighted by % complete) and actual cost is the hours logged. Their ratio is the cost performance index (CPI): above 1 is under budget for the work done, below 1 is over.

Dividing the budget by CPI gives a simple forecast of the final cost if the project keeps performing the same way. With hours at one rate, the rate cancels out of CPI, so you can work in hours.

=SUMPRODUCT(E2:E12,F2:F12)/SUM(G2:G12)

Result: 0.88603351955 shows as 0.89 with Number format

0.89: every 100 hours logged has delivered 89 hours of planned work. At that rate the $31,920 budget becomes about $36,026 (the last practice exercise builds that forecast). Wireframes (46 hours against 40) and the kickoff ran over.

Set a RAG status with IFS

Red, Amber, Green is only useful if everyone knows the rules. Write them into the formula: check Red conditions first, then Amber, and let TRUE catch Green. Here Red is more than 2 overdue tasks or a CPI under 0.9; Amber is any overdue task or a CPI under 0.95. Agree the thresholds with your sponsor and write them in the report.

Because the formula reuses the overdue count and the CPI, the status can never disagree with the numbers next to it.

=IFS(OR(COUNTIFS(D2:D12,"<"&H2,F2:F12,"<1")>2,SUMPRODUCT(E2:E12,F2:F12)/SUM(G2:G12)<0.9),"Red",OR(COUNTIFS(D2:D12,"<"&H2,F2:F12,"<1")>0,SUMPRODUCT(E2:E12,F2:F12)/SUM(G2:G12)<0.95),"Amber",TRUE,"Green")

Result: Red

Red, because CPI is 0.89. Only 2 tasks are overdue, which on its own would be Amber; cost is what tips it over.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1What is the total estimated effort for the project, in hours?

Your formula for exercise 1

Show answer
=SUM(E2:E12) = 336. All eleven tasks add up to 336 hours.
2How many tasks are finished?

Your formula for exercise 2

Show answer
=COUNTIF(F2:F12,1) = 2. Only the kickoff and the wireframes are at 100%.
3How many of Jon's tasks are still open?

Your formula for exercise 3

Show answer
=COUNTIFS(B2:B12,"Jon",F2:F12,"<1") = 3. All three of Jon's tasks are under 100%, and two of them are overdue.
4How much has the project cost so far, at the hourly rate in H4?

Your formula for exercise 4

Show answer
=SUM(G2:G12)*H4 = 17005. 179 hours x $95 = $17,005, about 53% of the $31,920 budget.
5How many working days does the page build (row 9) get, skipping the holiday in H6?

Your formula for exercise 5

Show answer
=NETWORKDAYS(C9,D9,H6) = 14. November 9 to 27 is 15 weekdays; Thanksgiving takes it to 14.
6How many hours of work does Maya still have?

Your formula for exercise 6

Show answer
=SUMPRODUCT((B2:B12="Maya")*E2:E12*(1-F2:F12)) = 30. Her kickoff is done, so only the review (6) and QA (24) remain: 30 hours.
7How many unfinished tasks are due within 7 days of the status date in H2 (on or after H2)?

Your formula for exercise 7

Show answer
=COUNTIFS(D2:D12,">="&H2,D2:D12,"<="&H2+7,F2:F12,"<1") = 3. CMS setup, visual design and the redirect map all land this week.
8Forecast the cost at completion: the budget (hours times rate) divided by CPI (earned hours / actual hours), rounded to whole dollars.

Your formula for exercise 8

Show answer
=ROUND(SUM(E2:E12)*H4/(SUMPRODUCT(E2:E12,F2:F12)/SUM(G2:G12)),0) = 36026. A CPI of 0.886 turns a $31,920 budget into a forecast of $36,026 if nothing changes.

Free templates for project management

Guided project: Team capacity check

Work out each person's capacity for the next two weeks from working days and time off, and find who is overloaded even when the team total looks fine.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

Can Excel be used for project management?

Yes, for small and medium projects it works well: a task list with owners, dates and % complete, formulas for status and overdue counts, and a status report built from them. Dedicated tools are better for dependencies, many people editing at once, and resource leveling across projects.

How do I calculate % complete for a whole project in Excel?

Weight each task by its effort: =SUMPRODUCT(hours,pct_complete)/SUM(hours). A plain AVERAGE of the % complete column counts a 2-hour task the same as an 80-hour one.

How do I calculate working days between two dates in Excel?

Use =NETWORKDAYS(start,end,holidays), where holidays is an optional range of dates to skip. It counts both the start and end dates. For weekends other than Saturday and Sunday, use NETWORKDAYS.INTL.

How do I make a RAG status in Excel?

Use IFS with the Red test first, then Amber, then TRUE for Green, for example =IFS(overdue>2,"Red",overdue>0,"Amber",TRUE,"Green"). Then add conditional formatting (Format only cells that contain: Specific text) to color each word.

Excel for other fields

Updated November 2, 2026