excel pivottable add

Excel: Pivot Table - add

Create a pivot table from a data range.

Path: /{SheetName}

Properties

Property	Required	Accepted Values	Description
source	yes	range ref	Source data range (e.g., Sheet1!A1:E100)
position		cell ref	Top-left cell for the pivot table (e.g., G1)
rows		comma-separated	Row fields (e.g., Region,Category)
cols		comma-separated	Column fields (e.g., Year,Quarter)
values		field:func pairs	Value fields with aggregation (e.g., Sales:sum,Qty:count); supports inline showDataAs: Sales:sum:percent_of_row
filters		comma-separated	Filter fields (e.g., Status,Priority)
aggregate		comma-separated funcs	Positional override of aggregation functions (e.g., sum,count)
showDataAs		comma-separated tokens	Positional override of showAs: normal, percent_of_total, percent_of_row, percent_of_col, running_total
sort		asc | desc | locale | locale-desc	Axis sort applied at render time (not persisted as OOXML sortType — v2 candidate)
grandTotals		both | rows | cols | none	Row/column grand total visibility (default: both)
subtotals		on | off	Subtotal rows for row/col fields (default: on)
name		text	Pivot table name
layout		compact (default), outline, tabular	Row label layout form
repeatLabels		true | false	Repeat outer row labels on every data row (default: false). Excel's "Repeat All Item Labels" in Report Layout menu.
blankRows		true | false	Insert blank line after each outer group (default: false). Excel's "Insert Blank Line After Each Item" in Report Layout menu.
grandTotalCaption		text	Custom label for the grand total row/column (default: Grand Total)
style		style name	Pivot table style (default: PivotStyleLight16)

Aggregation Functions

Function	Aliases	Description
sum	-	Sum of values
count	-	Count of values
average	avg	Average of values
max	-	Maximum value
min	-	Minimum value
product	-	Product of values
stddev	stdDev	Standard deviation (sample)
stddevp	stdDevp	Standard deviation (population)
var	-	Variance (sample)
varp	-	Variance (population)
countnums	countNums	Count of numeric values

Date Auto-Grouping

Date columns in the source range are automatically grouped using native Excel fieldGroup XML. Excel renders these as Year > Month > Day hierarchy.

Multi-Field Support

• Up to N row fields and N col fields (hierarchical subtotals in compact mode)
• Multiple data fields: values=Sales:sum,Qty:count,Price:average
• Cross-product rendering for 2+ rows x 2+ cols x K data fields
• Number format inherited from source column
• Page filter fields rendered above the pivot table area

Examples

# Basic pivot table
officecli add data.xlsx /Sheet1 --type pivottable \
  --prop source="Sheet1!A1:E100" \
  --prop rows=Region,Category \
  --prop values="Sales:sum"

# Full pivot table with columns, filters, and style
officecli add data.xlsx /Sheet1 --type pivottable \
  --prop source="Sheet1!A1:F500" \
  --prop position=H1 \
  --prop rows=Region \
  --prop cols=Year \
  --prop values="Sales:sum,Qty:count,Price:average" \
  --prop filters=Status \
  --prop name="SalesPivot" \
  --prop style=PivotStyleMedium9

# Pivot table with multiple aggregations
officecli add data.xlsx /Sheet2 --type pivottable \
  --prop source="Sheet1!A1:D200" \
  --prop rows=Department \
  --prop values="Salary:avg,Salary:max,Headcount:count"

# Pivot table with showDataAs, grandTotals, subtotals
officecli add data.xlsx /Sheet1 --type pivottable \
  --prop source="Sheet1!A1:E100" \
  --prop rows="Region,Category" \
  --prop values="Sales:sum:percent_of_total" \
  --prop grandTotals=rows \
  --prop subtotals=off \
  --prop sort=asc

See Also

• Pivot Table - Overview of pivot table operations
• Pivot Table get - Returned attributes
• add - Excel - All addable Excel elements

---

Based on OfficeCLI v1.0.64