Work with PivotTables using the Excel JavaScript API

PivotTables streamline larger data sets. They allow the quick manipulation of grouped data. The Excel JavaScript API lets your add-in create PivotTables and interact with their components. This article describes how PivotTables are represented by the Office JavaScript API and provides code samples for key scenarios.

If you are unfamiliar with the functionality of PivotTables, consider exploring them as an end user. See Create a PivotTable to analyze worksheet data for a good primer on these tools.


PivotTables created with OLAP are not currently supported. There is also no support for Power Pivot.

Object model

The PivotTable is the central object for PivotTables in the Office JavaScript API.

Let's look at how these relationships apply to some example data. The following data describes fruit sales from various farms. It will be the example throughout this article.

A collection of fruit sales of different types from different farms.

This fruit farm sales data will be used to make a PivotTable. Each column, such as Types, is a PivotHierarchy. The Types hierarchy contains the Types field. The Types field contains the items Apple, Kiwi, Lemon, Lime, and Orange.


PivotTables are organized based on four hierarchy categories: row, column, data, and filter.

The farm data shown earlier has five hierarchies: Farms, Type, Classification, Crates Sold at Farm, and Crates Sold Wholesale. Each hierarchy can only exist in one of the four categories. If Type is added to column hierarchies, it cannot also be in the row, data, or filter hierarchies. If Type is subsequently added to row hierarchies, it is removed from the column hierarchies. This behavior is the same whether hierarchy assignment is done through the Excel UI or the Excel JavaScript APIs.

Row and column hierarchies define how data will be grouped. For example, a row hierarchy of Farms will group together all the data sets from the same farm. The choice between row and column hierarchy defines the orientation of the PivotTable.

Data hierarchies are the values to be aggregated based on the row and column hierarchies. A PivotTable with a row hierarchy of Farms and a data hierarchy of Crates Sold Wholesale shows the sum total (by default) of all the different fruits for each farm.

Filter hierarchies include or exclude data from the pivot based on values within that filtered type. A filter hierarchy of Classification with the type Organic selected only shows data for organic fruit.

Here is the farm data again, alongside a PivotTable. The PivotTable is using Farm and Type as the row hierarchies, Crates Sold at Farm and Crates Sold Wholesale as the data hierarchies (with the default aggregation function of sum), and Classification as a filter hierarchy (with Organic selected).

A selection of fruit sales data next to a PivotTable with row, data, and filter hierarchies.

This PivotTable could be generated through the JavaScript API or through the Excel UI. Both options allow for further manipulation through add-ins.

Create a PivotTable

PivotTables need a name, source, and destination. The source can be a range address or table name (passed as a Range, string, or Table type). The destination is a range address (given as either a Range or string). The following samples show various PivotTable creation techniques.

Create a PivotTable with range addresses (context) {
    // Create a PivotTable named "Farm Sales" on the current worksheet at cell
    // A22 with data from the range A1:E21.
      "Farm Sales", "A1:E21", "A22");

    return context.sync();

