Skip to main content
prompt GP ChatGPT

pivot — Claude Code prompt: what it does, how to install

Instructions for building native Excel pivot tables with add_pivot or summarizing data grids with dynamic SUMIFS formulas.

Apache-2.0 ★ 12,746 updated 2026-10-29

Install into Claude Code · Manually: put the files in place All methods and platforms ↓
.claude/commands/pivot.md
Open on GitHub

What pivot does

This prompt directs Claude Code on structuring summary reports in spreadsheets. The assistant runs add_pivot when interactive Excel tables with field dragging are needed, or falls back to SUMIFS and COUNTIFS grids when cells must remain editable formulas.

The add_pivot call accepts up to eight row dimensions, up to four page filters, and at most one column dimension. If a column field is defined, values can hold only one entry. The header row must contain non-empty, non-duplicate titles, and the target cell must stay outside the source region.

After saving, the output range is protected against manual set_cell edits. The source dataset is capped at 10,000 data rows and 200 columns.

What it helps with

  • generate an interactive Excel pivot table
  • aggregate sales by category using SUMIFS formulas
  • refresh calculated pivot ranges on sheet updates
  • compute running totals or percentage share of total

Example prompts for the agent

  • Create a pivot table from range A1:D500 placing it at F1
  • Build a two-dimensional cross-tab table with SUMIFS formulas
  • Refresh all pivot tables on sheet s1 after data changes

How to install pivot

Manually: put the files in place

.claude/commands/pivot.md

Questions about pivot

How to install pivot in Claude Code?

Save the prompt file to .claude/commands/pivot.md.

When should add_pivot be chosen over formula tables?

Choose add_pivot for native Excel interactivity, while formulas suit irregular data requiring downstream cell calculations.

What are the limits of the add_pivot operation?

The source range cannot exceed 10,000 rows by 200 columns, and target cells must not overlap the source data.

Source text

pivot.md · Apache-2.0
# Pivot table guide (pivot)

## When to pivot vs. formula aggregation

- The user asks for a "pivot table", or wants the result interactive in Excel (drag fields, refresh) → **add_pivot** (real pivot table).
- They just want "summarize by dimension" and will keep computing/charting from the summary values → a formula aggregation table also works (see the fallback plan): the result is real cell formulas that stay computable.

## Creating a pivot table (add_pivot)

`{op:"add_pivot", sheetId, sourceRange:"A1:F1000", targetCell:"H1", targetSheetId?, rowFields, columnField?, pageFields?, values:[{field, agg, showDataAs?, baseField?, baseItem?, name?}], name?}`

- **The first row of sourceRange must be headers** (non-empty and mutually unique); rowFields/columnField/values.field all use the header text.
- rowFields: row dimensions, 1–8 (string or array, **outermost first**); with multiple levels, rows group hierarchically and per-level subtotal rows are added automatically. columnField: column dimension (optional, at most 1; **when columnField is given, values may only have 1 entry**).
- values: `{field:"Amount", agg:"sum"|"count"|"average"|"max"|"min"|"product"|"countNums", showDataAs?, baseField?, baseItem?, name?}`, 1–8 entries; a values field cannot also be a row/column/page dimension; `name` overrides the "Sum of Amount" caption.
- showDataAs (value display mode, optional): `"percentOfTotal"` (% of grand total) | `"percentOfRow"` | `"percentOfCol"` (% of row/column total) | `"percentOfParentRow"` | `"percentOfParentCol"` (% of the next level up) | "percentOfParent" (% of the base field's parent-level total; `baseField` names a row/column field) | `"difference"` (value − base item; blank on the base item and totals) | `"percentDiff"` ((value−base)/base) | `"percentOf"` (value/base) | "runTotal" (running total along the base field's items, restarting per outer member / other-axis group) | `"percentOfRunningTotal"` | `"rankAscending"` / `"rankDescending"` (RANK.EQ-style, ties share the smaller rank) | `"index"`; percent modes display with the 0.00% format by default.
- baseField/baseItem (needed by the base-field showDataAs modes): `baseField` must be a row/column dimension header (e.g. a rowFields entry); `baseItem` is a member label of that field, required by difference / percentDiff / percentOf.
- pageFields (report filters, optional, ≤4): header names or `{field, item}`; they render as "Field | (All)" rows above the table (plus a blank row) and a chosen `item` restricts the whole report to matching source rows.
- targetCell: top-left of the output region, **must not overlap the source region** (put it in blank space right of the source, or use targetSheetId to place it on a summary sheet).
- How it takes effect: on apply, aggregated results (including subtotal and Grand Total rows/columns) are written directly to the target cells and are immediately visible; on save, native pivot parts are written (refreshOnLoad), so **the file opens in Excel as a live interactive pivot table**.
- Limits: source region ≤ 10,000 data rows × 200 columns; each row-dimension level ≤ 10,000 distinct members; the same batch cannot also do row/column insertion/deletion or sheet management on the source/target sheet (save first).
- The output region is protected after saving (not directly editable) — don't stack set_cell on top of it.

