Excel Skills for Project Managers

Excel for project managers comes down to a handful of skills: working-day date maths, status counts with COUNTIFS, overdue flags, conditional formatting that makes late tasks stand out, and filters that show one person's work. With those, a plain task list becomes a tracker you can run meetings and status reports from. This guide is for project coordinators, project managers and anyone interviewing for a role that involves planning work in Excel.

How project managers use Excel#

Project managers use Excel to plan, track and report work, especially when a team doesn't have dedicated project software or needs to analyze exported data. Typical files include:

• Task trackers: each task with an owner, dates and a status.
• Status reports: counts of open, done and late tasks by owner or workstream.
• Timelines: a simple Gantt view of when each task runs.
• Budgets: planned vs actual spend per workstream.
• RAID logs: risks, actions, issues and decisions with owners and due dates.

All of these are tables with dates, names and statuses, so the same formulas keep coming up.

Build a simple task tracker#

Start with one row per task and one column per piece of information. Keep each column to one kind of value, so formulas and filters work.

Put the report date in its own cell, for example H1, so every "is it late?" formula points at one place. In a live tracker that cell can hold =TODAY(); in a report or test, type a fixed date so the results don't change.

Use a drop-down list for Status (Done, Open, Blocked) so everyone types the same words. Our guide to creating a drop-down list in Excel shows how.

Working-day dates with WORKDAY and NETWORKDAYS#

Project dates usually skip weekends, so use the working-day functions instead of adding days.

• WORKDAY(start, days, [holidays]) returns the date a number of working days after the start.
• NETWORKDAYS(start, end, [holidays]) counts the working days between two dates, counting both ends.

The "Draft brief" task starts on Monday 3 March 2025 and takes 10 working days:

=WORKDAY(C2,D2-1)

The result is Friday 14 March 2025. The -1 is there because the start day counts as day 1; =WORKDAY(C2,D2) would give the 11th working day. To check it the other way round:

=NETWORKDAYS(C2,E2)

This returns 10. Add a range of public holidays as the third argument, such as $J$2:$J$10, and both functions skip those days too. The guide to NETWORKDAYS and WORKDAY covers custom weekends and other edge cases.

Count tasks by owner and status with COUNTIFS#

COUNTIFS counts the rows that meet every condition, which answers most status-report questions. With owners in B2:B50 and statuses in F2:F50:

=COUNTIFS(B2:B50,"Sam",F2:F50,"Open")

This counts Sam's open tasks. Put the owner and status in cells and point to them, and you can fill one formula across a whole summary grid. A few more useful counts:

The "<"&$H$1 part joins the less-than sign to the date in H1, which is how COUNTIFS compares dates. The COUNTIFS function guide has more examples.

Flag overdue tasks#

A task is overdue when its end date is before the report date and it isn't done. Write that as IF with AND:

=IF(AND(E2<$H$1,F2<>"Done"),"Overdue","")

With 20 March 2025 in H1, "Draft brief" shows nothing (it's done), and "Book venue", which ends on 14 March and is still open, shows Overdue. The $ signs keep H1 fixed when you fill the formula down.

Make late tasks stand out with conditional formatting#

Conditional formatting colours cells automatically, so a late task turns red without anyone touching it. To colour the whole row:

1. Select the data, for example A2:F50.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =AND($E2<$H$1,$F2<>"Done"), choose a fill colour and click OK.

The $ before E and F keeps every cell in the row looking at the same date and status columns. The same idea, with dates across the top of a grid, gives you a simple Gantt chart; see how to make a simple project timeline in Excel.

Filter the tracker for meetings#

In a status meeting, you often want to show only one owner's tasks or only the open ones. Click inside the tracker and press Ctrl+Shift+L (Mac: ⌘⇧F) to add filter buttons, then pick values from a column's drop-down.

Filters hide rows without deleting them, so the data stays safe. To total or count only the visible rows, use SUBTOTAL, for example =SUBTOTAL(3,A2:A50) to count visible tasks. Our guide on how to filter data in Excel covers number and date filters.

Track budget vs actual#

Many project managers also own a budget. Keep planned and actual spend in columns next to each workstream, then add:

• Variance: =D2-C2, actual minus planned. A positive number means overspend.
• Variance %: =(D2-C2)/C2, formatted as a percentage.
• Spend by workstream: =SUMIFS(D2:D50,A2:A50,"Design") from a transaction list.

These skills overlap with neighbouring roles. If you coordinate suppliers or stock, see the Excel skills for supply chain roles; for campaign-heavy projects, the Excel skills for marketing jobs; and for office coordination roles, the Excel skills for admin jobs.

Practise it#

Practise the three skills a tracker depends on: count working days and find deadlines in Workdays: NETWORKDAYS and WORKDAY, colour whole rows with a formula rule in Conditional formatting, and show one owner or status at a time in Filter a table. The exercise on this page is the COUNTIFS behind a status report.