To make a resource histogram in Excel, organize allocated work by time period and resource, summarize the totals in a grid, then select that grid and insert a clustered column chart. The result shows how demand changes over time and which resources are busiest. In project planning, a resource histogram is a time-based allocation chart—not the statistical Histogram chart used to group numeric data into bins.
The steps below use weekly allocation hours as an example. You can substitute people, machines, rooms, or another resource, and use days or months instead of weeks. The chart is only as reliable as the allocation data: it shows the values you record, not automatically calculated availability or actual work completed.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The main table, formula, and column-chart steps are also available in recent Excel for Mac and Excel for the web, though some chart formatting and PivotChart controls differ.
- A list of allocations that identifies a time period, a resource, and an amount in a consistent unit, such as hours per week.
- A clear decision about what the chart should measure: planned hours, people assigned, machine hours, or another comparable quantity.
Step-by-Step
- Decide what each bar will represent. Choose the time interval and measurement before entering data. For example, if you are tracking planned staff effort, use hours per week. Do not mix hours and headcount in the same measure. Decide whether you want to compare resources separately or emphasize total demand in each period; this choice affects whether a clustered or stacked column chart is most useful.
- Set up the allocation data. Create three columns with headers: Period, Resource, and Allocated Hours. Enter one row for each resource allocation in each period. For this example, enter these rows below the headers: Week 1, Designer, 24; Week 1, Developer, 32; Week 1, Tester, 16; Week 2, Designer, 30; Week 2, Developer, 24; Week 2, Tester, 20; Week 3, Designer, 18; Week 3, Developer, 36; and Week 3, Tester, 24. Keep the period and resource names spelled consistently. If you have several tasks for the same resource in the same week, use separate rows; the summary will add them together.
- Format the source as an Excel table. Click inside the data, choose Insert > Table, confirm the range includes all three headers, and make sure My table has headers is selected. A table is easier to filter and expand when you add allocations. Give it a useful name from the Table Design tab, such as Allocations, if you want to identify it easily. The example formula below uses ordinary cell ranges, so you do not have to name the table.
- Create a summary grid. In a clear area of the worksheet, enter Period in E1, then Designer, Developer, and Tester in F1:H1. Enter Week 1, Week 2, and Week 3 in E2:E4. Each row will represent a period, and each resource will have its own column. This layout makes a chart series for each resource and puts the periods in time order along the horizontal axis. If you use real dates, enter actual Excel dates rather than text that merely looks like dates, and use the same date values in both the source and summary.
- Calculate each period-and-resource total. With period names in A2:A10, resource names in B2:B10, allocated hours in C2:C10, summary periods in E2:E4, and resource headings in F1:H1, enter this in F2:
=SUMIFS($C$2:$C$10,$A$2:$A$10,$E2,$B$2:$B$10,F$1). For the example, F2 returns 24 because Week 1 has 24 Designer hours. SUMIFS adds the hours only when both the period and resource match. The dollar signs keep the source ranges fixed when you copy the formula, while the row and column references adjust for the summary grid. - Fill the summary grid and check the figures. Copy F2 across to H2, then copy F2:H2 down through row 4. The completed rows should be Week 1: 24, 32, 16; Week 2: 30, 24, 20; Week 3: 18, 36, 24. The period totals are 72, 74, and 78 hours respectively. These checks help catch misspelled labels, missing rows, or accidentally shifted references before you build the chart. If you add more source rows, update the formula ranges or use a table-based summary approach so the new data is included.
- Insert the chart. Select E1:H4, including the period labels and resource headings. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column. Excel creates side-by-side columns for each resource in each week. This is usually the clearest option when you want to compare resource workloads. If Excel puts resources on the horizontal axis instead of periods, select the chart and choose Chart Design > Switch Row/Column.
- Add labels that explain the chart. Select the chart, then choose Chart Design > Add Chart Element > Chart Title and enter a descriptive title, such as Weekly Planned Hours by Resource. Add axis titles using Chart Design > Add Chart Element > Axis Titles. Label the horizontal axis Week and the vertical axis Allocated hours. Keep the legend visible so readers can tell which color represents each resource. A useful title and unit label prevent viewers from mistaking hours for people or percentages.
- Choose clustered or stacked columns deliberately. Clustered columns make it easier to compare the same resource between weeks and to compare resources in a week. To show the combined workload per week as one total column divided into resource segments, select the chart and choose Chart Design > Change Chart Type > Column > Stacked Column. Stacking emphasizes the overall total but makes it harder to compare individual resources across periods. Avoid a 100% stacked chart unless you want to show proportions rather than the number of hours.
- Refresh the display when the plan changes. If you add allocation rows, check that the summary formulas cover them and that the chart data range includes all summary rows and resource columns. Excel tables can expand as you type below them, but a regular formula range such as C2:C10 does not automatically include row 11. After updating the source, verify a changed total in the summary before relying on the chart. In Excel for Mac, the core Insert and chart-type choices are similar, though ribbon labels or chart-formatting panels can differ slightly. Excel for the web supports basic charts, but some detailed formatting and PivotChart options may be limited compared with the Windows desktop app.
Common Mistakes
- Using the statistical Histogram chart. That chart groups numeric observations into bins; it does not normally show resource demand across weeks. For time-phased resource allocations, use a column chart with periods on the horizontal axis.
- Mixing units or meanings. A column of 40 hours cannot be directly compared with a column of 3 people. Pick one measure and use it throughout. If a resource is measured in people, decide whether the value means people assigned, full-time equivalents, or something else.
- Entering inconsistent labels. Week 1, Wk 1, and Week 1 with an extra space may be treated as different categories. Use consistent labels, or use actual dates consistently in both tables.
- Charting the source rows instead of the summary. Selecting the raw list can create many repeated categories and misleading bars. Select the compact summary grid, including its headers.
- Assuming allocated hours equal capacity or availability. A chart showing 40 assigned hours does not prove a person has 40 available hours. Compare demand with a separately defined capacity limit before concluding that a resource is overloaded.
Tips
- Use a consistent period length. Weekly bars are useful for near-term scheduling; monthly bars can be better for a high-level plan. Avoid changing from weeks to months midway through the chart.
- Keep unallocated time and zero allocation distinct. A blank can mean missing data, while 0 means the resource has no planned work for that period. Use zero when you know the allocation is zero.
- Sort periods chronologically. Text labels such as Week 1, Week 10, and Week 2 may sort alphabetically rather than in calendar order. Real dates or labels with leading zeroes can help maintain the correct sequence.
- Use a PivotTable if you need to summarize a changing or larger allocation list without maintaining a formula grid. Select a cell in the source table and choose Insert > PivotTable. Put Period in Rows, Resource in Columns, and Allocated Hours in Values; check that the Values field summarizes by Sum, not Count. A PivotChart can then visualize the summary. Refresh the PivotTable after changing its source data.
- Keep visual formatting restrained. Use one consistent color per resource, avoid 3-D columns, and set a readable vertical-axis scale. If you add a capacity threshold, label it clearly and use the same unit and period basis as the allocation bars.
Troubleshooting
The summary cells show zero. Check that the period and resource text in the summary exactly matches the source. Also check that the hours column contains numbers rather than values stored as text. If your Excel uses a different regional list separator, it may require semicolons instead of commas in formulas; the formula shown uses the standard comma syntax.
The chart has the wrong labels or series. Select the chart and use Chart Design > Select Data to check the chart data range, horizontal axis labels, and series names. The range should include E1:H4 in the example. Use Chart Design > Switch Row/Column if Excel has interpreted rows and columns in the opposite direction.
New allocations do not appear. Confirm that the source table expanded to include the new rows. If you are using the example formula with fixed ranges, extend the source references in every summary formula. Also check whether the summary has a row for the new period or a column for the new resource, and expand the chart selection to include them.
Bars are missing, tiny, or unexpectedly large. Look for blank cells, text-formatted numbers, duplicated allocation rows, or a unit mismatch such as entering 2.5 days alongside hours. Confirm the period total against the source data. If the bars are correct but hard to read, adjust the vertical axis bounds from Format Axis; avoid setting a misleading minimum that exaggerates small differences.
Frequently Asked Questions
Is a resource histogram the same as an Excel histogram?
No. In project scheduling, a resource histogram shows resource demand across time periods. Excel’s statistical Histogram chart groups numeric data into ranges, so it is not the right chart for a weekly allocation summary.
Should I use a clustered or stacked column chart?
Use clustered columns to compare individual resources across periods. Use stacked columns to show each period’s total demand and how each resource contributes to it. Choose 100% stacked only when proportions are the intended message.
Can I make a resource histogram without formulas?
Yes. Build a PivotTable from the allocation table, put periods in Rows, resources in Columns, and allocated amounts in Values, summarized by Sum. Insert a PivotChart if available in your Excel version. A formula summary is useful when you want a fixed, visible layout and direct control over the chart range.
Can the chart show whether a resource is over capacity?
It can help, but the allocation bars alone do not define capacity. Record or calculate a capacity value for each resource and period, then compare demand with that limit. Make sure both values use the same unit, such as hours per week, and clearly identify any capacity line or series.
Can I use days or months instead of weeks?
Yes. Use the chosen period consistently in the source and summary, and make sure the chart labels are clear. For actual dates, use real Excel date values and format them to display as days or months. If a task spans several periods, split or calculate its allocation across those periods before summarizing.
Why does my PivotTable show Count instead of Sum?
Excel may treat the allocated amounts as text or may find nonnumeric entries in the field. Clean the source values so they are numbers, then change the Values field setting to Sum. Refresh the PivotTable and verify its totals against the source list.
Related
- Insert Rows in Excel Multiple Times: A Step-by-Step Guide
- Unable to Unhide Rows in Excel? Try These Fixes
- How to Insert a Row in Excel Between Existing Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Bar Chart in Excel: How to Build and UseA pivot bar chart is a horizontal bar chart connected to a PivotTable, so you can summarize and…
- Excel Waterfall Chart: How to Set the Last Bar as a TotalTo make the last bar in an Excel waterfall chart show the final total, right-click that bar and…
- Excel Waterfall Chart Help: Build, Format, and Fix OneTo create a waterfall chart in Excel for Microsoft 365 on Windows, place each step of your change…
- Pivot Chart Help: Build and Troubleshoot OneIn Excel for Microsoft 365 on Windows, select a cell in your data, choose Insert > PivotChart, confirm…
- Power Pivot and SharePoint: What Works TodayYes—Excel Power Pivot can analyze data stored in SharePoint, including SharePoint lists and files, by importing or connecting…
- Excel Trendline Error: Causes and FixesIf an Excel trendline will not appear, select a supported chart series and choose Chart Design > Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.