Service CSAT Score: Find Hidden Service Quality Issues

Build the Service CSAT Score Dashboard in Excel

1. Set up the dataset columns used in the model

• Confirm you have these fields in your dataset:
  
  • Service Date
    
  • Month
    
  • Season
    
  • CSAT Score
    
  • Response Flag
    
• Create a Response Flag column so you only average real responses
  
  • Logic: if the CSAT Score cell has a value, return TRUE, otherwise FALSE
    
  • This prevents zeros from being treated as real scores and dragging down the average
    

2. Extract the month from the service date

• Create the Month field from the Service Date using a TEXT function
  
• Use this format pattern so the month displays as a month label
  
• Fill the formula down the entire dataset so every record has a month value
  

3. Build the monthly summary table

• In your summary area, generate a Month list from January through December
  
  • Type January and drag down to December
    
• Add columns for:
  
  • Feedback Count
    
  • Average Score
    
  • Target Score
    

4. Count how many CSAT responses were actually collected each month

• Use COUNTIFS to count the number of TRUE values in the Response Flag column for each month
  
• Criteria setup:
  
  • Response Flag equals TRUE
    
  • Month equals the month label in the summary table
    
• Drag the formula down for all months
  

5. Calculate the average CSAT score by month using multiple criteria

• Use AVERAGEIFS so you only average rows that meet both conditions:
  
  • Response Flag equals TRUE
    
  • Month equals the month label in the summary table
    
• Average range is your CSAT Score column
  
• Drag the formula down for all months
  

6. Add a dynamic benchmark target line

• Enter a target score in the Target column, for example 4.0
  
• Reference that target cell down the full column so the benchmark line can update dynamically
  
• Changing the target value should automatically update the benchmark line in the chart
  

7. Create the combo chart with counts, actual CSAT, and benchmark

• Select the Month, Feedback Count, Average Score, and Target columns
  
• Insert a Combo chart:
  
  • Feedback Count as clustered columns
    
  • Average Score as a line
    
  • Target as a line
    
• Put the score lines on a secondary axis so they scale correctly
  
• Optional workflow trick shown in the video
  
  • If you forgot to include a series, copy the data range, click the chart, and paste to add it as a new series
    
• Format the benchmark line to be thinner so it does not dominate the visual
  
• Add data labels to the score line if you want the exact values visible
  

8. Avoid averaging the averages when calculating an overall CSAT

• If you want an overall CSAT score, do not average the monthly averages
  
• Instead, calculate a single AVERAGEIFS across the raw data:
  
  • Average the Score column
    
  • Where Response Flag equals TRUE
    
• This produces a more accurate overall result
  

9. Build the technician by season matrix

• Create a unique list of technicians using UNIQUE
  
  • Copy and paste as values if you do not want formulas sitting in the model
    
• Create a unique list of seasons using UNIQUE
  
  • Place seasons across the top row, ordered in a logical sequence
    
  • You can transpose the seasons row using Paste Special Transpose if needed
    

10. Calculate CSAT by technician and season using AVERAGEIFS

• In the matrix, use AVERAGEIFS with these criteria:
  
  • Response Flag equals TRUE
    
  • Season equals the season header cell
    
  • Technician equals the technician name cell
    
• Use absolute and relative references correctly so you can drag:
  
  • Across seasons
    
  • Down technicians
    
• Lock dataset ranges with F4
  
• Lock the technician column reference and the season row reference so the grid fills properly without breaking
  

11. Create Heat Map version 1 using color scales

• Highlight the full technician by season matrix
  
• Apply Conditional Formatting color scales
  
• This shows relative high and low values across the grid
  

12. Create Heat Map version 2 using a benchmark threshold

• Add a benchmark cell with a numeric value, for example 3.5
  
• Highlight the matrix and apply Conditional Formatting:
  
  • Highlight Cell Rules
    
  • Less Than
    
  • Reference the benchmark cell
    
• Now anything below your benchmark turns red, which is more actionable than a pure gradient
  

13. Label the benchmark cell without breaking the number

• Do not type text into the benchmark cell directly, or Excel will treat it as text
  
• Use custom number formatting to display a label while keeping the value numeric
  
  • Format Cells
    
  • Number
    
  • Custom
    
  • Add a format like: Benchmark 0.0
    
• This lets your team understand what the cell is for while keeping it usable in formulas and conditional formatting