Resource Gantt Chart Excel Template: Build and Use It

Charts & pivot tables · Updated November 20, 2026

A resource Gantt chart Excel template combines a list of assignments with a date-based timeline, so you can see who is working on what and when. In Excel for Microsoft 365 on Windows, set up one row per resource-task assignment, add start and finish dates, and use conditional formatting to draw timeline bars; the layout and formulas below make a practical reusable template.

What a resource Gantt chart shows

A standard Gantt chart shows tasks across time. A resource Gantt chart adds the people, teams, equipment, or other resources assigned to those tasks. Each row represents an assignment, and the colored cells indicate the assignment’s scheduled dates. Grouping or filtering rows by resource helps reveal overlapping work and gaps in a schedule.

It is important to distinguish a schedule from a capacity plan. A Gantt bar shows that work is scheduled during a period; by itself, it does not say how many hours the work will take or whether the assigned person is available. To assess workload, include planned hours and compare them with each resource’s available hours. That extra information makes the workbook more useful than a chart that only displays dates.

Template layout: columns and rows to include

Start with a worksheet named Assignments. Use one row for each assignment rather than one row for each person. If Alex works on two different tasks in the same month, enter two rows. This keeps task dates and effort distinct and makes filtering, sorting, and workload calculations more reliable.

  • Resource: the person, team, or asset assigned.
  • Task: a short, recognizable task or deliverable name.
  • Start: the planned first day of the assignment.
  • Finish: the planned last day.
  • Hours per week: planned effort during a normal active week, if workload matters.
  • Status or owner: optional fields that help with updates and accountability.
  • Timeline: date columns to the right of the assignment details.

Put column labels in row 4 and assignment data in rows 5 onward, for example: Resource in A, Task in B, Start in C, Finish in D, and Hours per week in E. Make the assignment list an Excel Table using Insert > Table and confirm that the table has headers. Tables make it easier to extend a well-organized source list as assignments are added; keep the timeline headers and conditional formatting area clear when extending the layout.

Build the date timeline

Choose the time scale before filling the chart. Daily columns are useful for short projects or precise handoffs, but they make a long schedule very wide. Weekly columns work well for many project plans: each heading is the Monday starting that week, and the colored cell means the assignment overlaps at least one day in that week. Monthly columns are compact for long-range plans but conceal day-level timing.

For a weekly chart, enter the first Monday date in F4 and format the cell as a date. With the first week’s Monday in F4, enter this in G4 and copy it right: =F4+7. For example, if F4 is January 6, 2025, the formula returns January 13, 2025. Format later headings as dates or as a custom display such as “6 Jan” so the timeline remains readable. If the plan uses daily columns instead, increase the previous heading by one day.

Store actual Excel dates in the Start and Finish columns, not text that merely looks like a date. Select the date columns and choose Home > Number Format to apply a date format. Check that Finish is not earlier than Start, and decide whether your schedule uses calendar days or working days. A simple Gantt bar based on dates includes weekends; excluding weekends from the display or calculating workdays requires a different setup.

Create bars with conditional formatting

With weekly Monday dates in row 4 and assignment dates in columns C and D, select the timeline range for the first assignment rows, such as F5:AZ100. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use this formula, with F5 as the top-left cell of the selected range:

=AND(F$4<=$D5,F$4+6>=$C5)

Choose a fill color in the formatting options and confirm the rule. The test marks a week when its Monday is on or before the assignment’s finish and its Sunday is on or after the assignment’s start. For example, an assignment from January 8 through January 19 overlaps the weeks beginning January 6 and January 13, so both cells are filled. The dollar signs keep the header row and date columns aligned as Excel evaluates the rule across the range, while the row number changes for each assignment.

For daily columns, use a direct date-in-range test instead. If F4 contains the day shown in a timeline column, C contains the start date, and D the finish date, the conditional-formatting formula is =AND(F$4>=$C5,F$4<=$D5). Apply it to the daily timeline range. Use a consistent color for planned work and reserve other colors for meaningful distinctions, such as status, rather than decorating every task differently.

Show workload, not just dates

To flag possible over-allocation, compare scheduled effort with availability. In the example layout, column A contains resource names, C and D contain assignment dates, E contains planned hours per active week, and F4 is the Monday for the week being reviewed. Put a resource name such as Mia in A2 and use this formula in F2 to total the weekly hours assigned to that resource:

=SUMIFS($E$5:$E$100,$A$5:$A$100,$A2,$C$5:$C$100,"<="&F$4+6,$D$5:$D$100,">="&F$4)

