How to Track Customer Effort Score (CES) and
Improve Customer Experience

How to Track Customer Effort Score (CES) in Excel

1. Create the Month-End Cohort Column

• Start with the job or open date from your dataset.
• Use the EOMONTH function to convert each date into its month-end value.
• Fill the formula down so every survey response is assigned to a month-end cohort.
• This creates a clean monthly grouping that can be used for analysis and charting.

2. Build a Unique List of Months

• Create a separate summary table for your reporting section.
• Use the UNIQUE function on the month-end column you just created.
• This gives you one row per month instead of repeated dates from the raw survey data.
• Make sure the months stay in sequential order so the chart flows correctly from one month to the next.

3. Count the Number of Surveys by Month

• Add a column for survey count.
• Use the COUNTIF function to count how many survey records belong to each month in your summary table.
• Fill the formula down for all months.
• This gives you the monthly survey volume, which helps add context to the score trend.

4. Calculate the Monthly Average Customer Effort Score

• Add a column for the monthly average score.
• Use AVERAGEIFS to calculate the average customer effort score for each month.
• Reference the score column as the average range and the month-end column as the criteria range.
• Fill the formula down to complete the full monthly trend.
• Format the results to two decimal places for the worksheet.

5. Add a Target Line

• Create a target column next to the average score.
• Enter the target once and link the rest of the cells to that first target value.
• Fill the target down so it repeats across every month.
• This makes the target line dynamic, so if you change the target once, the full chart updates automatically.

6. Insert a Combo Chart

• Highlight the summary table or click anywhere inside it.
• Go to Insert, then Recommended Charts, then All Charts, and choose Combo.
• Set the monthly survey count as a column series on the secondary axis.
• Set the average customer effort score as a line on the main axis.
• Keep the target line on the main axis as well so it can be compared directly to the average score.

7. Format the Axes and Line Styles

• Format the main score axis so it shows one decimal place instead of two.
• Keep the worksheet values more precise if needed, but simplify the chart for readability.
• Adjust the target line styling so it is visible without taking over the chart.
• Make the target line thinner or use a different line style to separate it visually from the main score trend.

8. Add Data Labels Selectively

• If you add labels to everything, the chart can become too crowded.
• Instead, click only the score line and add labels there.
• You can also label only the highest and lowest points if you want a cleaner visual.
• Adjust the label number formatting to one decimal place to match the chart axis.
• If needed, move labels slightly or add a background so they are easier to read.

9. Finalize the Chart Title and Layout

• Update the chart title so it clearly reflects the metric being tracked.
• Keep the design clean so the main score trend remains the focus.
• Use survey count as supporting context rather than the primary visual.
• This creates a chart that shows both the customer effort trend and the monthly response volume in one view.

10. Use the Dashboard to Monitor Trends

• Review the monthly average score against the target line.
• Use the survey count to judge whether a month’s result is based on a strong response volume or a smaller sample.
• Update the target as needed and let the chart refresh automatically.
• This gives you a simple monthly reporting view built from one-question survey data.