Service NPS Score Analysis by Month and Technician

Service NPS Score by Month and Technician

1. Set up the months list for the monthly summary

• In cell B2, type Month.
  
• In cell B3, type January.
  
• Drag January down until you have all 12 months through December.
  

2. Convert your dataset into an Excel Table

• Click anywhere inside your raw dataset.
  
• Press Ctrl + T to convert it into a table.
  
• Make sure My table has headers is checked.
  
• Working in a table helps formulas auto-fill and makes references easier to audit.
  

3. Create a Month column inside the table using the date

• Add a new column in the table called Month.
  
• In the first row of that Month column, use the TEXT function to extract the month name from your date field:
  
  • Type =TEXT(
    
  • Click the date cell in the same row
    
  • Add a comma and enter “mmmm” in quotes
    
  • Close the formula and press Enter
    
• Confirm the table auto-fills the formula down the entire column.
  

4. Count total surveys per month

• Next to your months list, calculate how many survey records exist for each month using COUNTIFS.
  
• Use the table Month column as the criteria range, and the month name (January, February, etc.) as the criteria.
  
• When selecting the Month field, click the table column data area (not the sheet column letter) so your formula references the table field correctly.
  

5. Calculate Promoters percent per month

• Promoters are scores greater than or equal to 9.
  
• Use COUNTIFS with two criteria:
  
  • NPS Score >= 9
    
  • Month = the month in your list
    
• Divide the promoters count by your total count for the month to get a percentage.
  
• Format the result as a percent using Ctrl + Shift + 5.
  
• Copy the formula down through all twelve months.
  

6. Calculate Detractors percent per month

• Detractors are scores less than or equal to 6.
  
• Copy your Promoters COUNTIFS formula and change the score criteria to <=6.
  
• Divide by the total monthly count again.
  
• Format as percent using Ctrl + Shift + 5.
  
• Copy down through all months.
  

7. Calculate the Service NPS Score per month

• Subtract the detractors percent from the promoters percent.
  
• Multiply the result by 100.
  
• Use parentheses so Excel does the subtraction first:
  
  • (Promoters% – Detractors%) * 100
    
• Format the result as a number using Ctrl + Shift + 1.
  
• Copy down through all months.
  

8. Create a Benchmark line that stays dynamic

• Enter a benchmark value in a cell (this is the number you will adjust later).
  
• Under your Benchmark header, reference that benchmark cell with an equals sign so the value repeats down the column.
  
• This keeps the benchmark dynamic, so changing the benchmark updates the chart automatically.
  

9. Build the monthly combo chart

• Select Month and Count.
  
• Hold Ctrl and also select the NPS Score and Benchmark columns.
  
• Go to Insert, then Recommended Charts, then All Charts.
  
• Choose Combo chart.
  
• Set the chart types:
  
  • Count as clustered columns
    
  • NPS Score as a line
    
  • Benchmark as a line
    
• Put NPS Score and Benchmark on a secondary axis so the lines are readable next to the count bars.
  

10. Clean up the chart formatting

• Update the chart title (example used: Net Promoter Score by Month).
  
• Adjust the secondary axis bounds to make the line variation easier to see (the video shows tightening the range, like 20 to 60, depending on your data).
  
• Format the benchmark line so it reads like a reference line:
  
  • Change it to a thin line
    
  • Change it to a dotted style
    
• Add data labels to the NPS line:
  
  • Click the NPS line
    
  • Click the chart elements button (the plus icon)
    
  • Turn on Data Labels
    
• If labels overlap:
  
  • Click a label once
    
  • Drag it slightly to reposition
    
• If you want the labels to stand out more, adjust formatting (color, size) to match your dashboard style.
  

11. Build the technician-by-month layout

• Create a unique list of technicians from the table using UNIQUE.
  
• Copy and paste values using Ctrl + Shift + V so the list is static.
  
• Type Technician as the header above the technician list.
  
• Across the top, type January and drag across to December.
  

12. Build the NPS formula by technician and month

• In the first technician-month cell, create the promoters portion using COUNTIFS:
  
  • NPS Score >= 9
    
  • Technician = the technician name in the row
    
  • Month = the month header at the top
    
• Divide by the total count for that same technician and month:
  
  • Use another COUNTIFS that counts rows where Technician matches and Month matches
    
• Create the detractors portion using the same structure:
  
  • Replace the >=9 criteria with <=6
    
  • Divide by the same technician-month total count
    
• Subtract detractors percent from promoters percent and multiply by 100:
  
  • (Promoters% – Detractors%) * 100
    

13. Fix copy-across and copy-down with absolute references

• Lock the technician reference so it does not change when dragging left or right.
  
• Lock the month reference so it does not change when dragging down.
  
• Confirm the behavior matches the video:
  
  • Dragging across changes January to February to March
    
  • Dragging down changes the technician name
    
  • The technician criteria does not shift to the wrong column
    
• Note from the video: because structured table references cannot be locked with dollar signs the same way, use the workaround shown:
  
  • Copy the corrected formula
    
  • Paste it into the grid to avoid table-locking issues
    

14. Add the heat map with conditional formatting

• Enter a benchmark number in a cell (the video uses an example like 40).
  
• Select the technician-month NPS grid.
  
• Go to Home, then Conditional Formatting, then Highlight Cells Rules, then Less Than.
  
• Click the benchmark cell so the rule stays tied to that value.
  
• Confirm the heat map updates dynamically when you change the benchmark number.
  

15. Add a “Benchmark:” label without breaking the number

• Select the benchmark cell.
  
• Press Ctrl + 1 to open Format Cells.
  
• Choose Custom.
  
• Add a custom number format that displays text while keeping the value numeric, for example:
  
  • “Benchmark: “0
    
• Confirm conditional formatting still works, since the cell is still a number.