How to calculate utilization rate in Excel

Step 1: total billable hours per person

The sheet is one week of a small agency's time log: date, person, client, type (Billable or Internal) and time spent, typed as h:mm. On the right is a summary with each person's contracted hours, leave and available hours (=H2-I2), all as plain numbers of hours.

SUMIFS adds the time where the person matches and the type is "Billable". Lock the log ranges with $ so you can fill the formula down the summary. Format the result as [h]:mm. The square brackets let the total run past 24 hours; plain h:mm would show Maya's 26:30 as 2:30.

Step 2: divide by available hours (and multiply by 24)

This is where most utilization sheets go wrong. Excel stores a time as a fraction of a day, so Maya's 26:30 is really 1.104. Her available hours in J2 are the plain number 40. =K2/J2 divides days by hours and returns 0.0276, which shows as 3%. No error appears, just a number that is far too small.

Convert one side so both are in hours: multiply the time by 24. Wrap it in IF so a person with no available hours gets a label instead of #DIV/0!. Sam Ortiz is on leave all week, so his row shows "On leave".

Pick the right denominator

"Utilization" means different things in different firms, and the denominator changes the answer a lot. Priya worked a three-day week (16 hours of leave) and billed 13.5 hours.

Billable ÷ available hours (contract minus leave): 13.5 / 24 = 56.3%. This is the usual definition, and it does not punish people for holidays. Billable ÷ contracted hours: 13.5 / 40 = 33.8%. Use this only if you want leave to count against capacity, for example in a firm-wide capacity report. Billable ÷ hours logged: 13.5 / 15.5 = 87%. This is a "billable ratio", not utilization. It looks great for anyone who under-reports internal time.

Whichever you choose, write it in the column header ("Billable / available") so nobody compares numbers built on different definitions. The formula below is the billable ratio for comparison.

Over 100% and capped targets

Tom contracts 32 hours and billed 33.5, so his utilization is 104.7%. That is real information (he is over capacity), so keep the raw rate in column L and flag it with something like =IF(L3>1,"Over capacity","") rather than hiding it.

Capping belongs in a score, not in the rate. If you score people against a target (75% in N2 here, an input you set), cap the score at 100% so one heavy week does not dominate a bonus or a dashboard: =MIN(1,L2/$N$2). Uncapped, Tom's score would be 140%.

For planning, the hours still needed are more useful than a percentage: =MAX(0,$N$2*J2-K2*24) gives the billable hours short of target, and MAX stops people above target showing a negative number.

Team utilization: divide the totals, do not average the rates

Averaging the percentages in column L gives every person equal weight, whether they are available 24 hours or 40. AVERAGE(L2:L4) skips Sam's "On leave" text and returns 75.7%. If his row held 0 instead, the average would drop to 56.8%, which is why the guard returns text.

The correct team figure divides total billable hours by total available hours, the same way a weighted average works. It is 73.5 / 96.

Which method to use

Logged as h:mm: SUMIFS the time, then multiply by 24 before dividing by available hours. Logged as decimal hours (6.5 instead of 6:30): the same formulas without the *24. Monthly or quarterly: add date conditions to the SUMIFS, such as $A$2:$A$14,">="&DATE(2026,11,1), and subtract holidays from available hours for that period. Many people or periods: a PivotTable with Person in rows, Type in columns and Sum of Time in values gives billable and internal totals in one grid, but you still need the available hours next to it to get the rate.