Resource Planning Template in Google Sheets: Stop Overbooking Your Team

It's Monday, and three project leads each think they have Priya for the week. One booked her for 20 hours, one for 10 and one for 6. That would fit a 40-hour week, except she is off on Monday and Tuesday, so 36 hours of work just landed on 24 available hours. Nobody notices until Wednesday.

A resource planning template in Google Sheets prevents that by tracking three numbers for every person: the hours they have, the hours already booked, and the gap between them. You record each person's weekly capacity, subtract their time off, log every assignment in hours, and let the sheet flag anyone booked past 100%.

This guide walks through the full setup: the columns to create, the formulas that do the math, a worked example with a five-person team, and the mistakes that make most resource plans drift out of date within a month.

What Resource Planning Actually Covers

Resource planning answers one question before the work starts: do we have enough hours, from the right people, to deliver what we have promised? It works in hours and weeks, not in individual to-dos.

Three terms do most of the work:

• Capacity is the hours a person can work in a period, after time off. A 40-hour week with one day of leave gives 32 hours of capacity.
• Allocation is the hours you have committed that person to, across every project they touch.
• Utilization is allocation divided by capacity. It shows whether someone is stretched, about right, or has room for more.

This is different from task tracking. A task tracker shows what needs doing and whether it is done; a resource plan shows whether the people doing it have the hours. That gap is why teams that only track tasks still end up overbooked. For the task side, see this guide to balancing work with a team task tracker.

The Three Numbers Behind Every Capacity Plan

Every capacity planning template, in Google Sheets or anywhere else, runs on the same three calculations. Get these right and everything else is formatting.

1. Available hours = working days in the period × hours per day − leave hours. A person with 20 working days at 8 hours and two days of leave has 160 − 16 = 144 available hours.
2. Allocated hours = the total hours booked to that person across every project in the same period.
3. Utilization % = allocated hours ÷ available hours × 100. If that person is booked for 130 hours, they are at 130 ÷ 144 = 90%.

Utilization is booked hours divided by the hours a person really has after time off.

There is no single “right” utilization rate. Float points out that the ideal depends on the team, and that planning someone at 100% is only realistic if meetings, admin and training are counted as allocations too. For client-facing firms, SPI Research treats 75% billable utilization as the target, and its latest benchmark found the average slipped to 66.4%, the lowest in the survey's history (summary via Deltek).

A practical starting band for most teams:

• Under 75%: spare capacity, worth checking before you hire or outsource.
• 75–100%: fully used, the range you want most people in.
• Over 100%: overbooked. Something will slip, or someone will burn out.

Most teams aim to keep people between 75% and 100%.

That last line is not a figure of speech. In a Gallup study of nearly 7,500 full-time employees, an unmanageable workload was one of the five main causes of burnout. A resource plan is the earliest place you can see it coming.

How to Build a Resource Planning Template in Google Sheets

You need four tabs and three formulas. The formulas below were tested on the worked example in the next section, so you can paste them as they are.

Three input tabs feed one Utilization tab.

Step 1: List your team and their weekly hours

Create a tab called Team with three columns: Name, Role and Weekly hours. Use each person's real contracted hours, so a four-day part-timer gets 32, not 40. This tab is the capacity side of the plan.

Step 2: Log every assignment in hours, by week

Create a tab called Allocations with four columns: Person, Project, Week starting and Hours. Add one row per person, per project, per week, and always use the Monday of that week. If you have any date in A2, =A2-WEEKDAY(A2,3) returns that week's Monday.

Add a dropdown to the Person column so names always match. Select the column, open Data > Data validation, and choose a dropdown from the range Team!A2:A. One misspelled name is invisible to every formula that follows.

Step 3: Record time off

Create a tab called Time Off with Person, Week starting and Hours off. A full day off for a full-timer is 8 hours, a half day is 4. If leave is booked as a date range, =NETWORKDAYS(start, end, holidays) counts the working days in it, both ends included, so multiply the result by that person's daily hours.

Step 4: Build the utilization grid

Create a tab called Utilization. Put the Monday dates across row 1 (B1, C1, D1 and so on), then build three blocks underneath with a label row between them, each listing the same names in column A:

1. Available hours (rows 2–6): =VLOOKUP($A2,Team!$A:$C,3,FALSE)-SUMIFS('Time Off'!$C:$C,'Time Off'!$A:$A,$A2,'Time Off'!$B:$B,B$1)
2. Booked hours (rows 9–13): =SUMIFS(Allocations!$D:$D,Allocations!$A:$A,$A9,Allocations!$C:$C,B$1)
3. Utilization % (rows 16–20): =IF(B2=0,IF(B9>0,"Booked on leave","On leave"),B9/B2)

Fill each formula right and down, and format the last block as a percentage. The $ signs keep each lookup on the right column while the person and week change. These row numbers fit a five-person team; for a bigger team, leave more rows per block and point the formulas at the matching rows.

Step 5: Turn the grid into a traffic light

Select the utilization block, open Format > Conditional formatting, choose Custom formula is, and add three rules:

• Red: =AND(ISNUMBER(B16),B16>1)
• Green: =AND(ISNUMBER(B16),B16>=0.75,B16<=1)
• Amber: =AND(ISNUMBER(B16),B16<0.75)

