-
Notifications
You must be signed in to change notification settings - Fork 532
excel pivottable add
zmworm edited this page Apr 9, 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 | |
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 |
- | Standard deviation |
var |
- | Variance |
# 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.38