Create a PivotTable with Range objects (context) {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data comes from the worksheet "DataWorksheet" across the range A1:E21.
    var rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    var rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    return context.sync();

Create a PivotTable at the workbook level (context) {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data is from the worksheet "DataWorksheet" across the range A1:E21.
        "Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");

    return context.sync();

Use an existing PivotTable

Manually created PivotTables are also accessible through the PivotTable collection of the workbook or of individual worksheets. The following code gets a PivotTable named My Pivot from the workbook. (context) {
    var pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    return context.sync();

Add rows and columns to a PivotTable

Rows and columns pivot the data around those fields' values.

Adding the Farm column pivots all the sales around each farm. Adding the Type and Classification rows further breaks down the data based on what fruit was sold and whether it was organic or not.

A PivotTable with a Farm column and Type and Classification rows. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");



    return context.sync();

You can also have a PivotTable with only rows or columns. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    return context.sync();

Add data hierarchies to the PivotTable

Data hierarchies fill the PivotTable with information to combine based on the rows and columns. Adding the data hierarchies of Crates Sold at Farm and Crates Sold Wholesale gives sums of those figures for each row and column.

In the example, both Farm and Type are rows, with the crate sales as the data.

A PivotTable showing the total sales of different fruit based on the farm they came from. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // "Farm" and "Type" are the hierarchies on which the aggregation is based.

    // "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
    // that will have their data aggregated (summed in this case).
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    return context.sync();

PivotTable layouts and getting pivoted data

A PivotLayout defines the placement of hierarchies and their data. You access the layout to determine the ranges where data is stored.

The following diagram shows which layout function calls correspond to which ranges of the PivotTable.

A diagram showing which sections of a PivotTable are returned by the layout's get range functions.

Get data from the PivotTable

The layout defines how the PivotTable is displayed in the worksheet. This means the PivotLayout object controls the ranges used for PivotTable elements. Use the ranges provided by the layout to get data collected and aggregated by the PivotTable. In particular, use PivotLayout.getDataBodyRange to access the data produced by the PivotTable.

The following code demonstrates how to get the last row of the PivotTable data by going through the layout (the Grand Total of both the Sum of Crates Sold at Farm and Sum of Crates Sold Wholesale columns in the earlier example). Those values are then summed together for a final total, which is displayed in cell E30 (outside of the PivotTable). (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    var range = pivotTable.layout.getDataBodyRange();
    var grandTotalRange = range.getLastRow();
    return context.sync().then(function () {
        // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
        var masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
        masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];

Layout types

PivotTables have three layout styles: Compact, Outline, and Tabular. We've seen the compact style in the previous examples.

The following examples use the outline and tabular styles, respectively. The code sample shows how to cycle between the different layouts.

Outline layout

A PivotTable using the outline layout.

Tabular layout

A PivotTable using the tabular layout.

PivotLayout type switch code sample (context) {
    // Change the PivotLayout.type to a new type.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    return context.sync().then(function () {
        // Cycle between the three layout types.
        if (pivotTable.layout.layoutType === "Compact") {
            pivotTable.layout.layoutType = "Outline";
        } else if (pivotTable.layout.layoutType === "Outline") {
            pivotTable.layout.layoutType = "Tabular";
        } else {
            pivotTable.layout.layoutType = "Compact";
        return context.sync();

Other PivotLayout functions

By default, PivotTables adjust row and column sizes as needed. This is done when the PivotTable is refreshed. PivotLayout.autoFormat specifies that behavior. Any row or column size changes made by your add-in persist when autoFormat is false. Additionally, the default settings of a PivotTable keep any custom formatting in the PivotTable (such as fills and font changes). Set PivotLayout.preserveFormatting to false to apply the default format when refreshed.

A PivotLayout also controls header and total row settings, how empty data cells are displayed, and alt text options. The PivotLayout reference provides a complete list of these features.

The following code sample makes empty data cells display the string "--", formats the body range to a consistent horizontal alignment, and ensures that the formatting changes remain even after the PivotTable is refreshed. (context) {
    var pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    var pivotLayout = pivotTable.layout;

    // Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
    pivotLayout.emptyCellText = "--";

    // Set the text alignment to match the rest of the PivotTable.
    pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;

    // Ensure empty cells are filled with a default value.
    pivotLayout.fillEmptyCells = true;

    // Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
    pivotLayout.preserveFormatting = true;
    return context.sync();

Delete a PivotTable

PivotTables are deleted by using their name. (context) {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    return context.sync();

Filter a PivotTable

The primary method for filtering PivotTable data is with PivotFilters. Slicers offer an alternate, less flexible filtering method.

PivotFilters filter data based on a PivotTable's four hierarchy categories (filters, columns, rows, and values). There are four types of PivotFilters, allowing calendar date-based filtering, string parsing, number comparison, and filtering based on a custom input.

Slicers can be applied to both PivotTables and regular Excel tables. When applied to a PivotTable, slicers function like a PivotManualFilter and allow filtering based on a custom input. Unlike PivotFilters, slicers have an Excel UI component. With the Slicer class, you create this UI component, manage filtering, and control its visual appearance.

Filter with PivotFilters

PivotFilters allow you to filter PivotTable data based on the four hierarchy categories (filters, columns, rows, and values). In the PivotTable object model, PivotFilters are applied to a PivotField, and each PivotField can have one or more assigned PivotFilters. To apply PivotFilters to a PivotField, the field's corresponding PivotHierarchy must be assigned to a hierarchy category.

Types of PivotFilters

Filter type Filter purpose Excel JavaScript API reference
DateFilter Calendar date-based filtering. PivotDateFilter
LabelFilter Text comparison filtering. PivotLabelFilter
ManualFilter Custom input filtering. PivotManualFilter
ValueFilter Number comparison filtering. PivotValueFilter

Create a PivotFilter

To filter PivotTable data with a Pivot*Filter (such as a PivotDateFilter), apply the filter to a PivotField. The following four code samples show how to use each of the four types of PivotFilters.


The first code sample applies a PivotDateFilter to the Date Updated PivotField, hiding any data prior to 2020-08-01.


A Pivot*Filter can't be applied to a PivotField unless that field's PivotHierarchy is assigned to a hierarchy category. In the following code sample, the dateHierarchy must be added to the PivotTable's rowHierarchies category before it can be used for filtering. (context) {
    // Get the PivotTable and the date hierarchy.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    var dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    return context.sync().then(function () {
        // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
        // If it's not already there, add "Date Updated" to the hierarchies.
        if (dateHierarchy.isNullObject) {
          dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));

        // Apply a date filter to filter out anything logged before August.
        var filterField = dateHierarchy.fields.getItem("Date Updated");
        var dateFilter = {
          condition: Excel.DateFilterCondition.afterOrEqualTo,
          comparator: {
            date: "2020-08-01",
            specificity: Excel.FilterDatetimeSpecificity.month
        filterField.applyFilter({ dateFilter: dateFilter });
        return context.sync();


The following three code snippets only display filter-specific excerpts, instead of full calls.


The second code snippet demonstrates how to apply a PivotLabelFilter to the Type PivotField, using the LabelFilterCondition.beginsWith property to exclude labels that start with the letter L.

    // Get the "Type" field.
    var filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    var filter: Excel.PivotLabelFilter = {
      condition: Excel.LabelFilterCondition.beginsWith,
      substring: "L",
      exclusive: true

    // Apply the label filter to the field.
    filterField.applyFilter({ labelFilter: filter });

The third code snippet applies a manual filter with PivotManualFilter to the the Classification field, filtering out data that doesn't include the classification Organic.

    // Apply a manual filter to include only a specific PivotItem (the string "Organic").
    var filterField = classHierarchy.fields.getItem("Classification");
    var manualFilter = { selectedItems: ["Organic"] };
    filterField.applyFilter({ manualFilter: manualFilter });

To compare numbers, use a value filter with PivotValueFilter, as shown in the final code snippet. The PivotValueFilter compares the data in the Farm PivotField to the data in the Crates Sold Wholesale PivotField, including only farms whose sum of crates sold exceeds the value 500.

    // Get the "Farm" field.
    var filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    // Filter to only include rows with more than 500 wholesale crates sold.
    var filter: Excel.PivotValueFilter = {
      condition: Excel.ValueFilterCondition.greaterThan,
      comparator: 500,
      value: "Sum of Crates Sold Wholesale"
    // Apply the value filter to the field.
    filterField.applyFilter({ valueFilter: filter });

Remove PivotFilters

To remove all PivotFilters, apply the clearAllFilters method to each PivotField, as shown in the following code sample. (context) {
    // Get the PivotTable.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    return context.sync().then(function () {
        // Clear the filters on each PivotField.
        pivotTable.hierarchies.items.forEach(function (hierarchy) {
        return context.sync();

Filter with slicers

Slicers allow data to be filtered from an Excel PivotTable or table. A slicer uses values from a specified column or PivotField to filter corresponding rows. These values are stored as SlicerItem objects in the Slicer. Your add-in can adjust these filters, as can users (through the Excel UI). The slicer sits on top of the worksheet in the drawing layer, as shown in the following screenshot.

A slicer filtering data on a PivotTable.


The techniques described in this section focus on how to use slicers connected to PivotTables. The same techniques also apply to using slicers connected to tables.

Create a slicer

You can create a slicer in a workbook or worksheet by using the Workbook.slicers.add method or Worksheet.slicers.add method. Doing so adds a slicer to the SlicerCollection of the specified Workbook or Worksheet object. The SlicerCollection.add method has three parameters:

  • slicerSource: The data source on which the new slicer is based. It can be a PivotTable, Table, or string representing the name or ID of a PivotTable or Table.
  • sourceField: The field in the data source by which to filter. It can be a PivotField, TableColumn, or string representing the name or ID of a PivotField or TableColumn.
  • slicerDestination: The worksheet where the new slicer will be created. It can be a Worksheet object or the name or ID of a Worksheet. This parameter is unnecessary when the SlicerCollection is accessed through Worksheet.slicers. In this case, the collection's worksheet is used as the destination.

The following code sample adds a new slicer to the Pivot worksheet. The slicer's source is the Farm Sales PivotTable and filters using the Type data. The slicer is also named Fruit Slicer for future reference. (context) {
    var sheet = context.workbook.worksheets.getItem("Pivot");
    var slicer = sheet.slicers.add(
        "Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
        "Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    ); = "Fruit Slicer";
    return context.sync();

Filter items with a slicer

The slicer filters the PivotTable with items from the sourceField. The Slicer.selectItems method sets the items that remain in the slicer. These items are passed to the method as a string[], representing the keys of the items. Any rows containing those items remain in the PivotTable's aggregation. Subsequent calls to selectItems set the list to the keys specified in those calls.


If Slicer.selectItems is passed an item that's not in the data source, an InvalidArgument error is thrown. The contents can be verified through the Slicer.slicerItems property, which is a SlicerItemCollection.

The following code sample shows three items being selected for the slicer: Lemon, Lime, and Orange. (context) {
    var slicer = context.workbook.slicers.getItem("Fruit Slicer");
    // Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
    slicer.selectItems(["Lemon", "Lime", "Orange"]);
    return context.sync();

To remove all filters from the slicer, use the Slicer.clearFilters method, as shown in the following sample. (context) {
    var slicer = context.workbook.slicers.getItem("Fruit Slicer");
    return context.sync();

Style and format a slicer

You add-in can adjust a slicer's display settings through Slicer properties. The following code sample sets the style to SlicerStyleLight6, sets the text at the top of the slicer to Fruit Types, places the slicer at the position (395, 15) on the drawing layer, and sets the slicer's size to 135x150 pixels. (context) {
    var slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.caption = "Fruit Types";
    slicer.left = 395; = 15;
    slicer.height = 135;
    slicer.width = 150; = "SlicerStyleLight6";
    return context.sync();

Delete a slicer

To delete a slicer, call the Slicer.delete method. The following code sample deletes the first slicer from the current worksheet. (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    return context.sync();

Change aggregation function

Data hierarchies have their values aggregated. For datasets of numbers, this is a sum by default. The summarizeBy property defines this behavior based on an AggregationFunction type.

The currently supported aggregation function types are Sum, Count, Average, Max, Min, Product, CountNumbers, StandardDeviation, StandardDeviationP, Variance, VarianceP, and Automatic (the default).

The following code samples changes the aggregation to be averages of the data. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    return context.sync().then(function() {

        // Change the aggregation from the default sum to an average of all the values in the hierarchy.
        pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
        pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
        return context.sync();

Change calculations with a ShowAsRule

PivotTables, by default, aggregate the data of their row and column hierarchies independently. A ShowAsRule changes the data hierarchy to output values based on other items in the PivotTable.

The ShowAsRule object has three properties:

  • calculation: The type of relative calculation to apply to the data hierarchy (the default is none).
  • baseField: The PivotField in the hierarchy containing the base data before the calculation is applied. Since Excel PivotTables have a one-to-one mapping of hierarchy to field, you'll use the same name to access both the hierarchy and the field.
  • baseItem: The individual PivotItem compared against the values of the base fields based on the calculation type. Not all calculations require this field.

The following example sets the calculation on the Sum of Crates Sold at Farm data hierarchy to be a percentage of the column total. We still want the granularity to extend to the fruit type level, so we'll use the Type row hierarchy and its underlying field. The example also has Farm as the first row hierarchy, so the farm total entries display the percentage each farm is responsible for producing as well.

A PivotTable showing the percentages of fruit sales relative to the grand total for both individual farms and individual fruit types within each farm. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    var farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    return context.sync().then(function () {

        // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
        var farmShowAs = farmDataHierarchy.showAs;
        farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
        farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
        farmDataHierarchy.showAs = farmShowAs; = "Percentage of Total Farm Sales";

The previous example set the calculation to the column, relative to the field of an individual row hierarchy. When the calculation relates to an individual item, use the baseItem property.

The following example shows the differenceFrom calculation. It displays the difference of the farm crate sales data hierarchy entries relative to those of A Farms. The baseField is Farm, so we see the differences between the other farms, as well as breakdowns for each type of like fruit (Type is also a row hierarchy in this example).

A PivotTable showing the differences of fruit sales between "A Farms" and the others. This shows both the difference in total fruit sales of the farms and the sales of types of fruit. If "A Farms" did not sell a particular type of fruit, "#N/A" is displayed. (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    var farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    return context.sync().then(function () {
        // Show the difference between crate sales of the "A Farms" and the other farms.
        // This difference is both aggregated and shown for individual fruit types (where applicable).
        var farmShowAs = farmDataHierarchy.showAs;
        farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
        farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
        farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
        farmDataHierarchy.showAs = farmShowAs; = "Difference from A Farms";

Change hierarchy names

Hierarchy fields are editable. The following code demonstrates how to change the displayed names of two data hierarchies. (context) {
    var dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    return context.sync().then(function () {
        // changing the displayed names of these entries
        dataHierarchies.items[0].name = "Farm Sales";
        dataHierarchies.items[1].name = "Wholesale";

See also