# PivotTable from a range

Excel's Insert > PivotTable over a block of cells, on the same pivot engine the grid uses for its own pivot mode. The sheet keeps the definition - the source block, where the result goes, and which field is a row, a column or a measure - and writes the result as plain cells in one undo, so it can be formatted, charted, printed and saved to an .xlsx like any other block. Show Details writes the source rows behind a cell to a sheet of their own, and Refresh rebuilds it from the source; opening the dialog from inside one edits it.

Requires `@svgrid/enterprise` (and `@svgrid/grid`). Category: Spreadsheet. Live demo: https://svgrid.com/demos/487-sheet-pivot-range/

Install: `npm i @svgrid/grid @svgrid/enterprise` (Svelte 5).

## About this example

Excel's Insert > PivotTable over a block of cells in a Svelte 5 spreadsheet, on the same pivot engine the grid uses for its own pivot mode. The sheet keeps the definition, the source block, where the result goes and which field is a row, a column or a measure; the result is plain cells written in one undo, so it can be formatted, charted, printed and saved to an .xlsx like any other block. Refresh rebuilds it from the source. This sheet opens with sales by region down the rows and quarters across the columns. Enterprise, in @svgrid/enterprise.

Excel's Insert > PivotTable over a block of cells, on the same pivot engine the grid uses for its own pivot mode.

The sheet keeps the DEFINITION: the source block, where the result goes, and which field is a row, a column or a measure. The RESULT is plain cells, written in one undo, so everything else in the shell works on it: format it, chart it, print it, save it to an .xlsx. Refresh rebuilds the block from the source, which is what a pivot over live cells owes you.

```text
Insert > PivotTable   the dialog on the selected block, or on the
                      pivot the cursor is already in
Insert > Refresh      rebuild the one here
```

This sheet opens with a pivot already written at H1: sales by region down the rows, quarters across the columns, the amounts summed.

Try: change an Amount in E2:E25, put the cursor in the pivot and press Refresh. Click a number in the pivot and press Show Details for the rows behind it, on a sheet of their own. Open the dialog from inside it and move Rep into Rows under Region, or make Amount an Average. Select A1:E25 and build a second one somewhere else.

## Imports, features and API used

- Imports: `@svgrid/enterprise`

## Frequently asked questions

### Is the result live, like Excel's?

The definition is live, the cells are cells. The sheet remembers the source block, the axes and the measures; Refresh rebuilds the block from the source and clears whatever the last one wrote. Between refreshes the result is ordinary cells, which is what lets you format, chart, print and save it.

### Can I change the fields after the fact?

Yes. Put the cursor inside the written block and press Insert > PivotTable: the dialog opens on that pivot, with the fields as they stand. Move a field between Rows, Columns and Values, change how a value is summarised, or turn the grand total and the subtotals off.

### What happens when rows are inserted above it?

The definition moves with the cells: the source block, the target cell and the rectangle the last refresh wrote all shift. A pivot whose source is deleted, or whose target cell is, is dropped.

### Can I see the rows behind a number?

Yes: put the cursor in the cell and press Insert > Show Details, Excel's drill-down. The source rows behind that cell are written to a sheet of their own with the field names across the top. A cell in a subtotal line opens its whole group, one in the grand total column opens the whole line, and the grand total opens every row.

## Related documentation

