How to Calculate Change Order Frequency %
and Improve Project Performance

How to Calculate Change Order Frequency % in Excel

1. Create a Change Order Flag Column

• Start with the dataset and identify the fields needed for the analysis:
  • PM Name
  • System Type
  • Change Order Count
• Create a helper column that converts the change order count into a simple yes-or-no flag.
• Use the IF function so each row returns:
  • 1 when the change order count is greater than zero
  • 0 when there were no change orders
• This makes the rest of the reporting much easier because you can count or sum the ones.

2. Build a Unique List of PM Names

• Create a list of project managers using the UNIQUE function.
• Wrap it with the SORT function so the names appear alphabetically.
• Copy the results and paste them as values using Ctrl + Shift + V.
• Center-align the list if you want a cleaner layout.

This creates the row labels for the report.

3. Build a Unique List of System Types Across the Top

• Create a list of system types using the UNIQUE function.
• Use the SORT function again to alphabetize the categories.
• Use the TRANSPOSE function so the system types run across the row instead of down a column.
• Copy and paste the result as values.

This creates the column headings for the matrix.

4. Calculate the Numerator for Each PM and System Type

• For the top part of the percentage, count how many projects had a change order.
• Use COUNTIFS to count the rows where:
  • the change order flag equals 1
  • the PM Name matches the row
  • the System Type matches the column
• This gives you the number of projects with change orders for each PM and system combination.

5. Calculate the Denominator for Each PM and System Type

• For the bottom part of the percentage, count the total number of jobs for each PM and system combination.
• Use COUNTIFS again, but this time only match:
  • PM Name
  • System Type
• Do not include the change order flag in this version.
• This gives you the total number of projects in each segment.

6. Calculate the Change Order Frequency %

• Divide the numerator by the denominator to get the final percentage.
• Format the result as a percentage using Ctrl + Shift + 5.
• This gives you the Change Order Frequency % for each PM and System Type combination.

7. Fix the References Before Copying the Formula

• Before copying the formula across the full matrix, adjust the references so they behave correctly.
• Lock the PM reference so it changes when copied down, but not across.
• Lock the System Type reference so it changes when copied across, but not down.
• This uses Excel’s absolute and relative referencing correctly.
• After that, copy and paste the formula across the report area.

This allows the entire matrix to populate correctly.

8. Add an Overall Row by System Type

• Create an Overall row beneath the PM-level section.
• Start from the same structure used in the main matrix.
• Remove the PM-specific criteria so the calculation only segments by System Type.
• Keep the same logic:
  • count projects with change orders
  • divide by total projects
• Format the row as percentages.

This shows the overall change order rate for each system category.

9. Add an Overall Column by PM

• Create an Overall column at the far right of the matrix.
• Use the same logic again, but this time remove the System Type criteria.
• Keep the PM Name criteria so the result reflects each PM’s total rate across all systems.
• Format as percentages.

This gives you a second summary view that shows overall performance by project manager.

10. Add a Total Jobs Section

• Create a separate section that shows total job counts instead of percentages.
• Start with the denominator logic from the earlier calculation.
• Remove the numerator and keep only the job-count portion.
• Copy that structure across the same PM and System Type layout.
• For the overall totals:
  • use the SUM function to total across rows
  • use Alt + = for a quick autosum when needed

This helps add context to the percentages by showing how much volume is behind each rate.

11. Calculate the Main KPI

• Create the overall Change Order Frequency % for the full dataset.
• Use the SUM function on the helper column to count all projects that had change orders.
• Use COUNTA to count all rows in the dataset.
• Divide those two values to get the main KPI.
• Format the result as a percentage.

This gives you the single headline metric, while the matrix below explains what is driving it.

12. Build a Heat Map with Conditional Formatting

• Highlight the percentage section of the report.
• Create a target cell and enter a target percentage, such as 10%.
• Use Conditional Formatting with Highlight Cells Greater Than.
• Reference the target cell so any value above the threshold is highlighted automatically.
• This creates a dynamic heat map that updates whenever the target changes.

13. Format the Target Cell for Better Usability

• If you want the target cell to display a label like “Target: 20%”, format it as a custom number format.
• Use Ctrl + 1 to open the Format Cells dialog.
• Apply a Custom format so the label appears while the value still behaves like a number.
• This allows Excel to compare the target cell to the rest of the report correctly.

14. Use the Report to Analyze Performance

• Review the full matrix to compare PMs across system types.
• Use the Overall row to see which system categories drive the most change orders.
• Use the Overall column to compare PM performance across all project types.
• Use the Total Jobs section to understand whether a high percentage comes from a large or small sample size.
• Adjust the target cell to run quick sensitivity checks and identify problem areas faster.