Learning goals

Collapse Hours Tidy to one row per consultant with Group By
Filter to the report month before grouping, and say why the order matters
Add a capacity column and compute utilization without a helper column
Rebuild the utilization formula and the status thresholds on the grouped table

Concepts

Group By: the query that replaces the pivot

Home > Group By takes the tidy table and collapses it. Northgate groups by Consultant and Role, aggregating Billed Hours with the Sum operation. Forty-two rows in, seven out -- one per consultant, each carrying that consultant's total. Add a second aggregation (Average, Count, Min, Max are all on the same dialog) and you get a statistical row per group, not a second query.

The order of operations matters more than the dialog does. The Utilization query filters to the report month FIRST (Date.Month equals the ReportMonth parameter, 8) and groups SECOND. Group first and you would be comparing July-plus-August hours against an August-only capacity -- a number that looks precise and means nothing.

=SUM(Utilization!$E$5:$E$1004)

The roll-up over the grouped table: 892 billed hours across all seven consultants in August. The same SUM over Hours Tidy's August rows gives the same 892 -- two different shapes, one number, which is exactly how you prove a group-by did not lose rows.

Capacity and utilization

Utilization is hours billed divided by hours available. Northgate prices a billable week at 37.5 hours, so a four-week month gives each consultant a capacity of 150 -- the MonthlyCapacity parameter, mirrored in Settings!C10. The query adds that column as a constant (each MonthlyCapacity), and the utilization column divides the two.

August's seven rows sort from Priya Raman's 148 hours (98.7 percent) down to Rachel Cohen's 100 (66.7 percent). Four consultants are On target (85 percent and up), two are on Watch, one is Below plan. That spread is the whole point of the table: it names who is sellable and who is stretched, from numbers nobody typed.

=IF($F5=0,0,$E5/$F5)

The utilization column: billed hours in E5 divided by capacity in F5, guarded against an empty capacity so a blank row can never produce #DIV/0!. The cell carries a percent format, so 0.9866666 reads as 98.7%. The IF guard is the same discipline IFERROR gives the report sheet -- fail visible, not loud.

Status thresholds are policy, not arithmetic

The Status column turns utilization into a word: On target at 85 percent and above, Watch from 70 to 85, Below plan under 70. Those thresholds are Northgate's staffing policy, agreed in a meeting, written down once, and now applied identically every month. When the policy changes, one column changes -- not twelve formulas and a legend nobody trusts.

This is the pattern to take away: shape the data until it is tidy (lesson 5), group it until it is summarized (this lesson), and only then apply words and colors. Thresholds bolted onto raw rows are how a report ends up with three different definitions of 'overdue' in the same workbook.

💡 Tips:

Group By keeps only the columns you group on plus the aggregations. If you need Role along for the ride, group on both keys -- that is why the Utilization query groups on Consultant and Role, not Consultant alone.

In Excel 365 you could reach for a PivotTable here and be correct. The query version wins because it feeds the Monthly Report as a clean table with no pivot cache to refresh out of order.

Practice

The exercise opens on the Utilization output (AFTER); the input is the Hours Tidy tab. Cell G5 is blank -- you retype the utilization formula. Nothing is saved in the browser.

Audit the grouped table — Seven rows, sorted by Billed Hours: Priya Raman 148, Dana Whitfield 146, Sam Lindgren 136, Marcus Bell 130, Tom Okafor 120, Elena Vasquez 112, Rachel Cohen 100. Every row carries Role, the Aug 2026 month label, and a 150-hour capacity.
Retype the utilization formula — Cell G5 is empty. Type =IF($F5=0,0,$E5/$F5) and press Enter: 98.7%, Priya Raman's 148 of 150. Column G runs from 98.7% down to 66.7%, and column H reads On target four times, Watch twice, Below plan once.
Reconcile against the tidy table — Sum column E: 892 hours, against 1,050 capacity (7 consultants x 150). Flip to Hours Tidy and add Dana Whitfield's four August rows: 38 + 37 + 36 + 35 = 146 -- the same number her grouped row carries.
Check the month filter — Hours Tidy holds 42 rows; this table holds 7 consultants of August only. The fourteen July rows (two weeks times seven consultants) never reached the group-by -- that is the filter-first order doing its job.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped =IF($F5=0,0,$E5/$F5) into G5 and got 98.7% for Priya Raman
☐ Confirmed the grouped table holds 7 rows totalling 892 August hours against 1,050 capacity
☐ Verified the baseline: 892 timesheet hours in August, the numerator behind the 84.95% team utilization

⚡ Lesson 08 · Group and aggregate