Excel for call centers and contact centers
For team leads, workforce planners and analysts who get an interval report out of the phone system every morning and need answers before the 9 o'clock huddle. Excel turns that export into the call center metrics people actually ask about: service level, average handle time, abandonment rate, occupancy, first contact resolution and CSAT, with targets in cells you can change. Every formula below runs on one queue's half-hour interval report, so you can see exactly how each number is built and why the day total is not the average of the intervals.
- Contact center team leads and supervisors
- Workforce management (WFM) planners
- Customer support and quality analysts
- BPO operations managers
- Help desk and service desk leads
Sample data
Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Interval | Offered | Answered | Answered in 20s | Abandoned | Handle time (s) | Staffed time (s) | Survey responses |
| 2 | 08:00-08:30 | 38 | 37 | 34 | 1 | 10545 | 12600 | 146 |
| 3 | 08:30-09:00 | 52 | 50 | 41 | 2 | 15050 | 16200 | Satisfied (4 or 5) |
| 4 | 09:00-09:30 | 71 | 64 | 44 | 7 | 19968 | 21600 | 121 |
| 5 | 09:30-10:00 | 77 | 69 | 46 | 8 | 21942 | 23400 | Resolved contacts |
| 6 | 10:00-10:30 | 69 | 66 | 52 | 3 | 19536 | 23400 | 512 |
| 7 | 10:30-11:00 | 63 | 61 | 53 | 2 | 17690 | 21600 | Resolved on first contact |
| 8 | 11:00-11:30 | 58 | 57 | 51 | 1 | 16188 | 19800 | 386 |
| 9 | 11:30-12:00 | 60 | 58 | 49 | 2 | 17690 | 19800 | Service level target |
| 10 | 12:00-12:30 | 66 | 60 | 42 | 6 | 19320 | 19800 | 0.8 |
| 11 | 12:30-13:00 | 49 | 48 | 41 | 1 | 14352 | 16200 | Occupancy cap |
| 12 | 0.9 |
What Excel does for call centers
Calculate service level for the day
Service level is the share of calls answered within a threshold, written as 80/20 when the goal is 80% of calls answered within 20 seconds. The phone system usually exports "answered within threshold" as a count, so the math is that count divided by calls offered.
Add up the counts first and divide once. Averaging the ten interval percentages would give a quiet 08:00 interval as much weight as the 09:30 peak. Centers also differ on the bottom line: some divide by calls answered, some subtract short abandons from calls offered. Any of these is fine if it is written on the report and never changes month to month. To build service level from a raw call log with COUNTIFS, and see the four definitions side by side, read how to calculate service level in Excel. For a step-by-step version with a ticket timestamp log, see SLA compliance in Excel.
Result: 0.75124378109 shows as 75.1% with Percent format
75.1%: 453 of 603 calls were answered within 20 seconds, below the 80% target in H10.
Find the intervals that missed service level
A day total can hide a terrible hour. Divide each interval's answered-in-threshold count by its offered calls and count the results under the target. SUMPRODUCT does that for every row at once without a helper column; the double minus turns TRUE and FALSE into 1 and 0.
The missed intervals are what you take to the scheduler: they show when you were short of agents, not just that you were.
Result: 5
5 of the 10 intervals were below 80%, including both intervals from 09:00 to 10:00 and the lunch interval at 12:00. The worst was 09:30 to 10:00, where only 46 of 77 calls (59.7%) were answered in time.
Work out average handle time (AHT)
Average handle time is total handle time (talk, hold and after-call work) divided by calls handled. Do it from totals, the same way as service level: SUM the handle seconds and SUM the answered calls, then divide. Abandoned calls have no handle time, so divide by answered, not offered.
If your report shows handle time as mm:ss rather than seconds, Excel stores those as fractions of a day: multiply by 86400 to get seconds. To show seconds as minutes and seconds, use =TEXT(seconds/86400,"m:ss"). More in average handle time in Excel.
Result: 302
302 seconds, about 5 minutes 2 seconds per call: 172,281 seconds of handle time across 570 answered calls.
Calculate the abandonment rate
Abandonment rate is abandoned calls divided by calls offered. It moves with service level: when the wait gets long, people hang up. Here it is total abandoned over total offered.
Many centers leave out short abandons (callers who hang up within a few seconds, often wrong numbers) from both the top and the bottom of the fraction. If you do, subtract them from both, and say so on the report. The call abandonment rate guide shows the short-abandon version with COUNTIFS.
Result: 0.054726368159 shows as 5.5% with Percent format
5.5%: 33 of 603 callers hung up before an agent answered. 15 of those 33 came in the two intervals between 09:00 and 10:00.
Measure agent occupancy and spot burnout risk
Occupancy is the share of staffed time agents spent handling contacts: handle time divided by staffed time (handling plus waiting for the next call, not breaks or meetings). Low occupancy means more people than calls; very high occupancy means calls land back to back with no breathing room.
Many centers set a cap and watch the intervals that go over it. Like everything else here, the cap is a number you choose and keep in a cell (H12), not a rule from Excel. Details in agent occupancy in Excel.
Result: 4
4 intervals ran above the 90% cap. 12:00 to 12:30 hit 97.6%: 19,320 seconds of handling in 19,800 staffed seconds, while lunches pulled agents off the phones.
First contact resolution (FCR)
FCR is the share of resolved contacts that were solved on the first contact, with no repeat call, email or chat about the same issue. Here the ticketing system reports both counts, so FCR is one division.
If you only have a contact log, you have to find repeats yourself: the same customer contacting again within a window you choose (7 days is common). The first contact resolution guide builds that flag with COUNTIFS on customer ID and date.
Result: 0.75390625 shows as 75.4% with Percent format
75.4%: 386 of 512 resolved contacts needed only one contact.
CSAT as the percent of satisfied customers
CSAT is usually reported as the share of survey responses rating 4 or 5 on a 5-point scale, not the average score. An average of 4.1 hides whether the misses are a few 1s or lots of 3s; the percentage is easier to set a target on.
Divide by responses received, never by calls handled: customers who skip the survey are not unhappy, they are unknown. With raw ratings in a column, =COUNTIF(range,">=4")/COUNT(range) does the same; see CSAT score in Excel.
Result: 0.82876712329 shows as 82.9% with Percent format
82.9%: 121 of 146 survey responses were a 4 or 5.
Practice: 8 exercises
Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons. Solved: 0
-
1How many calls were offered across the whole morning?
Show answer
=SUM(B2:B11)= 603. Calls offered is the bottom line of most call center ratios: 603 here. -
2What was the service level for the 09:00 to 09:30 interval (row 4)?
Show answer
=D4/B4= 0.61971830986. 44 / 71 = 62.0%, well under the 80% target. -
3What share of calls offered in the 09:30 interval (row 5) were abandoned?
Show answer
=E5/B5= 0.1038961039. 8 / 77 = 10.4%. Long waits and abandons rise together. -
4Which interval had the most calls offered? Return its name.
-
5What was average handle time for the morning, in whole seconds?
-
6What was agent occupancy for the whole morning?
-
7How many intervals had a service level below the target in H10?
Show answer
=SUMPRODUCT(--(D2:D11/B2:B11<H10))= 5. The comparison gives TRUE or FALSE per interval and the double minus makes them countable: 5. -
8What was the service level if you divide by answered calls instead of offered calls?
Free templates for call centers
- Call center KPI dashboard templateFor 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.Free .xlsx
- Call center shrinkage calculator templateFor workforce planners and team leads who need to know how much scheduled time never reaches the phones, and how many agents to schedule because of it.Free .xlsx
Guided project: Call center agent scorecard
Guides for these formulas
- How to calculate average handle time (AHT) in Excel
- How to calculate first contact resolution (FCR) in Excel
- How to calculate call abandonment rate in Excel
- How to calculate agent occupancy in Excel
- How to calculate CSAT score in Excel
- How to calculate schedule adherence in Excel
- How to calculate SLA compliance in Excel
- How to calculate contact center shrinkage in Excel
FAQ
What are the most common call center metrics to track in Excel?
Service level, average handle time, abandonment rate, occupancy, first contact resolution and CSAT cover speed, staffing and quality. Add schedule adherence and customer effort score once those are stable. Each is a simple ratio in Excel once the export is clean.
Why does my daily service level not match the average of my intervals?
Because intervals have different call volumes. The daily figure should be total answered within threshold divided by total offered, which weights each interval by its calls. AVERAGE of the interval percentages treats a 38-call interval the same as a 77-call one.
How do I convert handle time in mm:ss to seconds in Excel?
If the cell holds a real time value, multiply by 86400: =A2*86400. If 5:03 means 5 minutes 3 seconds but Excel read it as 5 hours 3 minutes (it does that with h:mm entries), divide by 60 as well. Check with a call you know the length of.
Can Excel calculate how many agents I need (Erlang C)?
Yes, but it takes a longer formula built on Excel's POISSON.DIST function or a table of factorials, which this page does not cover. Once you have the number of agents needed on the phones, the free shrinkage calculator shows how many to schedule.
Excel for other fields
- Auto repair
- Bookkeeping
- Cleaning services
- Coaches and sports teams
- Construction and trades
- Event planning
- Farms and growers
- Fitness and habits
- Freelancers
- Home and family
- HR and people ops
- Marketing
- Medical office
- Nonprofits
- Personal finance and taxes
- Project management
- Real estate
- Restaurants
- Retail and ecommerce
- Sales teams
- Salons and spas
- Small business
- Students
- Teachers
- Trucking
- Warehouse and logistics
Updated