Cmdlet

Add-OfficeExcelPivotTable

Aliases: ExcelPivotTable

Module: PSWriteOffice
Aliases: ExcelPivotTable
Inputs: OfficeIMO.Excel.ExcelDocument
Source: AddOfficeExcelPivotTableCommand.cs:42 Edit on GitHub

Adds a pivot table to a worksheet.

Syntax

Add-OfficeExcelPivotTable
-Address <String>
[-ColumnField <String[]>]
[-ColumnHeaderCaption <String>]
[-CustomListSort]
[-DataDisplayName <String[]>]
[-DataField <String[]>]
[-DataFunction <ExcelPivotDataFunction[]>]
[-DataNumberFormat <String[]>]
[-DataOnRows]
[-Document <ExcelDocument>]
[-EnableDrill]
[-ErrorCaption <String>]
[-FieldCompact <String[]>]
[-FieldHiddenItems <Hashtable>]
[-FieldHideDropDowns <String[]>]
[-FieldInsertBlankRow <String[]>]
[-FieldInsertPageBreak <String[]>]
[-FieldListSort <Ascending|Descending>]
[-FieldNoDefaultSubtotal <String[]>]
[-FieldOutline <String[]>]
[-FieldSort <Hashtable>]
[-FieldSubtotalTop <String[]>]
[-FieldVisibleItems <Hashtable>]
[-GrandTotalCaption <String>]
[-Layout <Compact|Outline|Tabular>]
[-MissingCaption <String>]
[-Name <String>]
[-NoColumnGrandTotals]
[-NoRowGrandTotals]
[-PageField <String[]>]
[-PageFieldSelection <Hashtable>]
[-PassThru]
[-PreserveFormatting]
-Range <String>
[-RefreshOnOpen]
[-RowField <String[]>]
[-RowHeaderCaption <String>]
[-SaveSourceData]
[-Sheet <OfficeSheetSelector>]
[-ShowDataDropDown]
[-ShowDataTips]
[-ShowDrill]
[-ShowDropZones]
[-ShowEmptyColumns]
[-ShowEmptyRows]
[-ShowHeaders]
[-ShowMemberPropertyTips]
[-SourceSheet <OfficeSheetSelector>]
[-Style <String>]
[<CommonParameters>]

Parameter set: Range

Add-OfficeExcelPivotTable
-Address <String>
[-ColumnField <String[]>]
[-ColumnHeaderCaption <String>]
[-CustomListSort]
[-DataDisplayName <String[]>]
[-DataField <String[]>]
[-DataFunction <ExcelPivotDataFunction[]>]
[-DataNumberFormat <String[]>]
[-DataOnRows]
[-Document <ExcelDocument>]
[-EnableDrill]
[-ErrorCaption <String>]
[-FieldCompact <String[]>]
[-FieldHiddenItems <Hashtable>]
[-FieldHideDropDowns <String[]>]
[-FieldInsertBlankRow <String[]>]
[-FieldInsertPageBreak <String[]>]
[-FieldListSort <Ascending|Descending>]
[-FieldNoDefaultSubtotal <String[]>]
[-FieldOutline <String[]>]
[-FieldSort <Hashtable>]
[-FieldSubtotalTop <String[]>]
[-FieldVisibleItems <Hashtable>]
[-GrandTotalCaption <String>]
[-Layout <Compact|Outline|Tabular>]
[-MissingCaption <String>]
[-Name <String>]
[-NoColumnGrandTotals]
[-NoRowGrandTotals]
[-PageField <String[]>]
[-PageFieldSelection <Hashtable>]
[-PassThru]
[-PreserveFormatting]
[-RefreshOnOpen]
[-RowField <String[]>]
[-RowHeaderCaption <String>]
[-SaveSourceData]
[-Sheet <OfficeSheetSelector>]
[-ShowDataDropDown]
[-ShowDataTips]
[-ShowDrill]
[-ShowDropZones]
[-ShowEmptyColumns]
[-ShowEmptyRows]
[-ShowHeaders]
[-ShowMemberPropertyTips]
-SourceSheet <OfficeSheetSelector>
[-Style <String>]
[<CommonParameters>]

Parameter set: SourceSheet

Add-OfficeExcelPivotTable
-Address <String>
[-ColumnField <String[]>]
[-ColumnHeaderCaption <String>]
[-CustomListSort]
[-DataDisplayName <String[]>]
[-DataField <String[]>]
[-DataFunction <ExcelPivotDataFunction[]>]
[-DataNumberFormat <String[]>]
[-DataOnRows]
[-Document <ExcelDocument>]
[-EnableDrill]
[-ErrorCaption <String>]
[-FieldCompact <String[]>]
[-FieldHiddenItems <Hashtable>]
[-FieldHideDropDowns <String[]>]
[-FieldInsertBlankRow <String[]>]
[-FieldInsertPageBreak <String[]>]
[-FieldListSort <Ascending|Descending>]
[-FieldNoDefaultSubtotal <String[]>]
[-FieldOutline <String[]>]
[-FieldSort <Hashtable>]
[-FieldSubtotalTop <String[]>]
[-FieldVisibleItems <Hashtable>]
[-GrandTotalCaption <String>]
[-Layout <Compact|Outline|Tabular>]
[-MissingCaption <String>]
[-Name <String>]
[-NoColumnGrandTotals]
[-NoRowGrandTotals]
[-PageField <String[]>]
[-PageFieldSelection <Hashtable>]
[-PassThru]
[-PreserveFormatting]
[-RefreshOnOpen]
[-RowField <String[]>]
[-RowHeaderCaption <String>]
[-SaveSourceData]
[-Sheet <OfficeSheetSelector>]
[-ShowDataDropDown]
[-ShowDataTips]
[-ShowDrill]
[-ShowDropZones]
[-ShowEmptyColumns]
[-ShowEmptyRows]
[-ShowHeaders]
[-ShowMemberPropertyTips]
[-Style <String>]
-Table <String>
[<CommonParameters>]

