On-Time Project Completion %: How to Track and Analyze Project Delays in Excel

How to Build On-Time Project Completion % Heatmaps in Excel

1. Set up the source data

Start with a data table that includes the key project fields used throughout the analysis:

• Start date
• Planned date
• Actual end date
• System type
• PM
• Delay reason

Then add three calculated columns on the right side of the table:

• Planned duration
• Actual duration
• Delay flag

The video calculates these directly from the date fields, then uses an IF function to flag late jobs with a 1 and on-time jobs with a 0.

2. Create the planned duration, actual duration, and delay flag columns

Build the helper columns needed for the analysis:

• Planned duration compares the planned date to the start date
• Actual duration compares the actual end date to the start date
• Delay flag checks whether the actual duration is greater than the planned duration

If the project ran longer than planned, the delay flag returns 1. Otherwise, it returns 0.

3. Generate a unique list of system types

Use the UNIQUE function to pull a distinct list of system types from the table.

After that:

• Copy the list
• Paste it as values using Ctrl + Shift + V
• Optionally sort the list alphabetically if you want cleaner navigation

This list becomes the row labels for the first heatmap.

4. Generate a unique list of PMs

Create a separate distinct list of PMs using the UNIQUE function.

Then:

• Use the SORT function to arrange the PM names alphabetically
• Transpose the list so the PMs run across the columns instead of down the rows
• Copy and paste as values

This gives you the column headers for the first heatmap.

5. Calculate the percentage of late jobs by PM and system type

Build the first matrix by calculating the share of delayed jobs for each PM and system type combination.

The video uses COUNTIFS to do this in two parts:

• Count the number of delayed jobs where the delay flag equals 1
• Count the total number of jobs for the same PM and system type combination

Then divide those two results to get the percentage of late jobs.

6. Lock the references correctly before copying

Before copying the formula across the matrix, adjust the cell references so the grid fills correctly.

The video explains how to use:

• Relative references where you want values to move
• Absolute references where you want values to stay fixed

This is especially important for the PM headers across the top and the system type labels down the side.

Because of the table structure, the video copies and pastes formulas across the matrix instead of dragging them.

7. Add an overall column for each PM

Next, create an overall late-job percentage for each PM across all system types.

To do that:

• Copy the existing formula
• Remove the system type condition
• Keep only the PM condition and the delay logic

This gives you a quick overall performance view by PM.

8. Add an overall row for each system type

Then create an overall percentage for each system type across all PMs.

To do that:

• Copy the same logic again
• Remove the PM condition
• Keep only the system type condition and the delay logic

This helps identify which job types are most likely to run late overall.

9. Format the matrix as percentages

Once the full matrix is complete:

• Copy and paste formulas where needed
• Format the results as percentages using Ctrl + Shift + 5

Now the table is ready to be read as a performance heatmap.

10. Highlight values above a target threshold

The first heatmap in the video uses target-based highlighting to call out high-delay areas.

To do that:

• Enter a target value such as 35%
• Select the matrix and overall values
• Go to Conditional Formatting
• Use Highlight Cells Rules with a Greater Than rule
• Point the rule to the target cell

This instantly highlights PM and system type combinations that exceed the acceptable late-job threshold.

11. Format the target cell so it stays numeric

The video also shows how to label the target cell without breaking the conditional formatting rule.

To do that:

• Open Format Cells with Ctrl + 1
• Go to Custom formatting
• Keep the percentage stored as a number
• Add the word “Target:” inside the custom format

This lets the cell display as a labeled target while still working inside the rule.

12. Duplicate the layout to analyze delay reasons

After the first heatmap is complete, create a second grid to analyze why jobs are late.

The video copies the structure and then swaps the PM dimension for delay reason.

For this second heatmap:

• Keep system type down the side
• Replace the top headers with a unique list of delay reasons
• Use UNIQUE, SORT, and Transpose again to set up the headers

13. Calculate the percentage of total delays by system type and delay reason

For the second heatmap, calculate what percentage of all delayed jobs comes from each combination of:

• System type
• Delay reason

The video uses COUNTIFS to count delayed jobs for each system type and reason combination, then divides that by the total count of delayed jobs.

This produces a matrix where the values add up to 100%, showing how late jobs are distributed across causes.

14. Add overall percentages to the second heatmap

To make the second matrix easier to read, add an overall column by stripping out one of the criteria and copying the logic across.

The video points out that the totals in this layout sum to 100%, which makes it easier to interpret the biggest drivers behind schedule delays.

15. Apply a color-scale heatmap

For the second matrix, use a different visual style.

Instead of target-based highlighting, the video applies:

• Conditional Formatting
• Color Scales

The chosen scale makes higher percentages appear more severe, so the most common delay causes stand out immediately.

The same color-scale approach is also applied to the first matrix to create a more traditional heatmap view.

16. Use the heatmaps to identify patterns

With both views complete, the worksheet now helps you spot:

• Which PM and system type combinations are running late most often
• Which system types are hardest to complete on time
• Which delay reasons are driving the largest share of late jobs

The video shows how this deeper level of detail helps move from a vague “projects are late” problem to something measurable and actionable.