How to Track Milestone Achievement Rate in Excel
to Catch Project Delays Early

How to Track Milestone Achievement Rate % in Excel

1. Set up the milestone tracking columns

• Start with the existing dataset that includes:
  • project or job
  • milestone name
  • planned completion date
  • actual completion date
• Add the extra columns needed to support the analysis:
  • on-time flag
  • days late
  • milestone type grouping

2. Create the on-time column

• Use the IF function to check whether the actual completion date is less than or equal to the planned completion date.
• Mark milestones that were completed on time as 1.
• Mark milestones that were completed late as 0.
• Fill the formula down the full dataset.

3. Create the days-late column

• Subtract the planned date from the actual date.
• This column is not used directly in the KPI calculation, but it helps validate delays and can support additional analysis later.

4. Build a combined milestone category

• Because milestone names repeat across different project types, create a new combined field that joins:
  • project type
  • milestone name
• This prevents residential and commercial milestones with the same step number from being grouped together incorrectly.
• Combine the values into one unique label so each milestone category is tracked separately.

5. Generate a unique list of milestone categories

• Use the UNIQUE function on the combined milestone category column.
• Paste the results as values so you can sort and work with a static list.
• Add new headers beside it for:
  • total count
  • late count
  • percent late

6. Count the total number of milestone occurrences

• Use COUNTIF to count how many times each combined milestone category appears in the dataset.
• This gives you the denominator for the milestone analysis.
• Fill the formula down for every milestone category in the summary table.

7. Count how many milestones were late

• Use COUNTIFS with multiple criteria:
  • the combined milestone category
  • the on-time flag equal to 0
• This gives you the number of late milestones for each milestone group.
• Fill the formula down the summary table.

8. Calculate the late percentage

• Divide the late count by the total count for each milestone category.
• Format the result as a percentage.
• This gives you the percentage of milestones that were completed late for each category.

9. Create the first chart

• Highlight the milestone category labels and the percent-late column.
• Insert a chart using Recommended Charts.
• Choose one of the top bar or column chart options for the clearest view.
• Rename the chart title to something like Percent Late by Milestone.

10. Sort the summary table for better chart readability

• Sort the percent-late column from largest to smallest.
• This makes the highest-risk milestone categories appear first.
• Resize the chart and clean up the formatting so the labels are easier to read.

11. Format the chart axis

• Edit the percentage axis so it shows cleaner increments such as:
  • 0%
  • 5%
  • 10%
• Remove unnecessary decimals to make the chart easier to interpret.

12. Create a second milestone list for the PM heat map

• Generate the unique milestone category list again.
• This time, keep it in its natural order instead of sorting it by percent late.
• Create a second layout for the heat map section.
• Add project manager names across the top by:
  • generating a unique list
  • sorting it alphabetically
  • transposing it horizontally

13. Build the PM-by-milestone matrix

• Use COUNTIFS to calculate how many late milestones each project manager has for each milestone category.
• Then divide that count by the total number of milestones for that same category and manager combination.
• This creates one percentage for each PM and milestone intersection.

14. Lock the cell references correctly

• Use mixed references so:
  • the project manager header stays fixed when copying downward
  • the milestone label stays fixed when copying across
• Paste the formula across and down the matrix instead of dragging it if the table references start shifting unexpectedly.

15. Format the heat map

• Format the matrix as percentages.
• Apply Conditional Formatting to highlight cells above a chosen threshold.
• In the video, the threshold is set to anything greater than 30%.
• This makes it easy to spot which project managers and milestone stages are creating the most delays.

16. Add a target label to the threshold cell

• If you want the threshold cell to display a label such as Target, keep the cell numeric and format it with Custom Format.
• This allows the cell to remain usable in conditional formatting while still showing a descriptive label to the team.

17. Review the output

• Use the first chart to identify which milestone stages are slipping most often.
• Use the PM heat map to see whether delays are concentrated:
  • in specific project types
  • at specific milestone stages
  • under specific project managers
• This helps surface problems earlier instead of waiting until the full project is already late.