If Mia has two overlapping assignments in that week, planned at 12 and 10 hours per week, the result is 22 hours. This calculation counts the full weekly estimate for every assignment that overlaps any part of the week. It does not prorate an assignment that begins or ends midweek, so treat it as a planning indicator, not a timesheet total. Compare the result with a separately recorded weekly capacity, such as 32 available hours, and review weeks where planned hours exceed the person’s realistic availability.

Capacity depends on context: leave, meetings, part-time schedules, and work outside the project can all reduce available hours. A single standard capacity for every person may therefore mislead. You can maintain a separate capacity table by resource and week, or add a note for exceptions. A chart is most useful when its assumptions are visible.

Make the workbook easier to use

Freeze the identifying columns and header rows so resource names and dates remain visible while scrolling. In Excel for Windows, select the cell just below the rows and to the right of the columns you want to keep visible, then choose View > Freeze Panes > Freeze Panes. For example, selecting F5 before using the command freezes rows 1–4 and columns A–E. On Excel for Mac, the Freeze Panes command is also on the View tab, though the ribbon layout may vary slightly.

Use filters to focus on a person, team, or status, and sort assignments by resource and start date. Add data validation for fields such as status if many people edit the workbook, so values remain consistent. Include a “Last updated” date and a short note explaining whether dates are tentative or committed. If several people will edit the plan at once, store the workbook in a shared Microsoft 365 location and agree who owns schedule changes.

Common problems and ways to prevent them

  • Dates stored as text: bars may not appear because Excel cannot compare text dates reliably. Convert the entries to real dates and check their number format.
  • Wrong rule range or anchor: if the top-left cell in the formula does not match the first cell in “Applies to,” bars can shift. Check the range in Conditional Formatting > Manage Rules.
  • Assignments with missing dates: incomplete rows can behave unexpectedly. Require both dates before treating a row as scheduled, or exclude incomplete rows from the rule.
  • One row per person: this makes separate assignments difficult to represent accurately. Use one row per resource-task assignment, then filter or sort by resource.
  • Overlapping bars mistaken for overload: overlap means concurrent scheduled work, not necessarily excessive hours. Add effort and capacity information before drawing that conclusion.
  • Too much detail in one sheet: a large daily chart can become hard to navigate. Use weekly columns for a broad view and a separate detailed schedule when day-level planning is necessary.

When Excel is a good fit—and when to compare alternatives

Excel is a reasonable choice for a small or medium plan when the schedule is maintained by a limited group, the logic is easy to explain, and people need a flexible report. It is also convenient when assignments and capacity data already live in spreadsheets. A template is not a project-management system, however: Excel will not automatically resolve competing assignments or provide dependable live resource leveling just because bars are visible.

Consider dedicated project or resource-planning software if the plan has many contributors, frequent changes, dependencies, approval workflows, or a need for automatic conflict alerts and shared views. Compare products based on the number of people who need access, reporting needs, integrations, and current pricing; features and costs vary by provider and plan. A hybrid approach can work too: keep detailed scheduling in a planning system and use Excel for a focused export or summary. Avoid maintaining two independently edited “official” schedules, since they can quickly disagree.

Frequently Asked Questions

Can I download a ready-made resource Gantt chart Excel template?
Templates may be available through Microsoft Excel’s template gallery or other providers, but layouts and features differ. Check that a template has one row per assignment, date-based bars, and a clear way to record effort if you need to review capacity. Inspect formulas and conditional-formatting ranges before relying on it.

How do I show two resources on one task?
Enter one row for each resource-task assignment, using the same task name and dates where appropriate. This allows the chart to show each person’s assignment separately and lets workload calculations total hours by resource.

Can Excel show resource over-allocation automatically?
Excel can calculate planned hours by resource and time period and use conditional formatting to highlight values above a capacity threshold. The result is only as accurate as the hours, availability, and date assumptions entered; a Gantt bar alone does not detect over-allocation.

Should the chart use days or weeks?
Use days when exact dates and short handoffs matter. Use weeks for a longer project where a daily chart would be too wide. Monthly columns suit high-level roadmaps, but they do not show precise starts and finishes.

Will the template work in Excel for the web or on Mac?
The basic date cells, formulas, and conditional formatting are supported in current Excel versions, although ribbon placement and some editing details can differ. The steps above target Excel for Microsoft 365 on Windows; check the Conditional Formatting rules and date display after opening a workbook in another version.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.