# How to Create and Configure Pivot Tables with Multi-Field Grouping in OfficeCLI > Easily create pivot tables with multi-field grouping using OfficeCLI. Configure rows and values directly from the command line for hierarchical data analysis. - Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI) - Tags: how-to-guide - Published: 2026-08-03 --- **Use `officecli add --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)](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 **``** 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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 ```bash 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: ```bash 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: ```bash 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)](https://github.com/iOfficeAI/OfficeCLI/blob/main/skills/officecli-xlsx/SKILL.md#L245-L270) with 19 working examples in [`examples/excel/pivot-tables.md`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.