-
Notifications
You must be signed in to change notification settings - Fork 528
excel pivottable add
zmworm edited this page Apr 13, 2026
·
55 revisions
Create a pivot table from a data range.
Path: /{SheetName}
| 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) |
| 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 columns in the source range are automatically grouped using native Excel fieldGroup XML. Excel renders these as Year > Month > Day hierarchy.
- 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
# 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
- Pivot Table - Overview of pivot table operations
- Pivot Table get - Returned attributes
- add - Excel - All addable Excel elements
Based on OfficeCLI v1.0.45