How to Track Rework Rate % and Reduce Costly
Project Mistakes

How to Calculate Rework Rate % in Excel

1. Create a unique list of Project Managers

• Start with the Project Manager column from your dataset.
• Use the UNIQUE function to remove duplicate names.
• Wrap it with the SORT function so the Project Managers appear in alphabetical order.
• This gives you a clean list down the left side of your matrix.

2. Create a unique list of system types

• Use the System Type column to build the categories across the top of the report.
• If your data is not already in an Excel table, convert it into one with Ctrl + T.
• Use the UNIQUE function on the System Type field.
• Wrap it with SORT to alphabetize the list.
• Copy the result and paste it across the top using Transpose so the system types run horizontally.

3. Add overall rows and columns

• Add an Overall row for system type totals.
• Add an Overall column for Project Manager totals.
• This lets you compare detailed combinations against broader averages.
• It also helps you see whether a specific PM or system type is driving the company average.

4. Calculate the overall company rework rate

• Start with the overall metric before building the detailed matrix.
• Use the SUM function on the binary Rework Flag column to count all reworked jobs.
• Use COUNTA on a complete field such as Project ID to count total jobs.
• Format the result as a percentage.
• This gives you the company-wide rework rate baseline.

5. Build the detailed PM by system type matrix

• Use COUNTIFS to count reworked jobs for each Project Manager and System Type combination.
• Because the rework field is binary, counting the rows where the flag equals 1 gives you the reworked job count.
• Then use COUNTIFS again to count the total jobs for that same Project Manager and System Type combination.
• Divide the reworked count by the total job count.
• This gives you the Rework Rate % for each cell in the matrix.

6. Fix the references so the formula can be copied across the grid

• Before copying the formula, update the references so they behave correctly when moved across rows and columns.
• Lock the Project Manager reference so it changes correctly as you move down, but not across.
• Lock the System Type reference so it changes correctly as you move across, but not down.
• Use the F4 key or dollar signs to control the row and column locking.
• Once the references are set properly, copy and paste the formula across the full matrix.

7. Fill in the overall system type row

• Copy the same logic used in the main matrix.
• Remove the Project Manager condition so the calculation returns the rework rate for each system type across the entire company.
• Format the results as percentages.
• Paste the formula across the row.

8. Fill in the overall Project Manager column

• Copy the matrix formula again.
• Remove the System Type condition so the calculation returns the rework rate for each Project Manager across all jobs.
• Paste the formula down the column.
• This gives you a quick way to compare PM performance without looking at individual system types.

9. Format the report as percentages

• Select the matrix and overall cells.
• Use Ctrl + Shift + 5 to format the results as percentages.
• This makes the report easier to scan and compare.

10. Add conditional formatting with a target threshold

• Highlight the matrix and any overall cells you want included.
• Use Conditional Formatting and choose Highlight Cells Rules.
• Set the rule to highlight cells greater than a chosen target, such as 15%.
• This creates a simple heat map that flags high rework areas instantly.
• Because the threshold is based on a cell value, you can change the target later and the highlights will update dynamically.

11. Add conditional formatting based on the company average

• Clear the first formatting rule if you want a different view.
• Highlight the detailed matrix again.
• Create another Highlight Cells Rules condition using the overall company rework rate cell as the comparison point.
• This highlights every PM and system type combination performing worse than the company average.
• It is a useful way to identify where coaching, training, or troubleshooting should begin.

12. Use the matrix to spot where rework is concentrated

• The completed matrix shows much more than the overall company percentage.
• It lets you see which specific Project Managers and which specific system types are contributing most to rework.
• The overall company rate may look manageable, but the detailed breakdown reveals where the real issues are.
• That is what makes the matrix and heat map valuable for operational review.