- [The spreadsheet shell](https://svgrid.com/docs/help/cells/spreadsheet-shell.md)
- [Data tools: filter, sort, clean up, solve, pivot](https://svgrid.com/docs/help/sheet/data-tools.md)

## Related articles

- [Spreadsheet-Style Cell Range Selection in SvGrid](https://svgrid.com/blog/cell-range-selection/) - How to enable drag-to-select cell ranges, read live selection state, and build a status-bar footer that sums and averages the selected values.
- [A Fill Handle (Drag to Fill) in SvGrid](https://svgrid.com/blog/fill-handle-drag-to-fill/) - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
- [Copy a Cell Range to the Clipboard in SvGrid](https://svgrid.com/blog/copy-cell-range-to-clipboard/) - How SvGrid copies selected cell ranges as tab-separated values that paste cleanly into Excel and Google Sheets, plus headers, programmatic copy, and format control.

## Source code (487-sheet-pivot-range.svelte)

```svelte
<script lang="ts">
  /**
   * 487. PivotTable from a range
   * ----------------------------
   * Excel's Insert > PivotTable over a block of cells, on the same pivot
   * engine the grid uses for its own pivot mode.
   *
   * The sheet keeps the DEFINITION: the source block, where the result
   * goes, and which field is a row, a column or a measure. The RESULT is
   * plain cells, written in one undo, so everything else in the shell
   * works on it: format it, chart it, print it, save it to an .xlsx.
   * Refresh rebuilds the block from the source, which is what a pivot over
   * live cells owes you.
   *
   *   Insert > PivotTable   the dialog on the selected block, or on the
   *                         pivot the cursor is already in
   *   Insert > Refresh      rebuild the one here
   *
   * This sheet opens with a pivot already written at H1: sales by region
   * down the rows, quarters across the columns, the amounts summed.
   *
   * Try: change an Amount in E2:E25, put the cursor in the pivot and press
   * Refresh. Click a number in the pivot and press Show Details for the
   * rows behind it, on a sheet of their own. Open the dialog from inside it
   * and move Rep into Rows under Region, or make Amount an Average. Select
   * A1:E25 and build a second one somewhere else.
   */
  import {
    SvSheet, createWorkbook, createSheetDocument,
    pivotBlock, pivotWrittenRect, pivotId, type SheetPivot,
  } from '@svgrid/enterprise'

  const regions = ['North', 'South', 'EMEA']
  const reps = ['Ada', 'Grace', 'Linus']
  const quarters = ['Q1', 'Q2', 'Q3', 'Q4']
  const products = ['Licence', 'Support']

  // A plain sales log: one row per deal, the shape a pivot is made for.
  const log: string[][] = []
  let seed = 7
  const next = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648
  for (const region of regions) {
    for (const quarter of quarters) {
      for (const product of products) {
        log.push([
          region,
          reps[Math.floor(next() * reps.length)]!,
          quarter,
          product,
          String(4000 + Math.round(next() * 9000)),
        ])
      }
    }
  }

  const header = ['Region', 'Rep', 'Quarter', 'Product', 'Amount']
  const cells: string[][] = [header, ...log]

  // The pivot this sheet opens with. Its block is computed here so the
  // document arrives with the summary already in the cells; from then on
  // Refresh rewrites it through the shell.
  const source = [0, 0, cells.length - 1, 4] as const
  const pivot: SheetPivot = {
    id: pivotId(),
    source: source as unknown as SheetPivot['source'],
    target: { row: 0, col: 7 },
    rows: ['Region'],
    cols: ['Quarter'],
    values: [{ field: 'Amount', agg: 'sum' }],
  }
  const valueAt = (r: number, c: number) => {
    const text = cells[r]?.[c] ?? ''
    const n = Number(text)
    return text !== '' && Number.isFinite(n) ? n : text
  }
  const textAt = (r: number, c: number) => cells[r]?.[c] ?? ''
  const block = pivotBlock(pivot, valueAt, textAt)
  pivot.written = pivotWrittenRect(pivot, block)
  block.forEach((line, i) => {
    const row = (cells[pivot.target.row + i] ??= [])
    line.forEach((text, j) => { row[pivot.target.col + j] = text })
  })
  // A workbook takes a dense block: the gap between the log and the pivot,
  // and any short row, has to be empty strings rather than holes.
  const width = cells.reduce((w, row) => Math.max(w, row.length), 0)
  for (const row of cells) {
    for (let c = 0; c < width; c += 1) row[c] ??= ''
  }

  const wb = createWorkbook([{ name: 'Sales', cells }])
  const doc = createSheetDocument({ workbook: wb })
  const sheet = doc.get('Sales')
  const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
  sheet.formats.set([[0, 0, 0, 4]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
  sheet.formats.set([[1, 4, cells.length - 1, 4]], { numFmt: '$#,##0' }, at)
  // The written block: its header row, and the money inside it.
  sheet.formats.set([[0, 7, 0, 12]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
  sheet.formats.set([[1, 8, pivot.written[2], 12]], { numFmt: '$#,##0' }, at)
  sheet.formats.set([[pivot.written[2], 7, pivot.written[2], 12]], { bold: true, border: { top: { width: 1 } } }, at)
  sheet.widths.A = 90
  sheet.widths.H = 120
  sheet.freeze = { rows: 1, cols: 0 }
  sheet.pivots = [pivot]
</script>

<SvSheet document={doc} height="100%" rows={30} columns={14} />
```

[View this example on GitHub](https://github.com/sv-grid/sv-grid/blob/main/examples/src/demos/487-sheet-pivot-range.svelte)