Excel guide · 6 min read
How to calculate contact center shrinkage in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Week of | Team | Paid hrs | Breaks | Meetings | Training | Time off | Sick | Absent/late | Lost hrs | Unplanned | Shrinkage |
| 2 | Oct 26, 2026 | Billing | 480 | 30 | 12 | 8 | 16 | 10 | 4 | 80 | 14 | 0.16666666667 |
| 3 | Oct 26, 2026 | Tech | 200 | 12.5 | 5 | 40 | 0 | 4 | 1.5 | 63 | 5.5 | 0.315 |
| 4 | Nov 2, 2026 | Billing | 480 | 30 | 10 | 0 | 24 | 22 | 6 | 92 | 28 | 0.19166666667 |
| 5 | Nov 2, 2026 | Tech | 200 | 12.5 | 4 | 32 | 8 | 6 | 2 | 64.5 | 8 | 0.3225 |
| 6 | Nov 9, 2026 | Billing | 480 | 30 | 14 | 12 | 8 | 31 | 9 | 104 | 40 | 0.21666666667 |
| 7 | Nov 9, 2026 | Tech | 200 | 12.5 | 5 | 16 | 8 | 10 | 3.5 | 55 | 13.5 | 0.275 |
Download this sheet as an Excel practice file (.xlsx)
Shrinkage for one team and week
The sheet logs three weeks for two teams. Billing has 12 agents on 40 paid hours (480), Tech has 5 (200). Columns D to I are the hours lost to each category, J adds them (=SUM(D2:I2)) and L divides by paid hours. Billing's first week lost 80 of 480 paid hours: 16.7%.
What goes in the numerator is every paid hour that could not be spent on contacts: paid breaks, team meetings, coaching, training, paid time off, sickness, absence and lateness. Unpaid lunch is in neither number, because it is not paid time. Paid time off belongs in both: it is in the 480 paid hours and in the lost hours, so a week with a lot of holiday shows higher shrinkage, which is exactly what the schedule has to cover.
Keep the hours in categories rather than one "lost" figure. The total tells you how much to gross up; the categories tell you what to fix.
Result: 0.16666666667 shows as 16.7% with Percent format
16.7%: Billing lost 80 of its 480 paid hours in the week of Oct 26.
Planned vs unplanned shrinkage
Planned shrinkage is known when the schedule is built: breaks, meetings, training, booked time off (columns D to G here). Unplanned shrinkage happens on the day: sickness, absence, lateness (H and I, added in helper column K). Planners split them because they are handled differently. Planned time can be moved to quiet intervals; unplanned time can only be forecast from history and covered with extra heads.
For the whole three weeks and both teams, HSTACK in Excel 365 puts the three rates in one row. Divide each sum of hours by the sum of paid hours. Planned and unplanned add up to the total because they share the same bottom number.
Result: spills 1 row × 3 columns
| 0.17132352941 | 0.053431372549 | 0.22475490196 |
0.171, 0.053 and 0.225: 17.1% planned, 5.3% unplanned, 22.5% total (458.5 lost of 2,040 paid hours).
By week and by team: total over total
For one week, add the lost hours of every team and divide by their paid hours. O3 holds the week (Nov 9). SUMIFS does both sums: 104 + 55 lost hours over 480 + 200 paid gives 23.4%. If you prefer no helper columns, =SUMPRODUCT((A2:A7=O3)*D2:I7)/SUMIFS(C2:C7,A2:A7,O3) multiplies the whole category block by the week test and gives the same 23.4%.
The tempting shortcut is =AVERAGE(L6:L7), the average of Billing's 21.7% and Tech's 27.5%. It says 24.6%, because it gives the 5-person team as much weight as the 12-person team. The same thing happens across weeks: Tech over all three weeks is =SUMIFS(J2:J7,B2:B7,"Tech")/SUMIFS(C2:C7,B2:B7,"Tech"), 30.4%, not the average of its weekly rates.
To compare with a target in O4 (25%): =LET(s,SUMIFS(J2:J7,A2:A7,O3)/SUMIFS(C2:C7,A2:A7,O3),IF(s<=O4,"Within target","Over by "&TEXT(s-O4,"0.0%"))) returns "Within target" for Nov 9.
Result: 0.23382352941 shows as 23.4% with Percent format
23.4% for the week of Nov 9 across both teams. Averaging the two team rates would say 24.6%.
The weekly trend: same total, different mix
One LET formula builds a small report: each week (format the first column as a date), then planned, unplanned and total shrinkage. UNIQUE lists the weeks and SUMIFS takes that list as its criteria, so every column spills three rows. Excel 365 only; in older versions put the weeks in cells and fill SUMIFS down.
The total looks steady: 21.0%, 23.0%, 23.4%. Underneath, planned shrinkage fell from 18.2% to 15.5% as Tech's new-hire training wound down (40, 32, then 16 hours), while unplanned shrinkage almost tripled, 2.9% to 5.3% to 7.9%. Billing's sick hours went 10, 22, 31. A total-only report would miss the change that matters, because training ends on a known date and sickness does not.
Result: spills 3 rows × 4 columns
| 46321 | 0.18161764706 | 0.028676470588 | 0.21029411765 |
| 46328 | 0.17720588235 | 0.052941176471 | 0.23014705882 |
| 46335 | 0.15514705882 | 0.078676470588 | 0.23382352941 |
Three rows: Oct 26 18.2% / 2.9% / 21.0%, Nov 2 17.7% / 5.3% / 23.0%, Nov 9 15.5% / 7.9% / 23.4% (planned / unplanned / total).
Agents to schedule: divide by (1 - shrinkage), don't multiply by (1 + shrinkage)
Your forecast or Erlang model says how many agents must be on the phones (net), 14 in O1. At 30% shrinkage (O2), each scheduled agent gives you only 70% of their paid time. So scheduled agents x 0.7 must equal 14, which means scheduled = 14 / 0.7 = 20.
The common mistake is 14 x 1.3 = 18.2. Schedule 18.2 and lose 30% of it: 18.2 x 0.7 = 12.74 agents on the phones, more than one short. Even rounded up to 19 it gives 13.3. Multiplying adds 30% of the net number; shrinkage is 30% of the scheduled number, which is bigger. The gap grows with shrinkage: at 30% the right factor is 1.43, not 1.30.
Round up with =ROUNDUP(O1/(1-O2),0). Using the measured rate for Nov 9 instead of the planning figure, =ROUNDUP(O1/(1-SUMIFS(J2:J7,A2:A7,O3)/SUMIFS(C2:C7,A2:A7,O3)),0) gives 19 (14 / 0.766 = 18.27). In hours it works the same way: 14 x 40 = 560 net hours / 0.7 = 800 scheduled hours. For a one-page version with your own categories, the free shrinkage calculator template does the same gross-up.
Result: spills 1 row × 2 columns
| 18.2 | 20 |
18.2 vs 20: multiplying by 1.3 understaffs; dividing by 0.7 gives the 20 agents that leave 14 on the phones.
Keep one definition so weeks compare
Centers draw the line in different places. Some leave paid time off out and report "in-center" shrinkage: take the time-off hours out of both the lost hours and the paid hours. On this sheet that turns 22.5% into 20.0%. Neither is wrong, but they are different numbers, and a week reported one way cannot be compared with a week reported the other way.
The definition also has to match how you plan. If you gross up with in-center shrinkage, the time off has to be covered separately in the schedule, or you understaff every holiday week. Write the definition in a cell next to the result (which categories count, paid or scheduled hours in the bottom), and keep the category columns the same from week to week so a new category doesn't quietly change the trend.
Shrinkage is not the same as occupancy, which measures how busy agents were while available, or schedule adherence, which checks whether they were where the schedule said. The call centers hub puts these metrics side by side.
Result: 0.19964574899 shows as 20.0% with Percent format
20.0% in-center shrinkage (time off taken out of both numbers), against 22.5% with time off included.
Common mistakes
Grossing up with net x (1 + shrinkage)
Shrinkage is a share of scheduled time, so divide: =net/(1-shrinkage). At 30%, 14 agents need 20 scheduled, not 18.2.
Averaging team or week percentages
A small team then counts as much as a big one. Sum lost hours and paid hours separately, then divide (SUMIFS over SUMIFS).
Putting unpaid lunch in the lost hours
Unpaid time is not in paid hours, so it can't be in the lost hours either. Only paid time that isn't available for contacts counts.
Changing what counts halfway through the year
Adding or dropping time off or a new category moves the rate with no real change. Fix the definition in a cell and keep the columns the same.
FAQ
What is the formula for shrinkage in a contact center?
Shrinkage % = paid hours not available for contacts / total paid hours for the same period. In Excel: =SUM(lost_hours)/paid_hours, formatted as a percentage. For several teams or weeks, sum both columns first and then divide.
How do I calculate required staff with shrinkage?
Divide the agents (or hours) you need on the phones by 1 minus shrinkage and round up: =ROUNDUP(14/(1-0.3),0) gives 20. The net number comes from your forecast or staffing model; shrinkage only converts it into the number to schedule.
What is a normal shrinkage percentage for a call center?
Figures around 30% to 35% are often quoted, but the number depends heavily on what you include (time off, training, coaching) and whether you divide by paid or scheduled hours. Compare against your own history built on the same definition rather than a general benchmark.
What is the difference between shrinkage and absenteeism?
Absenteeism counts only unplanned absence, and HR often measures it as days lost against days scheduled. Shrinkage includes absence plus all planned time away from contacts such as breaks, meetings and training. See absenteeism rate for that calculation.
Should shrinkage use paid hours or scheduled hours?
Either can work as long as you use the same one every period and the gross-up uses the same base. Paid hours is common because it includes paid time off; scheduled hours leaves out time off that was never scheduled, which gives a lower rate.
Functions used
SUMAdds numbersSUMIFSAdds with several conditionsSUMPRODUCTMultiply then addHSTACKJoins arrays side by sideUNIQUERemoves duplicates, spillsLETNames values inside a formulaAVERAGEMean of numbersROUNDUPRounds away from zeroIFPicks a result from a testTEXTNumber to formatted text
Related guides
- How to calculate agent occupancy in Excel
- How to calculate schedule adherence in Excel
- How to calculate absenteeism rate in Excel
- How to calculate service level in Excel
- How to calculate backorder rate in Excel
- Cost per mile formula in Excel
- How to calculate percentage of subtotal in Excel
Updated