How to Calculate Utilization Rate in Excel

Utilization is billable hours divided by capacity hours. The formula is easy; the denominator is where every firm goes wrong, and the person it gets most wrong is the one who logged nothing at all.

The formula is one division. Everything that goes wrong with utilization goes wrong in the denominator, so this covers the formula quickly and the denominator properly.

Utilization = billable hours / capacity hours

Set the export up as a table

Export your timesheets to a single sheet — one row per person, project and day. Harvest, Toggl and Clockify all produce this shape. You want at least:

• Date, Person, Project, Hours
• A billable flag, usually a column reading Billable or Non-billable

Select any cell in it and press Ctrl + T to make it a Table, then rename it Time in the Table Design tab. This is not cosmetic: a Table grows when you paste next month in, and every formula below keeps working. Ranges do not, and that is how a working utilization report quietly stops including the newest rows.

The numerator

With a list of people down column A, billable hours per person is:

=SUMIFS(Time[Hours],Time[Billable],"Billable",Time[Person],$A2)

Match the flag to whatever your export actually writes. Harvest exports the words Yes and No in a Billable? column; Clockify writes Billable and Non-Billable. Get this wrong and the formula returns zero rather than an error, which is the worst kind of wrong because it looks like an answer.

Total logged hours, for comparison, drops the flag:

=SUMIFS(Time[Hours],Time[Person],$A2)

The denominator, which is the actual work

Capacity is the hours the person was available. It is not in the timesheet, and there is no formula that recovers it, so you maintain a small table beside the export:

Person        Contracted   Holiday   Leave    Capacity
A. Okafor        160          16        0        144
B. Lindqvist     160          16        8        136
C. Marsh          96          10        0         86

Three ways to fill the Capacity column, in rising order of honesty:

1. Flat 40 hours a week. One number for everyone. Comparable, simple, and understates utilization for anyone who took leave.
2. Contracted hours minus public holidays. One number per contract type. Good enough for most firms and worth the ten minutes.
3. Contracted minus holidays and per-person leave. Correct. Needs your leave records, which are usually in a different system, and is the only version that treats part-timers fairly.

Then utilization per person is a lookup and a division:

=IFERROR(SUMIFS(Time[Hours],Time[Billable],"Billable",Time[Person],$A2)
        /XLOOKUP($A2,Cap[Person],Cap[Capacity]),"")

Format the column as a percentage with one decimal. On older Excel without XLOOKUP, VLOOKUP($A2,Cap,4,FALSE) does the same job.

The trap: the person who logged nothing

This is the one worth understanding, because it survives every rebuild of the spreadsheet.

If your denominator is logged hours, then someone who recorded only the six hours they billed and nothing else shows as 100% utilized. Someone who recorded nothing at all does not appear in the export, so they have no row, and your average is computed over the people who filled in their timesheets.

Both errors push the same direction: the number goes up as timesheet discipline goes down. That is precisely backwards, and it is why a firm can watch utilization climb for two quarters while the pipeline is emptying.

Dividing by capacity fixes both, because the capacity table has one row per person whether or not they logged anything. Build the report from the capacity list and pull hours onto it, not the other way round.

A monthly series, not one number

Add a column to the timesheet table:

=EOMONTH([@Date],0)

Now add the month as another SUMIFS condition, with months across the top and people down the side:

=IFERROR(SUMIFS(Time[Hours],Time[Billable],"Billable",Time[Person],$A3,Time[Month],B$2)
        /XLOOKUP($A3,Cap[Person],Cap[Capacity]),"")

Strictly, capacity should also vary by month — August and December are shorter almost everywhere. If you have the leave data, make the capacity table one row per person per month and use SUMIFS on it as well.

A monthly series is what a partner actually wants: one figure says where you are, twelve say which way you are going. If the report needs to reach people every month, the automatic emailing walkthrough covers sending it without remembering to.

Or use a PivotTable

For the numerator, a PivotTable is faster than any formula: rows Person, columns Month, values Sum of Hours, and the billable flag on Filters set to Billable. What a PivotTable will not do is divide by a capacity that lives outside the source data, so the usual arrangement is a pivot for billable hours and one formula column beside it for the division. Details in the PivotTable guide.

Asking for it instead

All of the above is a monthly rebuild. If you would rather ask, Quiriz reads the same timesheet export and answers in plain English — in Excel, =QUIRIZ.ASK("billable hours by person by month", "table") spills the grid straight into the sheet.

The same limit applies to us as to your workbook, and we would rather say it here than have you find out later: capacity is not in the timesheet, so true utilization is not something we can compute from that file alone. Billable ratio by person, by role, by month, we do. Utilization needs the capacity source, and if you have one, it becomes another dataset to upload rather than a formula to maintain.

Frequently asked questions