Pivot tables & grouping
Build pivot tables and groupings by dragging fields, with the right filters and layout.
20 project-authored proposals · taxonomy v1.1 · 0 new benchmark runs. Verifiers and partial-credit rules below are designs, not executed results.
- Typical applications
- Excel · LibreOffice Calc · Google Sheets
- Class GUI-need label
G2· GUI-essential
Task proposals · 20
Expand to inspect the complete original wording and proposed checks. Original proposal text remains English in both interfaces.
R04.03.01.02-T01 Build a Region × Quarter revenue pivot with date grouping
Original instruction
From table 'Sales' in fixtures/sales_2025.xlsx (18k rows), insert a PivotTable on a new sheet 'Pivot' at A3: Region in rows, OrderDate grouped by Quarters (not Years) in columns, Sum of Revenue as values formatted #,##0. Grand totals on both axes. Save as output/sales_pivot.xlsx.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & click- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Parse pivotTableDefinition: location A3 on 'Pivot', rowFields=Region, colFields=OrderDate with fieldGroup groupBy='quarters', one dataField sum Revenue numFmt #,##0; refreshed cell values equal key. openpyxl cannot author pivots; VBA disabled by policy and no COM, AppleScript or Python automation in the VM.
Proposed partial credit
20% pivot at right location from 'Sales'; 30% rows/columns fields; 30% quarter grouping; 20% values and format correct.
R04.03.01.02-T02 Count tickets by priority and status for one team in Calc
Original instruction
Open fixtures/helpdesk_q3.ods in LibreOffice Calc. Create a pivot table on new sheet 'Summary' with Priority in rows (order P1, P2, P3, P4), Status in columns, Count of TicketID as data, and a page filter on Team set to 'Network'. Save as .ods.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- LibreOffice Calc
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & click- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Parse content.xml table:data-pilot-table: row field Priority with custom order, column field Status, data field count TicketID, page field Team selected 'Network'; output cells equal key. Agent session has macros disabled and no UNO/Python access.
Proposed partial credit
25% pivot on 'Summary'; 25% row/column fields; 25% Network filter; 25% counts and order correct.
R04.03.01.02-T03 Show product share of category with a tabular, repeated-label layout
Original instruction
In fixtures/retail_lines.xlsx build a PivotTable on sheet 'Mix': Category then Product in rows, Sum of Units shown as % of Parent Row Total (1 decimal), Tabular Form with Repeat All Item Labels, no subtotals, sorted by Units descending within each category. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & clickhover· Hover & context- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
pivotTableDefinition: compact=0 outline=0 (tabular), fillDownLabels set, dataField showDataAs='percentOfParentRow', subtotals off, autoSortScope by Units desc; refreshed values equal key (tol 0.05 pp). VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
25% fields and nesting; 30% % of parent row; 25% tabular + repeated labels + no subtotals; 20% sort order.
R04.03.01.02-T04 Bin order values into 250-wide groups and filter by year slicer
Original instruction
fixtures/orders.xlsx: create a PivotTable on 'Bins' counting OrderID by OrderValue grouped into bins starting at 0, ending 2999, by 250 (values ≥3000 fall into the top '>3000' bin). Add a Year slicer from OrderDate and set it to 2025 only. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & clicktype· Text & forms- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
fieldGroup rangePr startNum=0 endNum=2999 groupInterval=250; slicer cache bound to the pivot with only 2025 selected; refreshed counts equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
35% numeric grouping exact; 25% count of OrderID; 25% slicer connected and set to 2025; 15% counts correct.
R04.03.01.02-T05 Manually group states into named custom regions inside a pivot
Original instruction
In fixtures/field_sales.xlsx pivot 'PT_States' lists 14 states. Group them into 4 named groups per input/regions.txt ('Pacific', 'Mountain', 'Plains', 'Lakes'), rename the new field to 'Sales Region', collapse it to group level and sort groups alphabetically. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickdrag· Drag & droptype· Text & forms- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
pivotCacheDefinition has a discrete fieldGroup named 'Sales Region' whose groupItems map states exactly per regions.txt; group items collapsed (sd=0); sort ascending; displayed totals per group equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
45% 14 states grouped correctly (pro-rata); 20% group names and field renamed; 20% collapsed; 15% sorted.
R04.03.01.02-T06 Add a calculated margin field and a top-10 filter to a pivot
Original instruction
In fixtures/product_pnl.xlsx add a calculated field 'Margin %' = Profit / Revenue to pivot 'PT_Products', format it 0.0%, sort products by Margin % descending and apply a Top 10 by Sum of Revenue value filter. Remove the Sum of Profit field from values. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clicktype· Text & formsdrag· Drag & drop- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
cacheField with formula 'Profit/Revenue'; dataField uses it with numFmt 0.0%; filter type 'count' top 10 on Sum of Revenue; Sum of Profit absent; refreshed visible rows and values equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
35% calculated field correct; 25% top-10 value filter; 25% sort by margin; 15% profit field removed and formats.
R04.03.01.02-T07 Build a Numbers pivot table with category rows and year columns
Original instruction
Open fixtures/bookstore.numbers in Numbers. Create a pivot table from table 'Orders' on a new sheet 'Pivot': Category in rows, Order Date grouped by Year in columns, values Sum of Net Sales and Average of Discount (percentage, 1 decimal). Hide the grand-total column. Save.
- Platforms
mac· macOS- Applications
- Numbers
- Interfaces
desk· Desktop GUI- GUI need · task setting
G3· GUI-only- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & click- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader exports the file to .xlsx via its own AppleScript and checks pivot output values, row/column headers and number formats against key; also inspects pivot options in the exported sheet. osascript/JXA is blocked for the agent; the file must be saved by Numbers and keep the original document id, so an imported .xlsx pivot fails.
Proposed partial credit
30% pivot sourced from 'Orders' on 'Pivot'; 25% rows/columns incl. year grouping; 30% two value fields correct; 15% formats and hidden total.
R04.03.01.02-T08 Wire one slicer and one timeline to two pivots and set Q3 EMEA
Original instruction
fixtures/bookings_dash.xlsx has pivots 'PT_Revenue' and 'PT_Units' on sheet 'Dash'. Insert a Region slicer and a BookingDate timeline, connect both to both pivots via Report Connections, then set Region = EMEA and the timeline to Jul–Sep 2025. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickdrag· Drag & drop- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
slicerCache for Region and timelineCache for BookingDate each list both pivots; selection EMEA and timeline range 2025-07-01..2025-09-30; both pivots' refreshed values equal key. Timeline objects cannot be authored by openpyxl; VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
25% slicer inserted; 25% timeline inserted; 25% both connected to both pivots; 25% selections and resulting values correct.
R04.03.01.02-T09 Distinct-count customers by month and channel via the Data Model
Original instruction
In Excel for Windows, load table 'Txns' from fixtures/txn_log.xlsx into the Data Model and build a PivotTable on 'Active' showing Distinct Count of CustomerID with TxnDate by Month (Year > Month hierarchy) in rows and Channel in columns. Save as output/active_customers.xlsx.
- Platforms
win· Windows- Applications
- Excel · Power Pivot
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickdrag· Drag & drop- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Workbook contains a Data Model (xl/model) with 'Txns'; pivot is OLAP-based with a distinct-count measure on CustomerID; Excel COM refresh by grader yields values equal key. Regular cache pivots cannot distinct-count; VBA disabled; no COM host or Python in the VM.
Proposed partial credit
25% table in Data Model; 35% distinct count measure; 25% month and channel layout; 15% values correct.
R04.03.01.02-T10 Repoint three stale pivots to a growing table and refresh
Original instruction
fixtures/ops_report.xlsx has 3 pivots built on fixed range Data!A1:K4200; new 2026 rows were pasted below. Convert the data to an Excel Table 'tblOps', change all 3 pivots' data source to tblOps so they share one cache, refresh, and confirm 2026 appears in each year filter. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clicktype· Text & forms- Work product
WP02· Records/table- Conditions
hidden· Hidden/stale state- Proposed difficulty
- 4 / 5
Proposed verifier
Table tblOps spans all rows; all 3 pivots reference cacheId with worksheetSource name='tblOps' (single shared cache); cached records include 2026; refreshed values equal key. Grader appends rows and refreshes to confirm table-based source. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
20% table created; 40% three pivots repointed (pro-rata); 20% single shared cache; 20% refreshed values incl. 2026.
R04.03.01.02-T11 Group a Calc pivot's dates by month and quarter with two data fields
Original instruction
In fixtures/energy_meter.ods create a LibreOffice Calc pivot table on sheet 'Monthly' with ReadingDate grouped by Quarters and Months in rows, Site in columns, and data fields Sum of kWh and Average of PeakKW. Turn off 'Show expand/collapse buttons' and add a pivot chart (columns, Sum of kWh only). Save.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- LibreOffice Calc
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & click- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
content.xml data-pilot-table: date grouping quarters+months, Site column field, two data fields with sum/average, drill-down buttons off; a chart object sourced from the pilot with one series; output values equal key. Macros/UNO disabled.
Proposed partial credit
30% date grouping; 30% two data fields correct; 15% buttons off; 25% pivot chart with right series.
R04.03.01.02-T12 Build a pivot and a GETPIVOTDATA summary that survives re-layout
Original instruction
From fixtures/hr_headcount.xlsx build pivot 'PT_HC' (Department rows, Grade columns, Count of EmployeeID). On sheet 'Board' fill B2:E9 with GETPIVOTDATA formulas for the 8 departments × 4 grades listed there. The grader will move fields around, so plain cell references are not acceptable.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & droptype· Text & forms- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Pivot fields as specified; every Board!B2:E9 cell is a GETPIVOTDATA formula referencing PT_HC; grader swaps rows/columns and moves the pivot via COM, refreshes, and Board values must still equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
30% pivot correct; 30% GETPIVOTDATA in all 32 cells; 40% values correct before and after re-layout.
R04.03.01.02-T13 Relate three tables in Power Pivot and report YoY revenue by quarter
Original instruction
In Excel for Windows open fixtures/sales_model.xlsx (tables Sales, Products, Calendar). In Power Pivot Diagram View create relationships Sales[ProductKey]→Products and Sales[DateKey]→Calendar, mark Calendar as date table, add DAX measures Revenue and Revenue YoY %, and build a pivot by Calendar Year > Quarter and Products[Line].
- Platforms
win· Windows- Applications
- Excel · Power Pivot
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & clicktype· Text & formscode· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 5 / 5
Proposed verifier
Model inspected with the grader's DAX/AMO reader: 2 active relationships with correct columns, Calendar marked as date table, measures evaluate to key for 12 probe filter contexts; pivot output equals key after refresh. VBA disabled; no COM host or Python in the VM.
Proposed partial credit
25% relationships; 10% date table; 35% measures correct; 30% pivot layout and values.
R04.03.01.02-T14 Reproduce a pivot report exactly from a reference screenshot
Original instruction
input/target_pivot.png shows a management pivot report built from fixtures/claims.xlsx. Rebuild it on sheet 'Report': same fields and order, layout form, subtotal placement, grand-total settings, number formats, value filter, collapsed items and PivotStyle. Only the screenshot specifies the target.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & clickvis· Visual judgment- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 5 / 5
Proposed verifier
Grader compares pivotTableDefinition attributes to the reference spec (fields, order, outline/compact, subtotalTop, grand totals, numFmts, filter, sd flags, style name) and renders the sheet via Excel COM to PNG: SSIM ≥0.92 vs target. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
30% fields and order; 25% layout/subtotal/totals options; 20% formats, filter and collapse state; 25% rendered SSIM threshold.
R04.03.01.02-T15 Combine running-total and rank views and collapse all but one region
Original instruction
In fixtures/subscriptions.xlsx pivot 'PT_MRR' (Region > Country rows, Month columns): add MRR twice, once as Running Total In Month shown as % (Running Total in %), once as Rank Largest to Smallest within Region. Collapse every region except EMEA, and set the pivot to keep column widths on update. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickdrag· Drag & drophover· Hover & context- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 5 / 5
Proposed verifier
Two dataFields on MRR with showDataAs percentOfRunningTotal (baseField Month) and rankDescending (baseField Country within Region); region items sd=0 except EMEA; useAutoFormatting=0; refreshed values equal key. VBA, COM and AppleScript automation disabled, no Python in the VM.
Proposed partial credit
35% running-total % field; 35% rank field with right base; 20% collapse state; 10% column width option.
R04.03.01.02-T16 Control pair of T01: create the same pivot through the Sheets API
Original instruction
Control pair of T01. The same 18k-row sales data is in Google Sheet 'Sales 2025'. Using the Sheets API (service-account credentials provided), create a pivot table on new sheet 'Pivot' at A3 with Region rows, OrderDate grouped by quarter in columns and SUM of Revenue. No UI use needed.
- Platforms
web· Any OS (web)- Applications
- Google Sheets · Sheets API
- Interfaces
api· API/SDK- GUI need · task setting
AP· API-sufficient- GUI need · ordinary access
AP· API-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Sheets API read: pivotTable on 'Pivot'!A3 with rows Region, columns OrderDate with dateTimeRule QUARTER, value SUM Revenue; computed cells equal the T01 key.
Proposed partial credit
30% pivot created at location; 40% fields and quarter grouping; 30% values equal key.
R04.03.01.02-T17 Insert department subtotals and collapse the outline to level 2
Original instruction
fixtures/cost_centres.xlsx 'Costs' is sorted by Department. Use Data > Subtotal to add Sum of Cost and Sum of Budget at each change in Department with a grand total, then collapse the outline to level 2 so only subtotals show, and set print titles to repeat row 1. Save.
- Platforms
mac· macOSwin· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & click- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
SUBTOTAL(9,…) rows inserted after each department with correct ranges; outline levels set and detail rows hidden (level 2 view); grand total row; print titles $1:$1; values equal key. openpyxl could emulate this, so the task tests the feature path, not exclusivity.
Proposed partial credit
40% subtotal rows correct; 15% grand total; 30% outline collapsed to level 2; 15% print titles.
R04.03.01.02-T18 Filter a Calc pivot by hidden items and group dates into Monday weeks
Original instruction
In fixtures/web_sessions.ods create a pivot on 'Weekly': SessionDate grouped into 7-day intervals starting Monday 2025-06-02, Browser in rows sorted by Sum of Sessions descending, page fields Country (hide 'Unknown' and 'Bot') and Device = 'Mobile'. Save.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- LibreOffice Calc
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
drag· Drag & dropclick· Point & clicktype· Text & forms- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
data-pilot grouping with step 7 days and start 2025-06-02; Browser sorted by data field desc; Country members 'Unknown' and 'Bot' hidden; Device page field 'Mobile'; output values equal key. Macros/UNO disabled.
Proposed partial credit
35% weekly grouping with correct start; 20% sort; 30% page-field filters incl. hidden members; 15% values.
R04.03.01.02-T19 Create and rearrange a pivot table in Numbers for iPad by touch
Original instruction
On iPad, open 'Club Dues.numbers' in Numbers. Create a pivot table from table 'Payments': Member Type in rows, Month in columns, Sum of Amount. Then, by dragging in the pivot options, move Month to rows under Member Type, add Count of Payment ID, and sort Member Type by Sum of Amount descending.
- Platforms
ios· iOS/iPadOS- Applications
- Numbers
- Interfaces
mob· Mobile touch- GUI need · task setting
G3· GUI-only- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
touch· Touch gesturesdrag· Drag & drop- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
File synced via iCloud; grader exports to .xlsx on a Mac via AppleScript and checks pivot output headers, nesting, two value columns and order against key. Shortcuts is disabled on the agent's iPad and it has no Mac or iCloud web access; the file must be saved by Numbers for iPadOS.
Proposed partial credit
25% pivot created from 'Payments'; 30% Month nested under Member Type; 25% count field added; 20% sort order.
R04.03.01.02-T20 Consolidate four regional sheets with a multiple-range pivot
Original instruction
fixtures/budget_regions.xlsx has sheets North, South, East, West with the same accounts in different row orders. Using the PivotTable and PivotChart Wizard (multiple consolidation ranges), build pivot 'PT_Consol' on 'Consolidated' with one page field whose items are named North/South/East/West, accounts in rows, months in columns, Sum of values.
- Platforms
win· Windows- Applications
- Excel
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickkey· Shortcutsdrag· Drag & drop- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
pivotCacheDefinition consolidation source with 4 rangeSets and page item names North..West; rows = accounts, cols = months; values equal key; selecting page item 'East' (grader via COM) shows East-only totals. VBA disabled; no COM host or Python in the VM.
Proposed partial credit
30% 4 ranges consolidated; 25% page items named; 30% totals equal key; 15% per-region page filter works.
Mapped research references
Existing benchmark-to-class links from the research catalog. They are not record-level annotations, executions or product endorsements.