Cellwise Start free

Excel for your field

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.

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.

RowABCDEFGH
1IntervalOfferedAnsweredAnswered in 20sAbandonedHandle time (s)Staffed time (s)Survey responses
208:00-08:3038373411054512600146
308:30-09:0052504121505016200Satisfied (4 or 5)
409:00-09:3071644471996821600121
509:30-10:0077694682194223400Resolved contacts
610:00-10:3069665231953623400512
710:30-11:0063615321769021600Resolved on first contact
811:00-11:3058575111618819800386
911:30-12:0060584921769019800Service level target
1012:00-12:30666042619320198000.8
1112:30-13:0049484111435216200Occupancy cap
120.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.

=SUM(D2:D11)/SUM(B2:B11)

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.

=SUMPRODUCT(--(D2:D11/B2:B11<H10))

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.

=ROUND(SUM(F2:F11)/SUM(C2:C11),0)

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.

=SUM(E2:E11)/SUM(B2:B11)

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.

=SUMPRODUCT(--(F2:F11/G2:G11>H12))

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.

=H8/H6

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.

=H4/H2

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.

  1. 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.

  2. 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.

  3. 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.

  4. 4Which interval had the most calls offered? Return its name.

    Show answer

    =INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)) = 09:30-10:00. 09:30-10:00 had 77 calls, the peak of the morning.

  5. 5What was average handle time for the morning, in whole seconds?

    Show answer

    =ROUND(SUM(F2:F11)/SUM(C2:C11),0) = 302. Dividing totals weights each interval by its calls: 302 seconds.

  6. 6What was agent occupancy for the whole morning?

    Show answer

    =SUM(F2:F11)/SUM(G2:G11) = 0.8862191358. 172,281 / 194,400 = 88.6% across the morning, even though four intervals were above 90%.

  7. 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.

  8. 8What was the service level if you divide by answered calls instead of offered calls?

    Show answer

    =SUM(D2:D11)/SUM(C2:C11) = 0.79473684211. 453 / 570 = 79.5%, versus 75.1% on offered calls. Same day, different definition, which is why the report must say which one it uses.

Free templates for call centers

Guided project: Call center agent scorecard

Turn a week of agent stats into AHT, FCR and CSAT per agent, a target check and team figures weighted the right way.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open Projects

Guides for these formulas

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

Updated