# How to Create Pivot Tables from a Source Range with Multi-Field Support in OfficeCLI

> Learn to create OfficeCLI pivot tables from source ranges with multi-field support. Easily define rows, columns, and values for powerful data analysis.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-08-02

---

**Use `officecli add --type pivottable` with `--prop source=Sheet!A1:D100` to define your data range, then specify multiple row fields with `--prop rows=Region,Product` and value aggregations with `--prop values=Sales:sum,Quantity:avg`.**

Creating **pivot tables from a source range** in OfficeCLI involves defining a rectangular data region and mapping fields to the four pivot axes: rows, columns, values, and filters. The CLI internally splits this into a workbook-level **PivotTableCacheDefinitionPart** (data cache) and a worksheet-level **PivotTablePart** (rendered table), both managed by the `PivotTableHelper` class in [[`src/officecli/Core/PivotTableHelper.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/PivotTableHelper.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/PivotTableHelper.cs#L904). This guide covers multi-field configurations, aggregation functions, and placement options based on the actual OfficeCLI source implementation.

## Defining the Source Range

Every pivot table requires a **source** property that points to a rectangular range containing headers and data. The helper validates this reference through `PivotTableHelper.ResolvePivotSourceSpec` (around line 1669) before creating or reusing a cache definition.

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500

```

The source can be:
- **Sheet-qualified range**: `Sheet1!A1:D100`
- **Named range**: `SalesData` (if defined in the workbook)

If you omit the **anchor** property (`pos`), the pivot auto-places immediately after the source range via `PivotTableHelper.CreatePivotTable` (line 1675).

## Configuring Multi-Field Rows, Columns, and Filters

OfficeCLI supports **comma-separated field lists** for multi-field axis configurations. The parser in `PivotTableHelper.BuildPivotTableDefinition` (around line 1120) tokenizes these into individual `PivotField` references.

### Multi-Field Row Hierarchy

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product,Rep

```

Fields appear left-to-right in the pivot, creating a nested hierarchy: Region → Product → Rep.

### Multi-Field Columns

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop cols=Year,Quarter

```

### Filter Fields

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop filters=Status,Priority

```

Filter fields populate the pivot's filter area without appearing in rows or columns.

## Setting Value Aggregations with Multi-Field Support

The **values** property accepts `Field:agg` tuples separated by commas. Valid aggregations per the schema include `sum`, `avg`, `count`, `max`, `min`, `product`, `stddev`, `stddevp`, `var`, and `varp`.

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product \
  --prop cols=Year \
  --prop values=Revenue:sum,Units:count,Margin:avg

```

Each tuple generates a separate data field in the pivot cache. The parser handles type coercion and aggregation function mapping within `BuildPivotTableDefinition`.

## Top-N Row Limits and Style Options

### Limiting Row Keys

The **topN** property restricts displayed row keys to the highest N values by the first value field:

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Product \
  --prop values=Revenue:sum \
  --prop topN=10

```

This executes in `PivotTableHelper.CreatePivotTable` (around line 1089) by setting page field filters on the row axis.

### Layout and Style Flags

| Property | Values | Effect |
|----------|--------|--------|
| `style` | `compact`, `outline`, `tabular` | Pivot table format |
| `subtotals` | `true`, `false` | Show/hide subtotal rows/columns |
| `grandtotals` | `true`, `false` | Show/hide grand total row/column |
| `blankrows` | `true`, `false` | Insert blank rows after items |

```bash
officecli add report.xlsx /Summary --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Region,Product \
  --prop values=Sales:sum \
  --prop style=compact,outline \
  --prop subtotals=false \
  --prop grandtotals=false

```

Style processing occurs in `BuildPivotTableDefinition` (around line 1205).

## Complete Multi-Field Configuration Example

```bash
officecli add sales.xlsx /Dashboard --type pivottable \
  --prop source=RawData!A1:F1000 \
  --prop rows=Territory,Account_Manager \
  --prop cols=Quarter,Month \
  --prop values=Deal_Size:sum,Win_Rate:avg,Opportunities:count \
  --prop filters=Stage,Priority \
  --prop topN=50 \
  --prop style=compact \
  --prop subtotals=true \
  --prop grandtotals=true \
  --prop pos=H5

```

This creates a pivot table anchored at cell H5 with:
- Two-level row hierarchy (Territory → Account_Manager)
- Two-level column hierarchy (Quarter → Month)
- Three aggregated metrics with different functions
- Two active filters
- Top-50 row limit
- Compact styling with subtotals and grand totals enabled

## Verifying and Inspecting Pivot Tables

After creation, query the pivot structure using `get` with JSON formatting. The CLI exposes read-only child nodes (`pivotfield`, `pivotrow`, `pivotcolumn`, `pivotdata`) for inspection.

```bash
officecli get sales.xlsx /Dashboard/pivottable[1] --format json

```

These structural nodes cannot be directly added, set, or removed—they reflect the internal Open XML representation per the schema in [[`schemas/help/xlsx/pivottable.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/pivottable.json)](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/pivottable.json).

