Blog

What does this extension do?

The pivot table extension transforms any Jspreadsheet worksheet into a summary tool. Users drag fields into the row, column, and value areas, and the extension automatically performs the grouping and calculations, displaying the results in a familiar pivot table layout. It operates without a server or external libraries.

Multi-level grouping

You can group by a single field or nest several. For example, in a sales dataset, the top level could group by region, then by salesperson within each region, and finally by product within each salesperson. Collapse to see the totals; expand to see the detail.

Both rows and columns support grouping, so you can build a typical matrix view: regions down the left, quarters across the top, and total sales filling the middle cells.

12 summary functions

Choose the appropriate algorithm for your data:

Function	Purpose
SUM	Sum of all values
AVERAGE	Average value
COUNT	Number of numeric entries
COUNTA	Number of non-empty entries
COUNTUNIQUE	Number of distinct values
MEDIAN	Median
MAX / MIN	Maximum and minimum values
PRODUCT	Product of all values
STDEV / STDEVP	Sample and population standard deviation
VAR / VARP	Sample and population variance

Users select a field and a summary function, and the extension handles the rest. Common summarization requirements generally don't require writing formulas or code by hand.

Filtering and sorting

Filters apply to the data before summarization. You can filter by specific values ("only show North America and Europe") or by rules ("amount greater than 10,000", "date between Q1 and Q2").

Sorting supports both name and value. Regions can be ordered alphabetically or by total revenue from highest to lowest, placing the best-performing regions at the top. Both rows and columns can be sorted in ascending or descending order.

GETPIVOTDATA formula

Once the pivot table is generated, you can use GETPIVOTDATA to retrieve values from other cells:

=GETPIVOTDATA("Sales", PivotTable1, "Region", "North", "Quarter", "Q2")

This lets you extract a specific summary result from a pivot table and place it in a regular cell. It's suitable for building dashboards or reports without having to rewrite the summarization logic.

Data source across worksheets

Pivot table data can come from another worksheet within the same workbook. By setting the source to a range reference such as SHEET1!B3:E53, a pivot table on SHEET2 will summarize the raw data on SHEET1. Users see only the clean summary results, without the underlying detail rows.

How to install

Install the extension, then add it to the Jspreadsheet configuration:

npm install @jspreadsheet/pivot

import jspreadsheet from 'jspreadsheet';
import pivot from '@jspreadsheet/pivot';

jspreadsheet.setExtensions({ pivot });

jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: salesData,
        pivotTable: {
            anchor: 'A1',
            source: 'SHEET1!A1:E100',
            rows: [
                { columnIndex: 0 }  // Group by the first column (e.g., region)
            ],
            columns: [
                { columnIndex: 2 }  // Expand by the third column (e.g., quarter)
            ],
            cells: [
                { columnIndex: 3, method: 'SUM' }  // Sum the fourth column (e.g., sales revenue)
            ]
        }
    }]
});

If you want to try it out quickly, you can also use a CDN directly:

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/pivot/dist/index.min.js"></script>

The same configuration works with React, Vue, and Angular through the corresponding framework wrappers in the official documentation.

Filtering examples

Only keep rows with sales exceeding 5,000:

pivotTable: {
    anchor: 'A1',
    source: 'SHEET1!A1:E100',
    rows: [
        { columnIndex: 0 }
    ],
    cells: [
        { columnIndex: 3, method: 'SUM' }
    ],
    filters: [
        {
            columnIndex: 3,
            validation: {
                type: 'number',
                criteria: '>',
                value: [5000]
            }
        }
    ]
}

You can also filter by specific values. For example, to only view the North and West regions:

filters: [
    {
        columnIndex: 0,
        filter: ['North', 'West']
    }
]

Sort by value

Sort regions by sales revenue instead of alphabetically:

rows: [
    {
        columnIndex: 0,
        sortBy: 'cell-id-of-sales-aggregation',
        ascendingOrder: false  // Highest-ranking first
    }
]

This puts the best-performing regions at the top of the table, which is what most business users expect.

When to use this

Internal tools and admin panels. If your application already has a data management interface, pivot tables allow experienced users to query data themselves, eliminating the need to ask the development team to schedule reports every time.

Financial applications. Breaking down expenses by category, viewing revenue by product line, comparing budgets with actuals — the 12 summary functions cover most financial summarization scenarios.

Inventory and operations. Group by warehouse, supplier, or product category. See which categories have the highest return rates and which stock levels are running low.

HR and people analytics. Headcount by department, salary breakdowns by role, recruitment funnel analysis by channel. Pivot tables transform flat employee data into understandable summary results.

Customer analytics. Support tickets grouped by type and priority. Order amounts summarized by customer segment. Unique customer counts deduplicated by acquisition channel.

A common alternative for these scenarios is to export to Excel. With pivot tables, the analysis stays connected to the application's real-time data and doesn't become detached.

What are the differences between this and a standalone pivot table library?

There are a few standalone JavaScript pivot table components on the market. The difference with the Jspreadsheet extension lies in its integration: the pivot table is part of the spreadsheet component that also handles data entry, formulas, and formatting, so users don't have to switch back and forth between a data grid and a separate analysis tool — everything happens in one place.

The GETPIVOTDATA formula is the clearest example. In a standalone library, there is no formula engine, so you can't reference pivot tables from other cells. In Jspreadsheet, pivot tables are part of the spreadsheet's formula graph. Changing a value in the source data automatically refreshes the pivot table and every cell referencing it.

Related reading

• Pivot Table Extension Documentation
• GETPIVOTDATA Function Reference
• JavaScript Spreadsheet Formula Engine
• JavaScript Data Grid Performance
• Jspreadsheet Documentation