Pivot Tables in a JavaScript Spreadsheet — Live Cross-Tab Summaries in a Few Lines

Data validation stopped bad records at the cell. Sort & filter let users find their way around the ones that got in. The next question is the one every spreadsheet eventually gets asked: so what does it add up to? Sales by region and product. Headcount by department and office. Order counts by quarter. In Excel that’s a pivot table — and in ReoGrid Web (since v1.4) it’s one API call that stays live as the data underneath it changes.

Pivot tables are a Pro feature (pricing). Creating and editing them requires Pro; a Pro-authored document still renders in the free Lite tier.

A pivot in one call

Say columns A–E hold 200 sales records — Region, Product, Quarter, Channel, Sales — with headers on row 0. One call cross-tabulates them:

import { createReogrid } from '@reogrid/pro';

const { worksheet: ws } = createReogrid('#grid');

const pivot = ws.createPivot({
  source: { row: 0, col: 0, rows: 201, columns: 5 }, // A1:E201, first row = headers
  anchor: { row: 0, column: 6 },                      // output starts at G1
  rows:    [{ field: 'Region' }],
  columns: [{ field: 'Product' }],
  values:  [{ field: 'Sales', agg: 'sum', numberFormat: '#,##0' }],
});

Starting at G1, the grid writes out a block: one row per region, one column per product, each body cell the sum of Sales for that region–product pair, with a grand-total column on the right and a grand-total row at the bottom. The output cells are locked — a user can’t type over a computed total — and the block is a live object, not a paste: edit any Sales cell in the source and the affected totals recompute on the spot.

Fields are referenced by header name, not by column index. The pivot reads its field list from source.row, so { field: 'Region' } means “the column whose header cell says Region” — reorder your source columns and the definition still holds.

Anatomy of a PivotDefinition

Everything createPivot accepts, in one place:

The agg on each value field is one of seven aggregations: 'sum', 'count', 'countNumbers', 'average', 'max', 'min', 'product'. Two are easy to mix up: count counts every non-empty record, countNumbers only the numeric ones — the same distinction as Excel’s COUNTA vs COUNT.

A value field can carry its own presentation, and you can aggregate the same source field twice:

values: [
  { field: 'Sales', agg: 'sum',     caption: 'Total',    numberFormat: '#,##0' },
  { field: 'Sales', agg: 'average', caption: 'Avg sale', numberFormat: '#,##0.0' },
],

caption overrides the default "Sum of Sales"-style header, and numberFormat applies a number format to every value cell of that measure — the totals come out as 12,847, not 12847.

It stays live — mostly by itself

This is the property that separates a pivot object from a one-shot “summarize this range” helper. The worksheet tells the pivot engine whenever a cell changes; if the cell falls inside a pivot’s source range, that pivot recomputes and rewrites its output block. Type a new number into any Sales cell and the region’s subtotal, the product’s column total, and the grand total all update before you’ve clicked away.

One edit the pivot can’t see by itself: structural changes. The definition stores source and anchor as fixed ranges, and inserting or deleting rows/columns doesn’t shift them. After a structural edit, tell the pivot to re-read its source:

ws.insertRows(5, 3);       // structural edit — the stored ranges didn't move
pivot.refresh();           // re-read the source range and re-render

Value edits inside the source: automatic. Inserts and deletes: one refresh().

Driving it from code

createPivot returns a PivotHandle — a thin, stateless handle that delegates back to the worksheet:

pivot.update({ columns: [{ field: 'Quarter' }] }); // merge changes in + recompute
pivot.refresh();                                   // force a recompute from source
pivot.definition;                                  // current resolved definition
pivot.bounds;                                      // rectangle the output occupies, or null
pivot.remove();                                    // clear the block, unlock its cells

update takes a partial definition and merges it — pass just the piece you’re changing. That’s what makes a field-picker UI cheap to build: three <select> elements and every change is one update call.

rowSelect.onchange = () =>
  pivot.update({ rows: [{ field: rowSelect.value }] });

aggSelect.onchange = () =>
  pivot.update({ values: [{ field: 'Sales', agg: aggSelect.value, numberFormat: '#,##0' }] });

bounds answers “how big did the output get” — useful for scrolling the block into view or styling around it. And everything on the handle exists on the worksheet keyed by id, for when you’re holding a sheet but not the handle: ws.getPivot(id), ws.getPivots(), ws.getPivotBounds(id), ws.updatePivot(id, partial), ws.refreshPivot(id), ws.removePivot(id).

Filters and grand totals

A report filter restricts which source records feed the pivot at all — the Excel “page filter” box. include lists the allowed values:

