How to Track Warranty Callback Rate % in Excel
to Protect Project Margins

How to Calculate Warranty Callback Rate % by PM in Excel

1. Create a Unique List of PM Names

• Start by pulling a unique list of project manager names from the PM column.
• Use the UNIQUE function to remove duplicates.
• Copy the results and paste them as values so the list becomes static.
• Keep the list unsorted at first if you plan to sort later based on callback performance.

2. Set Up the Summary Table

Create the columns you need for the analysis:

• PM Name
• Number of Jobs
• Callback Count
• Callback Rate %
• Callback Cost
• Target %

This gives you the structure for both the KPI calculations and the final chart.

3. Count the Number of Jobs for Each PM

• Use COUNTIF or COUNTIFS to count how many rows in the dataset belong to each PM.
• This becomes the total job count for each person.
• Fill the formula down the table so every PM gets a job total.

4. Calculate the Number of Warranty Callbacks

• Use SUMIFS on the warranty callback flag column.
• Since the callback field is binary, summing it gives you the total number of callbacks.
• Match each result to the PM name in your summary table.
• Fill the formula down for all PMs.

5. Calculate the Callback Rate Percentage

• Divide the callback count by the total number of jobs for each PM.
• Format the results as percentages using Ctrl + Shift + 5.
• This gives you the install-related warranty callback rate for each PM.

6. Calculate the Total Callback Cost

• Use SUMIFS again, this time on the callback cost column.
• Sum the dollar value of callback costs for each PM.
• Fill the formula down the table.
• Format the values as currency using Ctrl + Shift + 4.

7. Add the Target Rate

• Enter your target callback rate in the target column, such as 6%.
• Copy it down so every PM has the same target value.
• To make future updates easier, link the rows below to the first target cell so one change updates the full column automatically.

8. Build the Initial Chart

• Select the PM names, callback count, and callback rate percentage.
• Hold Ctrl while selecting non-adjacent columns if needed.
• Go to Insert and choose Recommended Charts.
• Start with a combo chart so you can compare counts and percentages in one view.

9. Adjust the Combo Chart Settings

• Open the All Charts tab and choose Combo.
• Set the callback count as a line.
• Set the callback rate percentage as columns.
• Make sure the callback count is placed on the secondary axis so the percentage remains the main focus of the visual.

This keeps the KPI centered on the callback rate percentage instead of the raw count.

10. Sort the Data from Highest to Lowest Callback Rate

• Click on the callback rate percentage column in your summary table.
• Go to Data and sort from largest to smallest.
• This moves the worst performers to the left side of the chart, making the visual easier to interpret.

11. Add the Target Line to the Chart

• Copy the target percentage column.
• Click the chart and paste it in as a new series.
• If it appears on the wrong axis, go back into Change Chart Type.
• Keep the target as a line and remove it from the secondary axis.

This creates a benchmark line that shows whether each PM is above or below the acceptable threshold.

12. Format the Target Line

• Click the target line and open the formatting options.
• Reduce the line weight so it does not overpower the chart.
• Change the line style if needed, such as making it dotted.
• Adjust the color so it is visible but not distracting.

The goal is to make the threshold easy to see without cluttering the visual.

13. Add Data Labels Carefully

• If you want labels, add them only to the columns or the series that matters most.
• Avoid labeling everything, since it can make the chart too noisy.
• If labels are hard to read, add a background fill behind the label text to improve visibility.

14. Rename and Finalize the Chart

• Update the chart title to something clear, such as:
  Warranty Callback by PM %
• Review the axis setup, sorting, labels, and target line.
• Make sure the chart clearly shows which PMs are above target and which are below it.

15. Final Cleanup

• Confirm that the chart updates correctly when the target changes.
• Check that the PM list, callback counts, percentage values, and cost totals all align properly.
• Make sure your source data is structured consistently, since the calculations depend on clean PM names, binary callback flags, and valid callback cost values.