> 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.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.rowsncolumns.app/configuration/features/pivoting-and-grouping.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.