const pivot = ws.createPivot({
  source: { row: 0, col: 0, rows: 201, columns: 5 },
  anchor: { row: 0, column: 6 },
  rows:    [{ field: 'Region' }],
  columns: [{ field: 'Quarter' }],
  values:  [{ field: 'Sales', agg: 'sum', numberFormat: '#,##0' }],
  filters: [{ field: 'Channel', include: ['Online'] }],  // online sales only
});

// One update swaps the whole report to retail
pivot.update({ filters: [{ field: 'Channel', include: ['Retail'] }] });

This composes naturally with auto-filter on the source table, but they’re independent: the auto-filter hides rows from the user’s view, while a pivot filter decides which records enter the aggregation. Hiding a row does not remove it from the pivot.

Both grand totals default to on. For a tighter block — say the pivot feeds a chart and you don’t want the totals skewing the scale — switch them off:

pivot.update({ showRowGrandTotals: false, showColumnGrandTotals: false });

Worked example: a sales dashboard corner

Here’s the complete build — 200 seeded sales records in A–E, a pivot in G, and two dropdowns that reconfigure it live:

import { createReogrid } from '@reogrid/pro';

const { worksheet: ws } = createReogrid('#grid');

ws.suspendRender(); // batch the initial build

// ── Source table: Region / Product / Quarter / Channel / Sales ──
const HEADERS = ['Region', 'Product', 'Quarter', 'Channel', 'Sales'];
HEADERS.forEach((h, c) => {
  ws.cell(0, c).setValue(h).setStyle({
    bold: true, backgroundColor: '#1e3a5f', color: '#e2e8f0', textAlign: 'center',
  });
});

const REGIONS  = ['East', 'West', 'North', 'South'];
const PRODUCTS = ['Apple', 'Banana', 'Cherry'];
const QUARTERS = ['Q1', 'Q2', 'Q3', 'Q4'];
const CHANNELS = ['Online', 'Retail'];
const pick = (arr: readonly string[]) => arr[Math.floor(Math.random() * arr.length)];

for (let r = 1; r <= 200; r++) {
  ws.setCellInput(r, 0, pick(REGIONS));
  ws.setCellInput(r, 1, pick(PRODUCTS));
  ws.setCellInput(r, 2, pick(QUARTERS));
  ws.setCellInput(r, 3, pick(CHANNELS));
  ws.setCellInput(r, 4, String(Math.round(10 + Math.random() * 90)));
}

ws.setFrozenRows(1);
ws.resumeRender();

// ── The pivot: region × product, sum of sales ──
const pivot = ws.createPivot({
  source: { row: 0, col: 0, rows: 201, columns: 5 },
  anchor: { row: 0, column: 6 },
  rows:    [{ field: 'Region' }],
  columns: [{ field: 'Product' }],
  values:  [{ field: 'Sales', agg: 'sum', numberFormat: '#,##0' }],
});

// ── Two dropdowns drive the whole thing ──
const colField = document.getElementById('col-field') as HTMLSelectElement;
colField.onchange = () =>
  pivot.update({ columns: colField.value ? [{ field: colField.value }] : [] });

const agg = document.getElementById('agg') as HTMLSelectElement;
agg.onchange = () =>
  pivot.update({
    values: [{ field: 'Sales', agg: agg.value as any, numberFormat: '#,##0' }],
  });

Change the column dropdown from Product to Quarter and the block re-renders as a region-by-quarter matrix. Switch the aggregation to average and every cell, subtotal, and grand total flips from sums to means. Edit any Sales cell in the source and watch the totals move. The pivot table demo runs this exact setup live.

One practical note: the pivot reads numbers through the cell’s numeric value and text through its input string, so seed the source with setCellInput (as above) or setValue — either works. Rows whose measure isn’t numeric still count for count, but not for sum/average/countNumbers.

Where Lite ends and Pro begins

Pivot tables follow the same split as data validation: authoring is Pro, rendering is everywhere.

So a Pro-licensed build tool can author a workbook with pivot summaries, and a Lite-based viewer on the receiving end shows them correctly — the write side is what carries the license.

The v1 pivot is deliberately API-first: you declare the fields, the grid does the math and the drawing. An end-user drag-and-drop field panel is on the roadmap; as the example above shows, a couple of dropdowns wired to update cover a lot of that ground today.

Wrapping up

A pivot table is the shortest path from “200 rows of records” to “the number the meeting actually needs” — and here it’s one declaration: createPivot with a source, an anchor, and your row/column/value fields. Seven aggregations, per-measure captions and number formats, report filters, grand totals, and a handle whose update makes the whole thing reconfigurable at runtime. Value edits recompute automatically; structural edits need one refresh().

Try it in the pivot table demo — swap fields and aggregations against live data — or read the full API in the pivot table docs. To keep the source data clean before it’s summarized, see the data validation article; to let users explore the raw records alongside the summary, the sort & filter article.