```json
{
  "op": "add_pivot",
  "sheetId": "s1",
  "sourceRange": "A1:D500",
  "targetCell": "F1",
  "rowFields": ["Region", "Product"],
  "values": [
    { "field": "Sales", "agg": "sum" },
    { "field": "Sales", "agg": "sum", "showDataAs": "percentOfTotal" }
  ],
  "name": "RegionProductSummary"
}
```

## Refreshing existing pivot tables (refresh_pivot)

`{op:"refresh_pivot", sheetId}` — recomputes the data areas of all pivot tables on the sheet and writes them back. Use it after the source data has changed to bring pivot results up to date.

- When **new categories** appear in the source data, the layout grows automatically: new members are appended at the end of their level (with new subtotal rows for multi-level layouts) and the output region expands — but the area the growth occupies must be empty, otherwise it errors and asks you to clear it first.
- Cases that still fail with explicit errors: renamed headers / moved data sources, calculated fields, grouped fields, value filters, and growth of compact (non-tabular) layouts. Other edge cases are in the data guide's "Pivot tables" section.

## Fallback plan: formula aggregation table (for irregular data or when live formulas are needed)

Compute the summary grid directly with `SUMIFS`/`COUNTIFS`/`AVERAGEIFS`:

**Single-dimension summary** (sales by region):

```json
[
  { "op": "set_range", "sheetId": "s2", "range": "A1:B1", "values": [["Region", "Sales"]] },
  {
    "op": "set_range",
    "sheetId": "s2",
    "range": "A2:A5",
    "values": [["East"], ["South"], ["North"], ["West"]]
  },
  {
    "op": "set_formula",
    "sheetId": "s2",
    "address": "B2",
    "formula": "=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,A2)"
  }
]
```

(Fill the B2 formula down to B5; point the criteria ranges at the source table's region/amount columns.)

**Two-dimension cross-tab** (region × month): row headers hold regions, column headers hold months, and each intersection uses a double-criteria `=SUMIFS(src!amount, src!region, $rowHeader, src!month, colHeader$)` (note the `$` locking directions for filling).

**Deduplicating dimension values**: when the source's dimension values are unknown, first `read_range` the source column, write the deduplicated dimension values into the row/column headers in your proposal, then attach the SUMIFS.

## Companion steps

- Once the summary table is computed, add a chart per the `charts` guide (category comparison → column, share → pie).
- Apply `financial-formatting` currency formats to amount columns.
- For formula aggregation tables, the total row uses `=SUM(...)`, bold + light gray fill (add_pivot ships its own Grand Total).

© genspark-ai · Apache-2.0 · genspark-ai/genoffice

Similar prompts

All prompts for Office documents →

From the same set65

The whole set →
policy-compliance policy-compliance generates privacy policies and internal compliance documents with tick-box checklists in Word and Excel. Not legal advice — needs a lawyer's review. CX Codex +1 ★ 12,746repo stars nda-drafter nda-drafter drafts one-way or mutual non-disclosure agreements in Word format, complete with clause notes and flagged risk points. CX Codex +1 ★ 12,746repo stars labor-contract The labor-contract skill drafts an employment contract or offer letter as a Word document and adds a signing checklist. Use it when hiring terms need to be laid out. CX Codex +1 ★ 12,746repo stars data data documents the xlsx operations Claude can run: hyperlinks, AutoFilter, conditional formats, validation, notes and pivots. Useful when a workbook is edited by prompts, not by hand. GP ChatGPT ★ 12,746repo stars deck This prompt guides Claude Code through deck-wide changes to slide size, theme, text, footers, and sections. It calls for clarification when the requested scope is unclear. GP ChatGPT ★ 12,746repo stars table Tells the agent when to turn a range into a real Excel table and when to fake the look with plain formatting instead. GP ChatGPT ★ 12,746repo stars charts This Claude Code prompt creates and edits spreadsheet charts. It helps select a chart type and specify the source data. GP ChatGPT ★ 12,746repo stars table table is a Claude Code prompt for editing existing slide tables, from cell text and merges to sizing and styles. Invoke it to change a table in a presentation. GP ChatGPT ★ 12,746repo stars

Project sponsors

Companies that keep this analytics open