excel pivottable set

Excel: Pivot Table - set

Modify an existing pivot table's field layout, aggregation, display, and styling.

Path: /{SheetName}/pivottable[N]

Properties

Field Areas

Property	Accepted Values	Description
rows	comma-separated field names or column indices	Replace row-axis fields (e.g., Region,Category)
cols / columns	comma-separated field names or column indices	Replace column-axis fields
values	comma-separated Field[:func[:showAs]]	Replace value (data) fields. Default func is sum. Accepts Get readback format for round-trip.
filters	comma-separated field names or column indices	Replace page (filter) fields
source	range ref	Change source data range (e.g., Sheet1!A1:F200). Refreshes cache headers before field validation.

Singular aliases accepted: row, col, filter, value. Legacy rowFields/colFields/valueFields/filterFields also accepted.

Axis conflict resolution: A field cannot live in two axes at once. When one axis claims a field, the field is automatically dropped from other axes in the same set call.

Aggregation & Display

Property	Accepted Values	Description
aggregate	comma-separated funcs, positional with values	Override per-value-field aggregate function without restating values=
showDataAs	comma-separated tokens, positional with values	Override per-value-field display calculation
dataField{N}.showAs	single token	Per-data-field display calculation (round-trip with Get)

Aggregation functions: sum, count, average (avg), max, min, product, countNums, stdDev, stdDevp, var, varp

showDataAs tokens: normal, percent_of_total, percent_of_row, percent_of_col, running_total

Layout & Visibility

Property	Accepted Values	Description
grandTotals	both, rows, cols, none	Grand total visibility
rowGrandTotals / colGrandTotals	true / false	Per-axis grand total toggle
subtotals	on / off	Subtotal rows/columns for hierarchical fields
sort	asc, desc, locale, locale-desc, none	Sort axis labels
layout	compact, outline, tabular	Row label layout form (compact = single indent column, outline/tabular = one column per row field)
repeatLabels	true / false	Repeat outer row labels on every data row (Excel's "Repeat All Item Labels")
blankRows	true / false	Insert blank line after each outer group (Excel's "Insert Blank Line After Each Item")
grandTotalCaption	text	Custom label for the grand total row/column (default: Grand Total)
name	text (max 255 chars)	Pivot table name (unique within workbook)
style	style name	Pivot table style (e.g., PivotStyleLight16, PivotStyleMedium9)

Behavior

• Setting any of rows, cols, values, or filters rebuilds the affected field areas in place and re-renders materialized cells.
• Setting source refreshes the pivot cache headers, then validates that existing field references are still in range.
• Fields are deduped within and across axes — duplicate field names are silently dropped.
• Field names are matched case-insensitively with Unicode NFC normalization.
• Unknown properties trigger a fuzzy suggestion (e.g., Did you mean 'grandTotals'?).

Examples

# Change row and value fields
officecli set data.xlsx /Sheet1/pivottable[1] --prop rows=Region --prop values=Sales:sum

# Multiple data fields with different aggregations
officecli set data.xlsx /Sheet1/pivottable[1] --prop values="Sales:sum,Qty:count,Price:average"

# Override aggregation without restating values
officecli set data.xlsx /Sheet1/pivottable[1] --prop aggregate=average

# Show values as percentage of total
officecli set data.xlsx /Sheet1/pivottable[1] --prop showDataAs=percent_of_total

# Per-data-field display calculation
officecli set data.xlsx /Sheet1/pivottable[1] --prop dataField1.showAs=percent_of_row

# Sort axis labels descending
officecli set data.xlsx /Sheet1/pivottable[1] --prop sort=desc

# Hide grand totals and subtotals
officecli set data.xlsx /Sheet1/pivottable[1] --prop grandTotals=none --prop subtotals=off

# Change source range
officecli set data.xlsx /Sheet1/pivottable[1] --prop source="Sheet1!A1:G500"

# Change style
officecli set data.xlsx /Sheet1/pivottable[1] --prop style=PivotStyleMedium9

See Also

• Pivot Table - Overview, query, remove
• Pivot Table add - Create pivot tables
• Pivot Table get - Returned attributes
• Excel Reference - All Excel elements

---

Based on OfficeCLI v1.0.64