Guided project · 6 steps Cellwise Plus
Call center agent scorecard in Excel
Turn a week of agent stats into AHT, FCR and CSAT per agent, a target check and team figures weighted the right way.
You will use: ROUND, IF, AND, SUM
Starting data
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Agent | Calls | Handle time (s) | Resolved first contact | Surveys | Rated 4 or 5 | AHT (s) | FCR | CSAT | Check | AHT target (s) | |
| 2 | Ana | 212 | 61480 | 171 | 38 | 33 | FCR target | |||||
| 3 | Ben | 188 | 64860 | 139 | 31 | 24 | Team AHT (s) | |||||
| 4 | Chen | 236 | 58764 | 162 | 44 | 33 | Team CSAT | |||||
| 5 | Dev | 95 | 30875 | 76 | 17 | 15 | Agents on target | |||||
| 6 | Eli | 201 | 57888 | 158 | 35 | 30 | ||||||
| 7 | Fay | 174 | 55158 | 141 | 29 | 22 |
Build it step by step
Step 1
Average handle time is handle seconds divided by calls. Write it in G2, rounded to whole seconds, and fill to G7.
Step 2
First contact resolution is calls resolved on the first contact divided by calls. Write it in H2 and fill to H7.
Step 3
CSAT is the share of surveys rated 4 or 5. Write it in I2 and fill to I7.
Step 4
In J2, show "On target" when AHT is at or below M1 AND FCR is at or above M2, otherwise "Coach". Fill to J7.
Step 5
What is the team AHT? Divide total handle time by total calls, rounded to whole seconds, in M3.
Step 6
Team CSAT the same way: total rated 4 or 5 divided by total surveys, in M4. Then count the agents on target in M5 with COUNTIF.