-
Notifications
You must be signed in to change notification settings - Fork 533
excel pivottable set
zmworm edited this page Apr 29, 2026
·
21 revisions
Modify an existing pivot table's field layout, aggregation, display, and styling.
Path: /{SheetName}/pivottable[N]
| 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.
| 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
| 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) |
- Setting any of
rows,cols,values, orfiltersrebuilds the affected field areas in place and re-renders materialized cells. - Setting
sourcerefreshes 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'?).
# 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
- 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