The finished utilization block with all three formatting rules applied.

That grid is now a working resource utilization tracker. Over-allocation shows up in red the moment someone books it, not at Wednesday's stand-up.

Step 6 (optional): See demand by project

If you plan across several projects, total the hours each one is drawing. =SUMIFS(Allocations!$D:$D,Allocations!$B:$B,"Mobile App") gives one project's total, and adding Allocations!$C:$C,B$1 as a second condition splits it by week. It is the quickest way to see which project is quietly eating the team.

Worked Example: Five People, Three Projects, Four Weeks

Here is the template filled in for a small agency running a Website Redesign, a Mobile App and a Client Portal at the same time. Only two inputs change from week to week: the hours booked and the time off.

The worked example before and after two moves.

The grid catches two problems a task list would miss:

• Priya, week 2 (150%). Her two days off cut her week to 24 hours, but 36 were still booked. Moving 8 of those hours into week 3 and 8 into week 4 puts her at 83%, 85% and 80%, with no deadline moved.
• Lena, week 3 (125%). She is carrying 36 hours of Client Portal plus 14 of Mobile App. Marcus, also on the Mobile App, has only 20 hours booked that week, so handing him those 14 hours puts Lena at 90% and Marcus at 85%.

Sofia's 50% in week 1 is the third signal: spare QA time that could pull some testing forward.

Five Mistakes That Make Resource Plans Drift

Most resource plans don't fail on day one. They fail slowly, because of a few habits that make the numbers less true each week. The first one is the most common:

The same “50%” shrinks to 12 hours in a short week.

1. Planning in percentages instead of hours. “50% on the Mobile App” means 20 hours in a normal week but 12 in a week with two days off. Hours survive time off; percentages quietly don't.
2. Leaving out time off and holidays. Resource availability is capacity minus leave, every single week. If leave lives in a separate HR sheet, copy it across weekly, or keep it with your employee leave tracker and pull the totals in.
3. Letting each project lead book people in their own sheet. Resource conflicts happen because nobody sees the total for one person. Keep every project's allocations in one tab, even if each lead only edits their own rows.
4. Planning everyone to 100% of contracted hours. Meetings, email and admin take real time. Either book them as their own “Internal” project, or treat 85–90% as full for that person.
5. Building the plan once and never touching it. A plan from three weeks ago is a guess. Review the next four weeks every Friday, move what slipped, and the grid stays honest.

When a Spreadsheet Stops Being Enough

A spreadsheet is a good home for resource planning while the team is small enough to fit on one screen. Everyone already has access, there is no per-seat fee, and you can change any formula the day your process changes.

It starts to strain when:

• you need to schedule by the hour or the day, not by the week;
• allocations have to sync on their own from a time tracker or project tool;
• several managers need approvals, permissions or an audit trail on who changed a booking;
• the team grows past a few dozen people, or projects start and stop every week.

A quick test for when to move beyond a spreadsheet.

If two or more of those sound familiar, dedicated resource management software is worth the cost. If not, a well-built sheet will usually serve you better than a tool your team won't open.

If You'd Rather Not Build It Yourself

Everything above works, and the sheet is yours to keep adjusting. If you would rather start from a finished version, the Resource Allocation Planner from Acesheets is the same approach built out in full, for Google Sheets or Excel.

It plans from real start and end dates instead of weekly buckets and flags any booking that falls outside its project's dates. Leave types can allow part of a day's work, so a part-time day only removes the hours that person can't work. Everyone is sorted into Over Utilized, Optimized (75–100%) or Under Utilized automatically, a Conflict Log lists overlapping bookings with room for resolution notes, and the dashboard filters by person, project, role and date range. It holds up to 25 team members and 20 projects.

The Resource Allocation Planner dashboard in Google Sheets.

Frequently Asked Questions

What is a good resource utilization rate?

It depends on what you count. SPI Research uses 75% billable utilization as the target for professional services firms. If your allocations include every kind of work, meetings and admin too, planning people close to 100% can be realistic. Most teams treat 75–100% as healthy and anything above 100% as overbooked.

What is the difference between resource planning and resource allocation?

Resource planning decides, ahead of time, how many hours of which skills each project needs. Resource allocation assigns named people to those hours. The plan says “we need 70 developer hours in week 3”; the allocation says “Lena takes 36 and Marcus takes 34.”

How do you calculate team capacity?

Add up each person's available hours for the period: working days × hours per day, minus leave. Five full-timers over a 20-working-day month with no leave have 5 × 20 × 8 = 800 hours of capacity.

Can I do resource planning in Excel instead?

Yes. SUMIFS, VLOOKUP, NETWORKDAYS and WEEKDAY work the same way in Excel, and the conditional formatting rules carry over as written. Google Sheets is the easier choice when several managers edit the same plan at once.

What is resource leveling?

Resource leveling adjusts the schedule so nobody is booked beyond capacity, even if that pushes an end date back. Resource smoothing does the same balancing but keeps the original deadlines (ProjectManager explains both). Moving Priya's hours into weeks 3 and 4 in the example above, with no deadline changed, is smoothing.