Cellwise Start free

Free Excel template · Excel for call centers

Free call center KPI dashboard template

For team leads and analysts who want the six call center metrics everyone asks about on one sheet, built from a plain call log rather than a reporting tool. Paste one row per call (wait time, outcome, handle time, whether it was resolved on first contact, and the survey score if there was one), set your service level threshold, short abandon cutoff and targets, and the dashboard works out service level, abandonment rate, average handle time, occupancy, first contact resolution and CSAT, each with an On target or Off target status.

cellwise-call-center-kpi-dashboard.xlsxExcel workbook · 5.1 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup
Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

RowABCDEFG
1Call center KPI dashboard
2Paste your call log from row 19. Grey inputs and the Target column are yours to set.
3Service level threshold (seconds)20
4Short abandon cutoff (seconds, 0 = count all)5
5Agent staffed hours for this log1.8
6
7KPIActualTargetStatusVolumeCount
8Service level (higher is better)65%80%Off targetCalls offered22
9Abandonment rate (lower is better)10%5%Off targetAnswered18
10Average handle time, seconds (lower is better)316330On targetAbandoned4
11Occupancy (keep at or below)88%85%Too highShort abandons left out2
12First contact resolution (higher is better)78%75%On targetAnswered within threshold13
13CSAT, % rating 4 or 5 (higher is better)73%80%Off targetAHT as m:ss5:16
14Surveys returned15
15KPIs on target2 of 6
16
17
18Call IDAgentWait (s)OutcomeHandle (s)Resolved first contactCSAT (1-5)
19C-1001Ana4Answered286Yes5
20C-1002Ben12Answered341Yes4
21C-1003Chen31Answered512No2
22C-10043Abandoned
23C-1005Dev9Answered228Yes
24C-1006Ana18Answered304Yes5
25C-1007Ben45Answered397No3
26C-100862Abandoned
27C-1009Chen7Answered265Yes4
28C-1010Dev14Answered289Yes5
29C-1011Ana26Answered455No
30C-1012Ben5Answered198Yes4
31C-10132Abandoned
32C-1014Chen11Answered322Yes5
33C-1015Dev38Answered366Yes3
34C-1016Ana16Answered241Yes4
35C-101747Abandoned
36C-1018Ben8Answered309No2
37C-1019Chen19Answered277Yes5
38C-1020Dev6Answered254Yes
39C-1021Ana22Answered418Yes4
40C-1022Ben13Answered233Yes5

How the formulas work

Service level with short abandons left out

B8=IF(G8-G11>0,G12/(G8-G11),0)

Calls answered within the threshold in B3 (G12, a COUNTIFS on outcome and wait time) divided by calls offered minus short abandons. The sample has 22 calls, 2 of them abandoned within 5 seconds, and 13 of the remaining 20 were answered within 20 seconds: 65%. Set B4 to 0 if your center counts every abandoned call.

Abandonment rate on the same basis

B9=IF(G8-G11>0,(G10-G11)/(G8-G11),0)

Abandoned calls minus short abandons, over calls offered minus short abandons, so the service level and abandonment rate use the same denominator. Here 2 of 20 calls were abandoned after the cutoff: 10%, double the 5% target.

Average handle time from answered calls only

B10=IFERROR(ROUND(AVERAGEIF(D19:D40,"Answered",E19:E40),0),0)

AVERAGEIF averages the handle seconds of answered calls; abandoned calls have no handle time and would drag the average down if they were counted as 0. The result is in whole seconds, and G13 shows it as m:ss with =TEXT(B10/86400,"m:ss"): 316 seconds is 5:16.

Occupancy from handle time and staffed hours

B11=IF(B5>0,SUMIF(D19:D40,"Answered",E19:E40)/(B5*3600),0)

Total handle time divided by the hours agents were staffed and available for calls during the same period (B5, converted to seconds). Take staffed time from your phone system or schedule, excluding breaks, meetings and training. 5,695 seconds of handling in 1.8 staffed hours is 87.9% occupancy, above the 85% you set as the ceiling, so the status says Too high.

FCR and CSAT from the same log

B13=IFERROR(COUNTIF(G19:G40,">=4")/COUNT(G19:G40),0)

CSAT counts ratings of 4 or 5 and divides by the number of ratings, so calls without a survey (blank cells) are left out rather than counted as unhappy. FCR in B12 works the same way with Yes and No, ignoring abandoned calls that have neither. In the sample, 11 of 15 surveys were a 4 or 5 (73.3%).

Make it yours

FAQ

What KPIs should a call center dashboard show?

Most start with service level, abandonment rate, average handle time, occupancy, first contact resolution and customer satisfaction. The first four describe speed and staffing, the last two quality. Showing both stops a team from improving speed by rushing customers off the phone.

How do I calculate service level in Excel?

Count calls answered within your threshold with COUNTIFS, for example =COUNTIFS(D19:D40,"Answered",C19:C40,"<="&20), and divide by calls offered. Whether you also subtract short abandons from calls offered, or divide by answered calls instead, is a definition your center chooses; pick one and keep it.

Why is my AHT different from the phone system report?

Usually because of what is included. Check whether the system counts hold time and after-call work, whether transfers count as one call or two, and whether you are averaging interval averages instead of dividing total handle time by total calls.

Can I use this for chat or email?

Yes for handle time, FCR and CSAT. For chat, an agent can handle several conversations at once, so occupancy from handle time can go above 100%; many teams track concurrency separately instead.

Functions used

Related guides

More free templates

Updated