# How to Create Pivot Tables from a Source Range Using OfficeCLI: Complete Feature Guide

> Learn to create Excel pivot tables from source ranges using OfficeCLI. Explore features like field configuration, aggregation, styling, and slicers via the command line.

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

---

**OfficeCLI enables command-line creation of Excel pivot tables from source ranges using the `add` command with `--type pivottable`, supporting field configurations, aggregations, styling, and slicer integration through a dual-part architecture managed by the `PivotTableHelper` class.**

OfficeCLI by iOfficeAI provides comprehensive command-line manipulation of Excel workbooks, allowing you to create pivot tables from a source range using OfficeCLI without opening the Excel application. The tool implements a sophisticated dual-part architecture consisting of a **PivotTablePart** (the rendered visualization) and a **PivotTableCacheDefinitionPart** (the cached source data), with all creation logic centralized in the `PivotTableHelper` class. According to the schema documented in [`schemas/help/xlsx/pivottable.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/pivottable.json), the CLI exposes extensive configuration options through property bags while maintaining read-only structural integrity for generated pivot components.

## Understanding the OfficeCLI Pivot Table Architecture

OfficeCLI treats pivot tables as first-class workbook elements with a distinct internal structure. When you execute an **add** command with `--type pivottable`, the CLI parses the supplied property bag and constructs a `PivotTableDefinition` XML document, as implemented 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) around line 904. The architecture separates concerns between the **PivotTablePart** (worksheet-level rendering) and the **PivotTableCacheDefinitionPart** (workbook-level data cache), enabling efficient data management and update operations.

## Creating a Pivot Table from a Source Range

### Specifying the Source Data

The mandatory **source** property (alias `src`) defines the rectangular data range that feeds the pivot table, formatted as `SheetName!A1:D100`. The `PivotTableHelper.ResolvePivotSourceSpec` method (approximately line 1669 in [`PivotTableHelper.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/PivotTableHelper.cs)) validates this reference before creating or reusing a cache definition. Validating the source range ensures the pivot table maintains referential integrity with the underlying data.

### Configuring Row, Column, and Data Fields

Field placement and aggregation are controlled through three key properties parsed by `PivotTableHelper.BuildPivotTableDefinition` (around line 1120):

- **rows**: Comma-separated field names for row labels (e.g., `Region,Product`)
- **cols**: Comma-separated field names for column labels (e.g., `Year`)
- **values**: Field names with aggregation functions using `Field:agg` syntax (e.g., `Sales:sum,Quantity:avg`)

You can also apply filters using the **filters** property, which adds fields to the filter axis without displaying them in the row or column structure.

### Positioning the Pivot Table

By default, OfficeCLI places the pivot table immediately after the source range unless you specify an explicit **anchor** using the `pos` property (alias for position). The `PivotTableHelper.CreatePivotTable` method (approximately line 1675) handles this positioning logic, ensuring the pivot table does not overwrite existing data unless explicitly directed.

## Supported Pivot Table Features and Configuration

### Aggregation and Top-N Filtering

Beyond standard aggregations (sum, avg, count, etc.), OfficeCLI supports **topN** filtering to limit row keys to the top N values based on the first data field. Set `--prop topN=5` to retain only the five highest-value rows, implemented in `PivotTableHelper.CreatePivotTable` around line 1089.

### Layout and Visual Styling

Control the visual presentation through properties processed by `BuildPivotTableDefinition` (approximately line 1205):

- **style**: Layout options including `compact` and `outline` formats
- **subtotals**: Boolean to enable or disable subtotal rows (default true)
- **grandtotals**: Boolean to show or hide grand total rows and columns (default true)

### Slicer Integration

After creating a pivot table, you can attach interactive slicers using the separate slicer handler. The [`ExcelHandler.Slicer.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Slicer.cs) file (see line 42 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)) implements slicer binding by referencing the pivot table path (e.g., `/Sales/pivottable[1]`) and specifying the target field.

## Practical Command-Line Examples

The following examples demonstrate complete pivot table creation workflows:

```bash

# Create a comprehensive pivot table with styling and Top-N filtering

officecli add sales.xlsx /Sales --type pivottable \
  --prop source=Data!A1:E200 \
  --prop rows=Region,Product \
  --prop cols=Year \
  --prop values=Sales:sum,Quantity:avg \
  --prop topN=10 \
  --prop style=compact,outline \
  --prop subtotals=false

```

```bash

# Verify pivot table structure and read-only child nodes

officecli get sales.xlsx /Sales/pivottable[1] --format json

```

```bash

# Attach a slicer to filter the pivot by Region

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

```

```powershell

# PowerShell equivalent with backtick continuation

officecli add sales.xlsx "/Sales" --type pivottable `
  --prop source=Data!A1:E200 `
  --prop rows=Region,Product `
  --prop cols=Year `
  --prop values=Sales:sum,Quantity:avg `
  --prop topN=10 `
  --prop style=compact,outline `
  --prop subtotals=false

```

## Summary

- OfficeCLI creates pivot tables using a dual-part architecture (**PivotTablePart** and **PivotTableCacheDefinitionPart**) managed by `PivotTableHelper`
- The **source** property (mandatory) defines the input range via `ResolvePivotSourceSpec` (line 1669)
- Configure dimensions using **rows**, **cols**, and **values** (with aggregation syntax) via `BuildPivotTableDefinition` (line 1120)
- Apply **topN** filtering, **style** options, and **subtotals** controls for customized presentation
- Attach **slicers** to existing pivot tables using the handler in [`ExcelHandler.Slicer.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Slicer.cs) (line 42)
- Child nodes (`pivotfield`, `pivotrow`, `pivotcolumn`, `pivotdata`) are read-only structural elements generated by the CLI

## Frequently Asked Questions

### What is the minimum required property to create a pivot table?

You must specify the **source** property pointing to a valid range (e.g., `Sheet1!A1:D100`). The `PivotTableHelper.ResolvePivotSourceSpec` method validates this reference before creating the **PivotTableCacheDefinitionPart** and **PivotTablePart**.

### Can I modify an existing pivot table's field structure?

No. According to the schema in [`schemas/help/xlsx/pivottable.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/pivottable.json), child nodes such as `pivotfield`, `pivotrow`, `pivotcolumn`, and `pivotdata` are read-only structural elements. You must delete and recreate the pivot table with new field configurations.

### How does OfficeCLI handle the data cache?

OfficeCLI creates a **PivotTableCacheDefinitionPart** at the workbook level to cache the source data, while the **PivotTablePart** handles the worksheet-level rendering. The `PivotTableHelper` class manages both parts, ensuring the cache updates when source data changes.

### Is slicer binding supported for all pivot tables?

Yes. Any pivot table created via OfficeCLI can accept slicer bindings using the `add` command with `--type slicer`, referencing the pivot table path (e.g., `/Sales/pivottable[1]`) and specifying the target field, as implemented in [`ExcelHandler.Slicer.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Slicer.cs) around line 42.