How to Create and Configure Pivot Tables with Multi-Field Grouping in OfficeCLI

Use officecli add <file> <sheet-path> --type pivottable with --prop arguments like rows=Region,Category and values=Sales:sum to build hierarchical pivot tables directly from the command line.

OfficeCLI's XLSX skill provides a complete command-line interface for creating Excel pivot tables with multi-field grouping, date bucketing, and calculated fields. According to the iOfficeAI/OfficeCLI source code, the pivot table implementation uses a shared cache architecture that automatically links multiple pivots referencing the same source range, making repeated analyses lightweight and ensuring data changes propagate instantly.

Core Architecture and Data Model

The pivot table functionality resides in the XLSX skill defined in [skills/officecli-xlsx/SKILL.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/skills/officecli-xlsx/SKILL.md#L245-L270). When you invoke add --type pivottable, the CLI performs three operations:

1. Creates a <pivotTableDefinition> XML node in the workbook structure
2. Copies the source range into a shared pivot cache (Copy-on-Write pattern)
3. Links subsequent pivots to the existing cache when ranges match

This design means you can create dozens of pivot views from the same dataset without duplicating cached data or slowing down file operations.

Essential Properties for Multi-Field Grouping

Master these --prop arguments to configure complex pivot layouts:

Property	Purpose	Multi-Field Syntax
source	Raw data range	source=Sheet1!A1:J51
rows	Row axis fields (comma-separated)	rows=Region,Category,Subcategory
cols	Column axis fields	cols=Quarter,Month
values	Data fields with aggregation	values=Sales:sum,Cost:sum:percent_of_row
filters	Page-level slicers	filters=Channel,Priority
layout	Visual structure: compact, outline, tabular	layout=tabular
repeatlabels	Repeat outer group labels on each row	repeatlabels=true
blankrows	Insert visual separators between groups	blankrows=true
grandtotals	Total placement: both, rows, cols, none	grandtotals=both
sort	Sort direction: asc, desc, locale, locale-desc	sort=desc
style	Excel built-in pivot style	style=PivotStyleDark2

The values property accepts a three-part syntax: field:aggregation[:displayMode]. Supported aggregations include sum, count, average, min, max, product, countNums, stdDev, stdDevp, var, varp. Display modes include percent_of_row, percent_of_col, percent_of_total, index, difference_from_previous.

Creating Hierarchical Row Groupings

Two-Level Row Hierarchy

The simplest multi-field grouping combines two fields in the row axis:

officecli add sales.xlsx "/Region-by-Category" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region,Category \
  --prop values=Sales:sum \
  --prop layout=tabular \
  --prop repeatlabels=true \
  --prop style=PivotStyleLight1

This creates nested rows where Region forms the outer group and Category appears indented beneath each region. The repeatlabels=true setting ensures the Region name appears on every row, improving readability when subtotals are disabled.

Three-Level Deep Grouping

Add a third field for more granular analysis:

officecli add sales.xlsx "/Deep-Hierarchy" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region,Category,Subcategory \
  --prop cols=Quarter \
  --prop 'values=Sales:sum,Margin:sum' \
  --prop layout=outline \
  --prop blankrows=true \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium9

The layout=outline option places each field in its own column rather than merging them into a single indented column, making the hierarchy structure explicit.

Date Grouping with Automatic Bucketing

OfficeCLI supports automatic date grouping through colon-prefixed modifiers in field names:

officecli add sales.xlsx "/Date-Grouping" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop 'rows=Date:year,Date:quarter,Date:month' \
  --prop 'values=Sales:sum,Cost:sum' \
  --prop layout=outline \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium7

Available date grouping levels:

• Date:year — Calendar year
• Date:quarter — Quarter number (1-4)
• Date:month — Month name
• Date:day — Day of month
• Date:week — Week number
• Date:hour, Date:minute — Time components

The CLI automatically creates the grouping hierarchy in the pivot cache without modifying your source data.

Column Axis and Cross-Tabulation

Combine row and column fields for true cross-tabulation:

officecli add sales.xlsx "/Quarterly-Grid" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region,Category \
  --prop cols=Quarter,Product_Line \
  --prop 'values=Sales:sum,Quantity:sum' \
  --prop layout=compact \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium4

The column axis supports the same multi-field syntax as rows, creating nested column headers. When both axes contain multiple fields, the pivot produces a matrix view ideal for spotting patterns across dimensions.

Page Filters and Interactive Slicing

Add dropdown filters above the pivot for interactive analysis:

officecli add sales.xlsx "/Filtered-View" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region,Category \
  --prop cols=Quarter \
  --prop 'values=Sales:sum' \
  --prop 'filters=Channel,Priority,Sales_Rep' \
  --prop sort=desc \
  --prop layout=tabular \
  --prop repeatlabels=true \
  --prop style=PivotStyleDark2

The filters property places these fields in the pivot's page area—users can select values from dropdown menus without modifying the pivot structure.

Advanced Value Configurations

Multiple Aggregations and Display Modes

officecli add sales.xlsx "/Advanced-Metrics" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region \
  --prop 'values=Sales:sum,Sales:count,Sales:average,Cost:sum:percent_of_row' \
  --prop layout=tabular \
  --prop grandtotals=rows \
  --prop style=PivotStyleMedium6

The fourth value field demonstrates the display mode syntax: Cost:sum:percent_of_row shows each row's percentage contribution to its region total.

Calculated Fields

Define custom metrics using calculatedFieldN properties:

officecli add sales.xlsx "/Calculated-Fields" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop rows=Region \
  --prop 'values=Sales:sum' \
  --prop 'calculatedField1=Margin:=Sales-Cost' \
  --prop 'calculatedField2=Tax:=Sales*0.1' \
  --prop 'calculatedField3=Net:=Margin-Tax' \
  --prop layout=tabular \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium3

Calculated fields support standard Excel operators and field references. They appear as additional value columns in the final pivot.

Inspecting and Debugging Pivot Definitions

Retrieve the complete XML-derived structure for verification:

officecli get sales.xlsx "/Region-by-Category/pivottable[1]"

This outputs the pivot table definition as JSON, including cache relationships, field mappings, and formatting directives—useful for automated testing or troubleshooting configuration issues.

Summary

• Multi-field grouping uses comma-separated values in rows and cols properties: rows=Region,Category,Subcategory
• Date bucketing applies modifiers like Date:year and Date:quarter for automatic temporal grouping
• Shared cache architecture keeps multiple pivots lightweight and synchronized with source data
• Value syntax supports aggregation functions and display modes: field:agg[:showDataAs]
• Calculated fields extend pivots with custom formulas using calculatedFieldN=name:=formula syntax
• Authoritative reference lives in [skills/officecli-xlsx/SKILL.md](https://github.com/iOfficeAI/OfficeCLI/blob/main/skills/officecli-xlsx/SKILL.md#L245-L270) with 19 working examples in examples/excel/pivot-tables.md

Frequently Asked Questions

How do I group more than two fields in rows?

List all fields comma-separated in the rows property: --prop rows=Region,Category,Subcategory,Product. OfficeCLI creates the nested hierarchy automatically, with each subsequent field indented one level deeper. Use layout=outline to place each field in its own column for clearer export formatting.

Can I mix date grouping with regular fields?

Yes. Combine date-modified fields with standard fields: --prop 'rows=Date:year,Date:quarter,Region,Category'. The CLI processes date groupings first in the hierarchy, then appends regular fields. Date grouping creates implicit calculated fields in the pivot cache without altering your source worksheet.

What happens when source data changes?

The pivot cache automatically reflects updates when you reopen the workbook in Excel. Because OfficeCLI uses shared caches for identical source ranges, all pivots referencing Sheet1!A1:J51 update simultaneously. No manual refresh or formula recalculation is required—the cache stores raw data, not computed aggregations.

How do I remove subtotals for cleaner exports?

Use --prop grandtotals=none to disable all totals, or --prop subtotals=none if the implementation supports granular control. Pair with --prop repeatlabels=true to maintain context without total rows. For maximum control over totaling behavior, inspect the generated pivot definition with officecli get and verify the pivotTableDefinition XML attributes.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}