> For the complete documentation index, see [llms.txt](https://docs.rowsncolumns.app/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.rowsncolumns.app/configuration/features/pivoting-and-grouping.md).

# Pivoting and Grouping

Rowsncolumns Spreadsheet ships a full pivot-table authoring experience powered by DuckDB-WASM. Pivots run the same OOXML `pivotCacheDefinition` / `pivotTable` round-trip Excel uses, render through the existing CanvasGrid, and accept slicer-driven filtering on top of the regular sort + label-filter pipeline.

## Features at a glance

* **Row / column / value fields** with drag-to-reorder and per-field hide
* **Subtotals + grand totals** toggles (per-pivot, separate row + column)
* **Aggregation functions**: sum, count, avg, min, max, var, stddev, median, product
* **Show Values As** — `% of Grand Total`, `% of Row Total`, `% of Column Total`, `Running Total`, `Rank ↑` / `Rank ↓`
* **Field grouping** — date (year / quarter / month / week / day) or numeric (bucket size + optional offset)
* **Sort** — tri-state ↑ / ↓ / none on every field, including value-field sort
* **Label filter** — per-field popover with searchable checkbox list
* **Top N filter** — Top / Bottom × N by any value field
* **Refresh button** + auto-refresh on source mutation
* **Drilldown** — double-click any value cell to see the underlying source rows
* **Slicer ↔ pivot wiring** — slicer selection applies as a label filter to bound pivots
* **GETPIVOTDATA()** — formula-level lookup into a rendered pivot
* **XLSX round-trip** — full `pivotCacheDefinition` + `pivotTable` emission with `refreshOnLoad="1"`

## Installation

```bash
npm install @rowsncolumns/pivot
```

DuckDB-WASM is bundled as a dependency.

## Basic setup

`usePivot` returns the full callback surface; pass the same callbacks to `CanvasGrid` and to the `PivotEditor` component:

```tsx
import {
  usePivot,
  PivotEditor,
  NewPivotTableDialog,
  NewPivotTableEditor,
  createPivotTableFormats,
} from "@rowsncolumns/pivot";

const SpreadsheetWithPivot = () => {
  const [pivotTables, onChangePivotTables] = useState<PivotTable[]>([]);

  const {
    activeCell,
    activeSheetId,
    sheets,
    sheetData,
    onChangeSheets,
    onChangeSheetData,
    onChangeBatch,
    getEffectiveValue,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    cellXfsRegistry,
    sharedStringRegistry,
  } = useSpreadsheetState({
    onChangePivotTables,
    /* ... */
  });

  const getGridValues = useCallback(
    (range: SheetRange) => {
      const headers: any[] = [];
      const rows: any[][] = [];
      for (let r = range.startRowIndex; r <= range.endRowIndex; r++) {
        const row = [];
        for (let c = range.startColumnIndex; c <= range.endColumnIndex; c++) {
          row.push(getEffectiveValue(range.sheetId, r, c) ?? "");
        }
        if (r === range.startRowIndex) headers.push(...row);
        else rows.push(row);
      }
      return { headers, rows };
    },
    [getEffectiveValue],
  );

  const {
    // Create / delete / edit
    onCreatePivotTable,
    onRequestCreatePivotTable,
    onRequestEditPivotTable,
    onRequestDeletePivotTable,
    activePivotId,
    onClosePivotSettings,

    // Field management
    addRowPivot,
    addColumnPivot,
    addValue,
    removePivotField,
    changeFieldOrder,
    setAggregationFunction,

    // Source range
    updatePivotSourceRange,

    // Totals toggles
    toggleSubtotals,
    toggleRowGrandTotals,
    toggleColumnGrandTotals,

    // Sort + filter
    sortPivotField,
    removeSortPivotField,
    addFilter,
    removeFilter,

    // Show Values As + grouping
    setShowAs,
    setFieldGrouping,

    // Refresh + drilldown
    refreshPivot,
    executePivot,
    onDrillDownAtCell,

    // Slicer integration
    applySlicerSelectionToPivots,

    // Transforms
    transformPivotResults,
    transformChildRows,
    expandRowPivot,
  } = usePivot({
    locale: "en-US",
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    sharedStringRegistry,
    onChangeSheets,
    onChangePivotTables,
    onChangeSheetData,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    getGridValues,
  });

  const activePivotTable = pivotTables.find((t) => t.pivotId === activePivotId);

  return (
    <>
      <CanvasGrid
        pivotTables={pivotTables}
        onRequestEditPivotTable={onRequestEditPivotTable}
        onRequestDeletePivotTable={onRequestDeletePivotTable}
        onFilterPivot={applySlicerSelectionToPivots}
        /* ... */
      />

      {/* Pivot editor sidebar */}
      {activePivotTable && (
        <PivotEditor
          pivotTable={activePivotTable}
          updatePivotSourceRange={updatePivotSourceRange}
          addRowPivot={addRowPivot}
          addColumnPivot={addColumnPivot}
          addValue={addValue}
          removePivotField={removePivotField}
          changeFieldOrder={changeFieldOrder}
          setAggregationFunction={setAggregationFunction}
          toggleSubtotals={toggleSubtotals}
          toggleRowGrandTotals={toggleRowGrandTotals}
          toggleColumnGrandTotals={toggleColumnGrandTotals}
          sortPivotField={sortPivotField}
          removeSortPivotField={removeSortPivotField}
          addFilter={addFilter}
          removeFilter={removeFilter}
          setShowAs={setShowAs}
          setFieldGrouping={setFieldGrouping}
          refreshPivot={refreshPivot}
          onRequestClose={onClosePivotSettings}
        />
      )}

      <NewPivotTableDialog>
        <NewPivotTableEditor onSubmit={onCreatePivotTable} />
      </NewPivotTableDialog>
    </>
  );
};
```

## Authoring a pivot

### Programmatic creation

```ts
await onCreatePivotTable({
  pivotId: uuid(),
  source: {
    sheetId: 1,
    startRowIndex: 1,
    endRowIndex: 100,
    startColumnIndex: 1,
    endColumnIndex: 6,
  },
  targetPosition: { rowIndex: 1, columnIndex: 8, sheetId: 1 },
  rows: [{ field: "Region" }, { field: "Salesperson" }],
  columns: [{ field: "Quarter" }],
  values: [
    { field: "Revenue", aggFunc: "sum" },
    { field: "Units", aggFunc: "sum" },
  ],
});
```

### Right-click → "Create pivot table"

The `NewPivotTableDialog` is the in-app entry. Wire `onRequestCreatePivotTable` into the right-click context menu and the user picks the source range + target location.

## Show Values As

Switch a value field's display mode without changing the underlying aggregate. The transform runs in JS after DuckDB returns the raw `sum(...)` so any aggregator works.

```ts
// Six modes ship:
await setShowAs(pivotId, "Revenue", "as_is");          // raw aggregate (default)
await setShowAs(pivotId, "Revenue", "pct_of_total");   // PERCENT format auto-applied
await setShowAs(pivotId, "Revenue", "pct_of_row");
await setShowAs(pivotId, "Revenue", "pct_of_column");
await setShowAs(pivotId, "Revenue", "running_total");
await setShowAs(pivotId, "Revenue", "rank_desc");      // 1 = highest
await setShowAs(pivotId, "Revenue", "rank_asc");       // 1 = lowest
```

PERCENT (`0.00%`) and NUMBER (`0`) cell formats are auto-derived from the chosen mode and applied to the value + total cells.

## Field grouping

Group a date field by period or a numeric field by bucket size. Implemented as a DuckDB `EXCLUDE`-based SOURCE CTE rewrite — all downstream pivot logic sees the grouped value transparently.

```ts
// Group OrderDate column by month
await setFieldGrouping(pivotId, "OrderDate", {
  type: "date",
  period: "month",  // year | quarter | month | week | day
});

// Group Amount column into $1000 buckets starting at $0
await setFieldGrouping(pivotId, "Amount", {
  type: "numeric",
  bucketSize: 1000,
  startAt: 0,
});

// Clear the grouping (revert to raw column)
await setFieldGrouping(pivotId, "OrderDate", null);
```

## Sort

Tri-state ↑/↓/none. Sort on any row/column field by its labels, or on any value field by its aggregate.

```ts
// Sort regions A→Z
await sortPivotField(pivotId, "Region", "asc");
// Sort by total revenue (largest first)
await sortPivotField(pivotId, "Revenue", "desc");
// Clear the sort on a field
await removeSortPivotField(pivotId, "Region");
```

Value-field sort works in both layouts: grouping-only (single value column) AND column-pivoted (multiple `colVal_agg(field)` columns) — the matcher accepts both alias shapes.

## Label filter + Top N

Per-field set filter:

```ts
await addFilter(pivotId, "Region", {
  filterType: "set",
  values: ["North", "East"],
});

// Clear it
await removeFilter(pivotId, "Region");
```

Top N filter (the editor's "Top N filter" toggle does this for you):

```ts
await addFilter(pivotId, "Region", {
  filterType: "topN",
  direction: "top",      // "top" | "bottom"
  count: 10,
  byField: "Revenue",    // any value field
});
```

The filter is emitted as a subquery against the SOURCE CTE:

```sql
"Region" IN (SELECT "Region" FROM SOURCE GROUP BY "Region" ORDER BY sum("Revenue") DESC LIMIT 10)
```

## Totals toggles

```ts
await toggleSubtotals(pivotId, false);          // Hide subtotals
await toggleRowGrandTotals(pivotId, false);     // Hide bottom Grand Total row
await toggleColumnGrandTotals(pivotId, false);  // Hide right Grand Total column
```

Default is `true` for all three (Excel default).

## Refresh + auto-refresh

```ts
await refreshPivot(pivotId);  // Manual — re-run against current source data
```

Auto-refresh fires when `sourceDataVersion` bumps — typically when a cell in the pivot's source range changes. The Refresh icon in `PivotEditor` is a convenience for the manual case.

## Drilldown

Double-click any value cell to see the underlying source rows. Wire `onDrillDownAtCell` to your own modal / sheet / panel:

```ts
const handleDrilldown = async (cell: { rowIndex: number; columnIndex: number }) => {
  const result = await onDrillDownAtCell(pivotId, cell);
  // result.rows is an array of source rows that contributed to that cell
  openDrilldownModal(result);
};
```

## Slicer integration

Wire the pivot slicer's `onFilterPivot` callback directly to `applySlicerSelectionToPivots`:

```tsx
<CanvasGrid
  onFilterPivot={applySlicerSelectionToPivots}
  /* ... */
/>
```

Slicer selection then routes through the pivot's filter pipeline — checking/unchecking values applies a `filterType: "set"` filter on the pivot's `fieldName`. Empty selection clears the filter (shows all values).

## GETPIVOTDATA()

Look up a value cell in a rendered pivot from any formula:

```
=GETPIVOTDATA("Revenue", $H$1, "Region", "North", "Quarter", "Q3")
```

The function scans the pivot's row + column header bands for the supplied `(field=item)` tuples and returns the matching cell. See [Formula evaluation](/getting-started/formula-evaluation.md) for syntax details.

## XLSX round-trip

Pivot tables export to a full `xl/pivotCache/pivotCacheDefinitionN.xml` + `xl/pivotTables/pivotTableN.xml` pair with `refreshOnLoad="1"` so Excel rebuilds the cache from the live source on open. Positions, row/column/value fields, aggregation function, sort state, and filter state all round-trip.

Calculated fields / calculated items and the pivot-backed slicer OLAP cube cache export are still deferred.

## PivotTable type

```ts
type PivotTable = {
  pivotId: string | number;
  source?: SheetRange;
  targetPosition: SheetCoordinate;
  pivotRange?: SheetRange;  // current rendered range; the engine sets this
  rows: PivotGroup[];
  columns: PivotGroup[];
  values: PivotValue[];
  filters?: Record<string, any>;
  sortModel?: Array<{ field: string; sort: "asc" | "desc" }>;
  // Totals toggles — default true if omitted
  showSubtotals?: boolean;
  showRowGrandTotals?: boolean;
  showColumnGrandTotals?: boolean;
};

type PivotGroup = {
  field: string;
  displayName?: string;
  sortOrder?: SortOrder;
  grouping?:
    | { type: "date"; period: "year" | "quarter" | "month" | "week" | "day" }
    | { type: "numeric"; bucketSize: number; startAt?: number };
};

type PivotValue = {
  field: string;
  displayName?: string;
  aggFunc: "sum" | "count" | "avg" | "min" | "max" | "var" | "stddev" | "median" | "product";
  showAs?:
    | "as_is"
    | "pct_of_total"
    | "pct_of_row"
    | "pct_of_column"
    | "running_total"
    | "rank_desc"
    | "rank_asc";
};
```

## Performance notes

* DuckDB-WASM runs the PIVOT operator entirely in the browser. Datasets up to a few million rows handle comfortably.
* Filter evaluation happens at the SQL layer (`IN (SELECT …)` subqueries) — no JS-side row scans for set or Top N filters.
* Show Values As + value-field sort run as post-aggregation JS transforms — they operate on the already-pivoted result set, not the raw source.
* `refreshOnLoad="1"` in the XLSX export means Excel rebuilds the cache from the live source on open — saved files don't carry stale aggregate values.