Introduction
A capacity planning template in Excel is a workbook that lists every team member, their available hours after PTO and non-project time, and their assigned project hours, then calculates utilization automatically. The core columns are Person, Role, Week Start, Contracted Hours, PTO Hours, Non-Project Hours, Available Hours, Assigned Project Hours, and Utilization %. This structure lets you compare your team’s capacity against project demand in a single view.
This guide is for operations managers, agency owners and project managers who plan team workload in Excel today and want a reliable, formula-driven capacity model they can build quickly. Think of it as a simple, free resource planning template focused on hours: it shows who has room, who is overbooked and what the plan looks like before the week starts.
By the end you will be able to:
- Build a working three-sheet capacity planning template in about 10 minutes
- Use the key formulas: available hours, SUMIFS for assigned hours, and utilization rate
- Set up a Team roster sheet and a Demand sheet that feed a Summary sheet
- Run what-if scenarios for hiring, new projects and schedule changes
- Recognize when spreadsheets stop working and when to consider dedicated software
What a Capacity Planning Template in Excel Does
The template compares team capacity (the hours each person actually has available) with project demand (the hours needed across all active and upcoming work), week by week. When available capacity and allocated hours sit side by side, resource conflicts and staffing gaps become visible before they affect delivery.
A capacity planning template is a type of resource planning template: it records resource availability, resource allocation and the resulting resource utilization in one workbook. The focus here is people capacity for agencies, professional services firms and project teams, not machine or infrastructure capacity. Whether you manage client work, product sprints or internal projects, the structure is the same.
Capacity, Demand and Utilization
Capacity is the realistic working hours per person after subtracting PTO, public holidays and non-project commitments from a standard week. A 40-hour contracted week does not mean 40 hours of project work: team meetings, administrative tasks and training all reduce net capacity. Effective capacity reflects actual availability after time off, meetings and other non-project responsibilities.
Demand is the forecast or committed project hours for each person or role in a given planning period. Separating committed demand from tentative future demand clarifies resource planning and helps prevent overcommitting available team members.
Utilization % is assigned project hours divided by available hours, so a person with 32 available hours and 24 assigned hours is at 75%. It shows at a glance whether someone is under-allocated, running at a healthy level, or overbooked.
Using net available hours rather than a flat 40 prevents a common planning error. If you treat every person as having 40 hours of project capacity, you will overcommit the team every week. Good capacity management starts with accurate capacity planning, which means accounting for the hours lost to non-project responsibilities.
The stakes go beyond scheduling. In a Gallup study of nearly 7,500 full-time employees, 23% said they felt burned out at work very often or always, and employees who often or always had enough time to do all of their work were 70% less likely to experience high burnout (Gallup, 2018). A capacity plan is one of the simplest ways to check, before the week starts, whether the work you promised fits the hours your team really has.
Why an Excel Template Is Still a Good Starting Point
Excel or Google Sheets is cheap, flexible and already on most computers. For a small team with a manageable number of projects and one or two people keeping the file current, a well-structured Excel template is often enough. It tracks net capacity, committed demand and staffing gaps without software cost or a learning curve, and it sits comfortably alongside your existing project planning.
This guide shows how to build that capacity template and is straightforward about where Excel stops working.
How to Set Up Your Capacity Planning Template
The workbook has three sheets: Team (capacity), Demand (workload) and Summary (capacity vs demand). The Team sheet defines resource availability, the Demand sheet captures planned effort across projects, and the Summary sheet brings them together with utilization calculations and conditional formatting. Below are the exact columns, sample data and formulas.
Sheet 1: Team Roster and Availability
This sheet holds one row per person per planning week. It is where you enter contracted hours, subtract PTO and non-project time, and calculate available hours.
| Person | Role | Week Start | Contracted Hours | PTO Hours | Non-Project Hours | Available Hours |
|---|---|---|---|---|---|---|
| Alex Lee | Account Manager | 2026-11-02 | 40 | 0 | 8 | 32 |
| Jamie Ruiz | Designer | 2026-11-02 | 40 | 8 | 8 | 24 |
| Sam Chen | Developer | 2026-11-02 | 40 | 0 | 8 | 32 |
| Morgan Pratt | Analyst | 2026-11-02 | 40 | 0 | 10 | 30 |
Person is the team member’s name. Role groups people for roll-up reporting and hiring decisions. If your team has different skill sets, add a Skills column so you can filter by skill later. Week Start is the Monday of the planning week, here 2026-11-02. Contracted Hours is the standard working hours for the week (40 for full-time, or 24 or another number for part-time staff). PTO Hours covers annual leave, sick leave or public holidays that week. Non-Project Hours covers team meetings, admin, training and anything else that uses working hours but is not project work.
Available Hours is the formula-driven column. With Contracted Hours in column D, PTO in column E and Non-Project Hours in column F, place this in column G:
=MAX(0, D2 - E2 - F2)
MAX(0, …) stops availability going negative if someone has more PTO than contracted hours that week. Copy the formula down for every row. If your columns are in a different order, adjust the cell references to match your own layout.
Sheet 2: Project and Task Demand
This sheet holds all expected work (projects, workstreams or client deliverables) with planned hours per person per week. Estimate the resources needed as hours by role, and keep entries at the project or workstream level, not individual tasks, so the sheet stays maintainable. When new tasks arrive, add them as rows with a status of Tentative until they are confirmed.
| Project | Client | Task / Workstream | Person | Role | Week Start | Planned Hours | Status |
|---|---|---|---|---|---|---|---|
| Website Redesign Q4 | Acme Corp | Discovery | Alex Lee | Account Manager | 2026-11-02 | 15 | Committed |
| Website Redesign Q4 | Acme Corp | UI Design | Jamie Ruiz | Designer | 2026-11-02 | 20 | Committed |
| Website Redesign Q4 | Acme Corp | Front-End Build | Sam Chen | Developer | 2026-11-02 | 25 | Committed |
| Brand Refresh | Beta Inc | Visual Identity | Jamie Ruiz | Designer | 2026-11-02 | 6 | Tentative |
| Retainer | Acme Corp | Monthly Reporting | Morgan Pratt | Analyst | 2026-11-02 | 18 | Committed |
| Internal Tooling | n/a | Dashboard Update | Sam Chen | Developer | 2026-11-02 | 5 | Tentative |
| Retainer | Acme Corp | Account Management | Alex Lee | Account Manager | 2026-11-02 | 20 | Committed |
The Status column matters. Committed demand is work that is contracted or confirmed. Tentative rows are upcoming or pipeline work that may or may not land. Tracking committed demand separately from tentative demand lets you plan conservatively while still seeing future resource requirements.
Using SUMIFS to Total Assigned Hours
On the Summary sheet you need total assigned hours per person per week. In the Summary sheet below, Person is in column A and Week Start is in column C. With the Demand sheet named Demand (Person in column D, Week Start in column F, Planned Hours in column G, Status in column H), use:
=SUMIFS(Demand!$G:$G, Demand!$D:$D, $A2, Demand!$F:$F, $C2, Demand!$H:$H, "Committed")
The first criterion matches the person, the second matches the week start date, and the third counts only Committed work. Delete the third criterion to include tentative work as well. This gives you a clean split between committed demand and tentative future demand.
Sheet 3: Capacity vs Demand Summary
This is the control panel where availability and demand meet. It is the sheet a manager reviews weekly to spot resource conflicts and rebalance the workload. The table below counts Committed work only.
| Person | Role | Week Start | Available Hours | Assigned Project Hours | Utilization % | Status |
|---|---|---|---|---|---|---|
| Alex Lee | Account Manager | 2026-11-02 | 32 | 35 | 109.4% | Over |
| Jamie Ruiz | Designer | 2026-11-02 | 24 | 20 | 83.3% | Healthy |
| Sam Chen | Developer | 2026-11-02 | 32 | 25 | 78.1% | Healthy |
| Morgan Pratt | Analyst | 2026-11-02 | 30 | 18 | 60.0% | Under |
Available Hours comes from the Team sheet. The simplest approach is a helper key column on the Team sheet (for example, in column H: =A2&”|”&C2 and a lookup against it. In Excel 2021, Excel 2024 or Microsoft 365, use XLOOKUP:
=XLOOKUP($A2&"|"&$C2, Team!$H:$H, Team!$G:$G, 0)
In Excel 2016, Excel 2019 or any earlier version, use INDEX/MATCH instead. Microsoft documents that XLOOKUP is not available in Excel 2016 and Excel 2019:
=INDEX(Team!$G:$G, MATCH($A2&"|"&$C2, Team!$H:$H, 0))
The downloadable template uses the INDEX/MATCH version, so it works in every version of Excel.
Assigned Project Hours reuses the SUMIFS formula from the previous section. Both columns are formula-driven, never typed by hand, so the plan updates whenever the underlying sheets change.
Key Formulas and Calculations for Utilization
Calculating and Visualizing Utilization
Utilization % shows how much of a person’s available hours are already booked with project work. It is the core resource utilization metric in the workbook. With Available Hours in column D and Assigned Project Hours in column E on the Summary sheet, place this in the Utilization % column (column F):
=IF(D2=0, 0, E2 / D2)
Format the column as Percentage with one decimal place. The IF wrapper prevents a divide-by-zero error when someone has no availability that week.
Utilization bands are guidelines for reading the numbers:
- Under 70%: low utilization; the person has slack or may need more project work
- 70–85%: healthy range; productive, with buffer for unplanned work
- Over 85%: at risk of overload, especially if sustained across several weeks
- Over 100%: overbooked; more hours allocated than available
Conditional formatting makes these bands visible at a glance:
- Select the Utilization % column
- Go to Home → Conditional Formatting → New Rule
- Rule 1: Format cells where the value is greater than 1 → red fill (overbooked)
- Rule 2: Format cells where the value is between 0.85 and 1 → amber fill (at risk)
- Rule 3: Format cells where the value is less than 0.7 → light blue or neutral fill (underutilized)
You can then scan a whole team in seconds instead of reading every number. For the full math and edge cases, see the guide on how to calculate utilization rate.
Worked Example: One-Week Capacity Plan for a Small Agency
This section walks through a complete example for a 4-person creative agency for the week starting 2026-11-02. The numbers come from the tables above.
Team Roster and Available Hours (Example)
| Person | Role | Contracted | PTO | Non-Project | Available |
|---|---|---|---|---|---|
| Alex Lee | Account Manager | 40 | 0 | 8 | 32 |
| Jamie Ruiz | Designer | 40 | 8 | 8 | 24 |
| Sam Chen | Developer | 40 | 0 | 8 | 32 |
| Morgan Pratt | Analyst | 40 | 0 | 10 | 30 |
Jamie’s PTO cuts available hours to 24. Morgan’s higher non-project hours (more internal reporting) reduce availability to 30. Before loading demand, an operations manager would sanity-check these numbers: do the non-project estimates match the time actually spent in recent weeks?
Project Demand (Example)
| Project | Person | Planned Hours | Status |
|---|---|---|---|
| Website Redesign Q4 – Discovery | Alex Lee | 15 | Committed |
| Retainer – Account Management | Alex Lee | 20 | Committed |
| Website Redesign Q4 – UI Design | Jamie Ruiz | 20 | Committed |
| Brand Refresh – Visual Identity | Jamie Ruiz | 6 | Tentative |
| Website Redesign Q4 – Front-End Build | Sam Chen | 25 | Committed |
| Internal Tooling – Dashboard Update | Sam Chen | 5 | Tentative |
| Retainer – Monthly Reporting | Morgan Pratt | 18 | Committed |
The Website Redesign takes most of the designer and developer time. Two rows are marked Tentative, so they can wait if committed work already fills available resources.
Reading the Summary and Rebalancing Work
With committed work only, Alex is at 109.4% utilization: 35 hours allocated against 32 available. Morgan sits at 60%, well under the healthy band. Jamie is at 83.3%, but would reach 108% if the tentative Brand Refresh work is confirmed. Sam is at 78.1%, and the 5 tentative hours would take him to about 94%.
Practical responses:
- Move hours from Alex to Morgan. If Morgan can take some of the reporting work, shifting 4–6 hours brings Alex under 100% and lifts Morgan to roughly 73–80%.
- Hold the tentative Brand Refresh row. Jamie already has limited capacity because of PTO, so start that work the following week instead of overcommitting.
- Check the Internal Tooling hours. They fit within Sam’s capacity but leave little buffer, so treat them as flexible.
The value of the template is seeing these imbalances before the week starts, not after deadlines slip.
Using the Template for Different Planning Horizons and Industries
Weekly Planning for Agencies and Consulting Firms
Use Week Start as the period key and review the Summary sheet every Monday. To separate billable from non-billable work, add a Billable? column (Yes / No) to the Demand sheet. You can then sum billable project hours separately and compare them against revenue targets, not just allocation. Agencies on retainers can track each retainer as a recurring demand row, which shows how much capacity client work uses compared with internal or new-business activity.
Sprint Capacity Planning for Product and Engineering Teams
Replace Week Start with Sprint Start or a sprint number. Enter ceremonies (standups, retrospectives, demos) and on-call time in the Non-Project Hours column so they reduce available capacity realistically. Even if story points live in Jira, converting planned effort into hours per person per sprint keeps the template comparable across roles. Export backlog estimates from Jira or Trello and roll the total hours into the Demand sheet per person and sprint.
Role-Level Capacity for Hiring Decisions
Roll the Summary up by role with a SUMIF formula or a pivot table: sum Available Hours and Assigned Project Hours by Role across several weeks. If a role, such as Senior Designer, stays above 85% utilization for several weeks in a row, that is a signal to look at hiring or contracting. This answers a question like “Do we need another senior designer next quarter?” with data instead of guesswork, and it is the foundation of simple resource management for a growing team.
Taking Your Template Further: Dashboards, What-If Scenarios and Method
Create an Executive Dashboard with Pivot Tables
Once the Summary sheet holds several weeks of data, a pivot table turns it into a capacity report:
- Click anywhere in the Summary data range → Insert → PivotTable
- Drag Role to Rows, Week Start to Columns, and Assigned Project Hours to Values (sum)
- Add Available Hours as a second value (sum) so you can compare the two side by side
- Insert a stacked column chart to see hours allocated by role each week
- Insert a line chart of total assigned hours against total available hours over time
Calculate role-level utilization as total assigned hours divided by total available hours rather than averaging individual percentages, which can mislead when people have different available hours. Keep the charts on a dedicated Dashboard tab so leadership can open one sheet and get actionable insights at a glance.
Run What-If Scenarios
Because the structure is formula-driven, scenario planning is straightforward:
- Hiring: Add a hypothetical person to the Team sheet with an expected start date and contracted hours. The Summary sheet shows how available capacity changes and whether current overloads ease.
- New projects: Add tentative rows to the Demand sheet, then filter by Status to see the impact on utilization before committing resources.
- Changed assumptions: Increase Non-Project Hours (for example, a week with extra meetings or training) and watch how utilization shifts across the team.
For more structured scenarios, duplicate the workbook tab and label each copy, such as “Scenario – Hire in December” or “Scenario – Lose Client X”. Excel’s Scenario Manager can also store multiple sets of assumptions, though most teams find separate tabs simpler to review and share.
Methodology Note: Making the Numbers Trustworthy
A 40-hour week is a baseline only. Each person’s real availability changes week to week with PTO, public holidays and non-project commitments. The template only works if these inputs are kept honest: overestimating available hours is the fastest way to produce a plan that looks healthy but fails on delivery.
Keep all estimates in one unit, hours. Mixing hours with story points or abstract effort scores across sheets creates confusion. The utilization bands (70–85% healthy, above 85% at risk) are rules of thumb, not fixed standards; some roles, such as senior leads who mentor or managers who run intake, need more buffer than others. The template supports planning decisions. It is not a timesheet, and its numbers are only as good as the estimates that go into it.
Protect the formulas, too. Spreadsheet research is not reassuring on this point: Raymond Panko’s review of field audits of real business spreadsheets found that the most recent audits turned up errors in at least 86% of the spreadsheets audited, and most audits counted only substantive errors (Panko, 2000). The same paper notes that typing a number over a formula cell is a common error that cell protection prevents easily. In Excel, unlock the input cells (Format Cells → Protection → clear “Locked”), then choose Review → Protect Sheet so only the input cells can be edited.
Where Excel Capacity Planning Stops Working
Version Control and Collaboration
When several people edit the same workbook, formulas get overwritten, rows are inserted in the wrong place, and no one is sure which version is current. Shared workbooks and Excel for the web help, but auditing who changed what is still difficult. Every additional editor adds risk to the formulas.
Keeping Data Current
Scope changes, shifting deadlines, new hires and unexpected PTO make manual updates slow. If the file lags behind reality, resource management decisions are made on stale data. A capacity plan helps you spot staffing gaps before they affect delivery, but only while it reflects current conditions.
No Actuals: Planned vs Delivered Work
The template shows planned time, not what actually happened. Utilization based purely on estimates can look fine even when the team consistently runs over plan. Without actual time spent feeding back into the model, estimates stay guesses. That gap is what the next section addresses.
Comparing Planned vs Actual Hours with TimeTackle
TimeTackle is automatic time tracking software that turns Google Calendar and Outlook events into tracked time, so actual hours do not depend on manual timesheets. Calendar events such as client calls, design sessions and development blocks are categorized with custom tags.
Reports in TimeTackle show actual time by client, project, tag and team. You can export the data to Excel or CSV and paste it into the optional Actual Hours column on the Summary sheet, next to planned hours. Side by side, the two columns show where estimates consistently miss: perhaps non-project hours are higher than assumed, or one type of project regularly runs past its plan.
Over time, comparing planned time with actual time spent lets you refine the Non-Project Hours assumption on the Team sheet and adjust future demand estimates based on observed patterns instead of hope.
For readers weighing other tools beyond Excel, the overview of resource capacity planning tools covers what to look for and when a switch makes sense.
Download the Excel Capacity Planning Template
The downloadable file has a short Start here tab and the three working sheets. Team holds the availability formula (=MAX(0, Contracted – PTO – Non-Project)). Demand holds sample projects and a Status dropdown for committed vs tentative work. Summary pulls available hours with INDEX/MATCH, totals assigned hours with SUMIFS, and calculates utilization, a status label, utilization including tentative work, and an optional Actual Hours comparison, with the conditional formatting bands already applied. Input cells are blue and formulas are black. The sample data is the 4-person agency from the worked example. The file contains no macros.
Common Questions About Capacity Planning in Excel
What should a capacity planning template include?
A capacity planning template should connect availability, roles and project demand in one place. At minimum it needs a roster with available hours (after PTO and non-project time), a demand sheet with planned hours and status, a capacity vs demand summary with utilization calculations, and clear utilization bands with conditional formatting. Separating committed from tentative demand and adding role-level roll-ups round out the template.
How is utilization calculated in a capacity plan?
Utilization equals assigned project hours divided by available hours. If someone has 32 available hours and 24 hours allocated to project work, their utilization is 24 ÷ 32 = 75%.
How often should I update my capacity planning template?
Weekly works for most teams. Update sooner whenever something material changes: a new project lands, a deadline slips, a team member takes unexpected PTO, or the team changes. For longer-horizon planning, a monthly or quarterly review is appropriate, but the week-by-week view should stay current.
Can I use this template for long-term planning?
Yes. Switch Week Start to a month or quarter and plan by role rather than by individual. Use approximate hours and focus on future resource requirements at the role or department level. Avoid false precision: a long-range view should answer “do we need more developers in Q2?”, not “what will Sam do on March 14th?”
Is Excel enough, or do I need dedicated capacity planning software?
Excel is usually enough for a small team, a manageable number of projects and one or two owners keeping the file current. Dedicated software becomes the right tool when several people edit conflicting versions, assignments change daily and are hard to track, or leaders need live utilization views.
How do I handle part-time staff?
Enter their real contracted hours, for example 24 instead of 40. The PTO and Non-Project Hours formulas work the same way, and utilization percentages stay comparable across full-time and part-time staff.
How does this work with Jira or Trello?
Keep your task list and specific tasks in Jira or Trello and roll estimated hours per person or sprint into the Demand sheet. Many teams export or summarize backlog estimates periodically and add the total expected hours to the template. The workbook handles the capacity math; the project management tool handles day-to-day workflow.
Conclusion and Next Steps
A capacity planning template in Excel is three connected sheets, a handful of formulas and clear utilization bands. It gives project managers and resource planners a view of who is available, where demand exceeds capacity, and what needs to change before deadlines are at risk.
- Set up your Team sheet and check that available hours reflect real PTO and non-project time, not a flat 40
- Fill in the Demand sheet with the next 4–8 weeks of committed and tentative project work
- Review the Summary sheet weekly to catch resource conflicts and rebalance allocations early
- Start comparing planned vs actual hours using calendar data to improve your estimates over time
Once the spreadsheet becomes hard to maintain, with too many editors, stale data and no actuals, look at tools that connect resource planning with real calendar and time data.