Parameter set: Table

Examples

Summarise sales in a pivot table

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Orders' {
        ExcelPivotTable -Range A1:F500 -Address H2 -RowField Region -DataField Amount -DataFunction Sum
    }
}

Builds a pivot table of revenue by region from the order data in A1:F500.

Pivot revenue by region and quarter

$orders = Import-OfficeCsv -Path .\Data\Orders-2026.csv
New-OfficeExcel -Path .\Reports\Sales-2026.xlsx {
    ExcelSheet 'Orders' {
        ExcelTable $orders -Name Orders
        ExcelPivotTable -Table Orders -Address J3 -Name SalesPivot -RowField Region -ColumnField Quarter -PageField Product -DataField Amount -DataFunction Sum -DataDisplayName 'Revenue' -DataNumberFormat '#,##0' -Layout Tabular -RefreshOnOpen
    }
}

Writes a year of orders and pivots them by region and quarter with a product filter, naming the pivot so a slicer and conditional formats can refer to it.

Put the pivot on its own sheet

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Summary' {
        ExcelPivotTable -SourceSheet Orders -Address A3 -RowField Region -ColumnField Product -DataField Amount -DataFunction Sum
    }
}

Builds a summary sheet whose pivot reads all the data on the Orders sheet.

Parameters

-Address String required: A1 address of the target cell.
-ColumnField String[] optional: Column fields (header names).
-ColumnHeaderCaption String optional: Optional column header caption.
-CustomListSort SwitchParameter optional: Use Excel custom-list sorting.
-DataDisplayName String[] optional: Display names for data fields.
-DataField String[] optional: Data fields (header names). Defaults to the last column when omitted.
-DataFunction ExcelPivotDataFunction[] optional: Aggregation functions (Sum, Count, Average, etc.).; Possible values: Average, Count, CountNumbers, Maximum, Minimum, Product, StandardDeviation, StandardDeviationP, Sum, Variance, VarianceP
-DataNumberFormat String[] optional: Number format codes for data fields.
-DataOnRows SwitchParameter optional: Show data fields on rows.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-EnableDrill SwitchParameter optional: Allow users to drill into pivot details in Excel.
-ErrorCaption String optional: Optional error-value caption.
-FieldCompact String[] optional: Fields using compact field layout.
-FieldHiddenItems Hashtable optional: Field item captions to hide, for example @{ Region = @('Legacy') }.
-FieldHideDropDowns String[] optional: Fields whose filter drop-downs should be hidden.
-FieldInsertBlankRow String[] optional: Fields that insert blank rows after items.
-FieldInsertPageBreak String[] optional: Fields that insert page breaks after items.
-FieldListSort ListSortDirection optional: Controls the ordering of the pivot field list.; Possible values: Ascending, Descending
-FieldNoDefaultSubtotal String[] optional: Fields with default subtotal disabled.
-FieldOutline String[] optional: Fields using outline field layout.
-FieldSort Hashtable optional: Field sort map, for example @{ Region = 'Ascending' }.
-FieldSubtotalTop String[] optional: Fields with subtotals shown at the top.
-FieldVisibleItems Hashtable optional: Field item captions to keep visible, hiding other known items.
-GrandTotalCaption String optional: Optional grand total caption.
-Layout ExcelPivotLayout optional: Pivot layout (Compact, Outline, Tabular).; Possible values: Compact, Outline, Tabular
-MissingCaption String optional: Optional missing-value caption.
-Name String optional: Optional pivot table name.
-NoColumnGrandTotals SwitchParameter optional: Disable column grand totals.
-NoRowGrandTotals SwitchParameter optional: Disable row grand totals.
-PageField String[] optional: Page fields (header names) used as filters.
-PageFieldSelection Hashtable optional: Selected page-field item captions, for example @{ Product = 'Standard' }.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-PreserveFormatting SwitchParameter optional: Preserve pivot formatting when Excel refreshes the pivot table.
-Range String requiredsets: Range: A1 cell range on the selected worksheet.
-RefreshOnOpen SwitchParameter optional: Refresh the pivot cache when the workbook opens.
-RowField String[] optional: Row fields (header names).
-RowHeaderCaption String optional: Optional row header caption.
-SaveSourceData SwitchParameter optional: Save pivot source cache records in the workbook package.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-ShowDataDropDown SwitchParameter optional: Show the data drop-down.
-ShowDataTips SwitchParameter optional: Show pivot data tips.
-ShowDrill SwitchParameter optional: Show drill indicators.
-ShowDropZones SwitchParameter optional: Show pivot drop zones.
-ShowEmptyColumns SwitchParameter optional: Show empty columns.
-ShowEmptyRows SwitchParameter optional: Show empty rows.
-ShowHeaders SwitchParameter optional: Show field headers.
-ShowMemberPropertyTips SwitchParameter optional: Show member property tips.
-SourceSheet OfficeSheetSelector required in SourceSheetsets: Range, SourceSheet: Gets or sets the source worksheet name, one-based position or native worksheet.
-Style String optional: Built-in chart style identifier.
-Table String requiredsets: Table: Native Excel table used to resolve the source range or header.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.