## Attaching Slicers for Interactive Filtering

Once a pivot table exists, bind slicers using the slicer handler in [[`src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs#L42):

```bash

# Create slicer filtered by Region

officecli add sales.xlsx /Dashboard --type slicer \
  --prop pivotTable=/Dashboard/pivottable[1] \
  --prop field=Region

# Additional slicer for Product

officecli add sales.xlsx /Dashboard --type slicer \
  --prop pivotTable=/Dashboard/pivottable[1] \
  --prop field=Product

```

## PowerShell Equivalent Syntax

```powershell
officecli add sales.xlsx "/Dashboard" --type pivottable `
  --prop source=RawData!A1:F1000 `
  --prop rows=Territory,Account_Manager `
  --prop cols=Quarter,Month `
  --prop values=Deal_Size:sum,Win_Rate:avg,Opportunities:count `
  --prop filters=Stage,Priority `
  --prop topN=50 `
  --prop style=compact `
  --prop subtotals=true `
  --prop grandtotals=true `
  --prop pos=H5

```

## Summary

- **`source`** (alias `src`) defines the rectangular data range for cache generation
- **Multi-field axes** use comma-separated lists: `rows=A,B,C` creates nested hierarchies
- **Value aggregations** specify `Field:function` tuples: `Revenue:sum,Units:avg`
- **`topN`** limits displayed row keys; **`pos`** overrides auto-placement
- **Style flags** (`style`, `subtotals`, `grandtotals`) control visual presentation
- **Slicers** attach to existing pivots via separate `--type slicer` commands
- Core implementation resides in [`PivotTableHelper.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/PivotTableHelper.cs); schema documentation in [`pivottable.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/pivottable.json)

## Frequently Asked Questions

### What aggregation functions does OfficeCLI support for pivot values?

OfficeCLI supports standard Excel aggregations: `sum`, `avg`, `count`, `max`, `min`, `product`, `stddev`, `stddevp`, `var`, and `varp`. Specify these in `Field:agg` format within the `values` property. The parser in `BuildPivotTableDefinition` validates and maps these to Open XML aggregation constants.

### Can I create a pivot table without specifying row or column fields?

Yes. A pivot table with only **values** and optionally **filters** produces a single-cell summary (grand totals only). However, at minimum you must specify `source` and at least one field mapped to any axis (rows, columns, values, or filters) for a valid pivot cache.

### How does OfficeCLI handle overlapping source and pivot placement?

If you omit the `pos` property, `PivotTableHelper.CreatePivotTable` automatically places the pivot starting one column after the source range's right edge. For explicit placement, provide `pos=CellReference`—the helper validates no overlap exists before insertion.

### Why can't I modify pivot field properties directly through child nodes?

The `pivotfield`, `pivotrow`, `pivotcolumn`, and `pivotdata` nodes exposed via `get` are **read-only structural reflections** of the Open XML parts. Per the schema in [`pivottable.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/pivottable.json), these support only `get` and `delete` operations at the parent pivot level. Modify pivot structure by recreating with updated `--prop` values or using Excel's object model.