Improve Customer Service with First Contact
Resolution Rate % in Excel

How to Calculate First Contact Resolution Rate % in Excel

1. Create a Month-End Date Column

• Start with the open date for each case or job.
• Use the EOMONTH function to convert each open date into the end of its month.
• This gives you a consistent monthly grouping field for the rest of the analysis.
• Format the new column as a date so the month-end values display correctly.

2. Build a Unique List of Months

• Use the UNIQUE function on the month-end column you just created.
• This removes duplicates and gives you one row for each reporting month.
• This list becomes the timeline for your KPI table and chart.

3. Count the Total Number of Cases by Month

• Since each row represents one job or case, count how many rows belong to each month.
• Use the COUNTIF function against the month-end column.
• This gives you the total case volume for January, February, March, and so on.
• Label this column something like Number of Cases.

4. Count the Cases Resolved on First Contact

• Use the resolution flag column to identify which cases were resolved on the first interaction.
• In the video, resolved cases are marked with a 1.
• Use the SUMIFS function to total those resolved cases by month.
• This gives you the number of cases that were successfully resolved on first contact for each reporting period.

5. Calculate the First Contact Resolution Rate %

• Divide the resolved-on-first-contact count by the total number of cases for each month.
• Format the result as a percentage.
• Add decimals only if you want more precision in the display.
• Fill the calculation down for all months in your table.

6. Add a Target Line

• Create a target column next to the KPI results.
• Enter your target percentage once, then reference that same value down the column.
• This keeps the target dynamic, so if you change it once, the full chart updates automatically.
• In the video example, the target is set at 75%.

7. Select the Data for the Chart

• Highlight the month column and the total case volume column first.
• Then hold Control and also select the First Contact Resolution Rate % column and the target column.
• This allows you to chart the case counts together with the KPI percentage and the target line.

8. Insert a Combo Chart

• Go to Insert and choose a Combo Chart.
• Set the Number of Cases series as columns.
• Set the First Contact Resolution Rate % and Target series as lines.
• This creates a mixed view that shows both workload and performance on the same chart.

9. Move the Percentage Series to a Secondary Axis

• The case counts and percentage values are on very different scales.
• Change both the KPI line and the target line to the secondary axis.
• This makes the percentage trend readable instead of compressing it at the bottom of the chart.
• You can place the percentage axis on the left or right depending on which view you prefer.

10. Clean Up the Formatting

• Adjust the percentage axis so it displays whole percentages instead of long decimals.
• Rename the chart with the KPI title.
• Resize the chart and table so they are easier to read.
• This makes the final output presentation-ready.

11. Add Data Labels Only Where They Matter

• Adding labels to every series can make the chart too noisy.
• Instead, add labels only to the KPI line if you want to emphasize the monthly percentages.
• Another good option is to label only the peak and low points.
• You can also format those labels with a background fill or bold text to make them stand out.

12. Extend the Analysis Further

• Once the monthly structure is built, you can reuse the same setup at a more detailed level.
• Instead of month, you could analyze by CSR, dispatcher, or technician.
• The same framework helps you identify outliers and performance gaps across individuals or teams.
• That makes it easier to spot where coaching or process improvement is needed.