Resource utilization sheet: hours, capacity, and checks

A resource utilization sheet compares used hours with available hours for the same team and period. Build it from a staffing export and approved time records, with client and internal work shown separately. The useful output is a reconciled hours comparison that helps an operations lead decide where another assignment can fit.

Set the sheet's boundary first

For this layout, available hours mean scheduled working hours less approved absences. Used hours include client delivery and internal work; absence hours never count as work. Write those definitions above the table so another manager can reproduce the result.

This is a staff-hours workbook. The glossary's utilization entry describes attendance against seats sold in hospitality and sports, which uses different records and a different denominator.

Keep plans separate from actual time. The resource allocation and utilization comparison explains how assignment commitments relate to recorded use; this sheet concentrates on the completed hours and capacity records.

Build the workbook layout

Use a Time tab for the approved time export. Keep its fields in this order:

Preserve the original detail outside the summary. If the export includes approval status, select approved records before populating Time and retain the export as evidence of that selection. A missing submission is unresolved information, not proof that someone did no work.

On a Weekly tab, use one row per team and week. Populate scheduled and unavailable hours from the staffing export, aggregated to that same team and week.

Use a controlled team list in both tabs. Google Sheets supports dropdowns that reject values outside the list, which can prevent a spelling variation from becoming a separate team. Apply the same approach to the work category and keep multiple selections disabled for these fields.

Fill the hours with matching criteria

In Weekly!F2, sum client hours for the row's team and reporting date:

=SUMIFS(Time!$D$2:$D$10000,Time!$B$2:$B$10000,$A2,Time!$A$2:$A$10000,$B2,Time!$C$2:$C$10000,"Client")

In Weekly!G2, use the same criteria with the internal category:

=SUMIFS(Time!$D$2:$D$10000,Time!$B$2:$B$10000,$A2,Time!$A$2:$A$10000,$B2,Time!$C$2:$C$10000,"Internal")

Google's SUMIFS documentation describes summing a range against multiple criteria. Here, all criteria must match: team, date, and category. Extend every range together if the export exceeds the included rows, and make sure the date fields contain matching date values rather than a mixture of dates and text.

Covirage is not affiliated with Google.

Worked example: a US consulting firm

For the week ending October 23, 2026, a US consulting firm has the following approved time totals. These are the grouped results of the Time records, before they are combined with capacity:

The staffing export supplies the scheduled and unavailable hours below. The remaining columns follow the layout formulas; percentages are rounded to one decimal place, while hours remain unrounded.

For Design, available hours are 80 minus 8, or 72. Used hours are 48 plus 16, or 64; dividing 64 by 72 gives 88.9%. Its eight unused hours are the only unused hours in this summary, but the operations lead still needs to check skills and timing before assigning additional work there.

For the company total in row 5, sum each hours column and divide the total used hours by total available hours:

=SUM(H2:H4)/SUM(E2:E4)

That is 320 divided by 328, or 97.6%. The simple average of the three displayed team percentages would give smaller teams equal weight, so it is the wrong total.

Reconcile before using the result

The staffing check is 360 scheduled hours minus 32 unavailable hours equals 328 available hours. The time check is 272 client hours plus 48 internal hours equals 320 used hours. Finally, 320 used hours plus eight unused hours equals 328 available hours.

Tie the scheduled and absence totals to the staffing export, and tie the approved work total to the selected time export. Check that every source team appears in the summary: a valid time entry assigned to an omitted team will otherwise disappear from the comparison.

Keep an exception list for missing categories, unmatched teams, duplicate entry IDs, and nonpositive capacity. Review it before approving the workbook. If repeated spreadsheet maintenance becomes the obstacle, the resource utilization software guide covers the choice of approach without changing these checks.

Where it goes wrong

• Counting leave twice. Subtracting absence from capacity and adding it to used work inflates the comparison. In this layout, absence affects the capacity side only.
• Mixing periods or teams. A time record dated by submission rather than work period can land in the wrong week. Use the same reporting boundary and team assignment basis for both exports.
• Calling all work billable. Internal work consumes capacity but does not automatically qualify for billing. Preserve separate categories even when both count toward total used hours.
• Forcing exceptions to look normal. Do not replace missing submissions with zero or cap results above capacity. Both actions hide issues that need review before staffing decisions.

Compare your own workbook in Covirage

Bring the completed workbook with its capacity and approved time records: Covirage maps the columns, and its tools compute and check the hours and team comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; creating the account takes about a minute.

See capacity planning for the wider business context.