API Reference
Cmdlet
Add-OfficeExcelPivotTable
Adds a pivot table to a worksheet.
Remarks
Adds a pivot table to a worksheet.
Examples
Example 1: Create a pivot table from a report data sheet.
PS>
$rows = @(
[pscustomobject]@{ Region = 'North America'; Product = 'Standard'; Sales = 125000 }
[pscustomobject]@{ Region = 'EMEA'; Product = 'Standard'; Sales = 98000 }
[pscustomobject]@{ Region = 'APAC'; Product = 'Premium'; Sales = 143000 }
)
New-OfficeExcel -Path .\SalesPivot.xlsx {
Add-OfficeExcelSheet -Name Data {
Add-OfficeExcelTable -InputObject $rows -TableName Sales -AutoFit
Add-OfficeExcelPivotTable -SourceRange 'A1:C4' -DestinationCell 'E2' -Name 'SalesByRegion' -RowField Region -ColumnField Product -DataField Sales -DataFunction Sum -PivotStyle PivotStyleMedium9
}
}
Writes source rows to a worksheet and creates a pivot table using the existing OfficeIMO pivot support.
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.
Syntax
Add-OfficeExcelPivotTable [-ColumnField <String[]>] [-ColumnHeaderCaption <String>] [-CustomListSort] [-DataDisplayName <String[]>] [-DataField <String[]>] [-DataFunction <String[]>] [-DataNumberFormat <String[]>] [-DataOnColumns] [-DataOnRows] -DestinationCell <String> [-DisableDrill] [-EnableDrill] [-ErrorCaption <String>] [-FieldCompact <String[]>] [-FieldHiddenItems <Hashtable>] [-FieldHideDropDowns <String[]>] [-FieldInsertBlankRow <String[]>] [-FieldInsertPageBreak <String[]>] [-FieldListSortAscending] [-FieldListSortDescending] [-FieldNoDefaultSubtotal <String[]>] [-FieldOutline <String[]>] [-FieldSort <Hashtable>] [-FieldSubtotalTop <String[]>] [-FieldVisibleItems <Hashtable>] [-GrandTotalCaption <String>] [-HideDataDropDown] [-HideDataTips] [-HideDrill] [-HideDropZones] [-HideEmptyColumns] [-HideEmptyRows] [-HideHeaders] [-HideMemberPropertyTips] [-Layout <String>] [-MissingCaption <String>] [-Name <String>] [-NoColumnGrandTotals] [-NoCustomListSort] [-NoPreserveFormatting] [-NoRefreshOnOpen] [-NoRowGrandTotals] [-NoSaveSourceData] [-PageField <String[]>] [-PageFieldSelection <Hashtable>] [-PassThru] [-PivotStyle <String>] [-PreserveFormatting] [-RefreshOnOpen] [-RowField <String[]>] [-RowHeaderCaption <String>] [-SaveSourceData] [-ShowDataDropDown] [-ShowDataTips] [-ShowDrill] [-ShowDropZones] [-ShowEmptyColumns] [-ShowEmptyRows] [-ShowHeaders] [-ShowMemberPropertyTips] -SourceRange <String> [<CommonParameters>]Parameter set:
ContextParameters
- ColumnField String[]
- Column fields (header names).
- ColumnHeaderCaption String
- Optional column header caption.
- CustomListSort SwitchParameter
- Use Excel custom-list sorting.
- DataDisplayName String[]
- Display names for data fields.
- DataField String[]
- Data fields (header names). Defaults to the last column when omitted.
- DataFunction String[]
- Aggregation functions (Sum, Count, Average, etc.).
- DataNumberFormat String[]
- Number format codes for data fields.
- DataOnColumns SwitchParameter
- Show data fields on columns.
- DataOnRows SwitchParameter
- Show data fields on rows.
- DestinationCell String
- Top-left destination cell for the pivot table (e.g., "F2").
- DisableDrill SwitchParameter
- Disable pivot detail drill interaction in Excel.
- EnableDrill SwitchParameter
- Allow users to drill into pivot details in Excel.
- ErrorCaption String
- Optional error-value caption.
- FieldCompact String[]
- Fields using compact field layout.
- FieldHiddenItems Hashtable
- Field item captions to hide, for example @{ Region = @('Legacy') }.
- FieldHideDropDowns String[]
- Fields whose filter drop-downs should be hidden.
- FieldInsertBlankRow String[]
- Fields that insert blank rows after items.
- FieldInsertPageBreak String[]
- Fields that insert page breaks after items.
- FieldListSortAscending SwitchParameter
- Sort pivot field list ascending.
- FieldListSortDescending SwitchParameter
- Sort pivot field list descending.
- FieldNoDefaultSubtotal String[]
- Fields with default subtotal disabled.
- FieldOutline String[]
- Fields using outline field layout.
- FieldSort Hashtable
- Field sort map, for example @{ Region = 'Ascending' }.
- FieldSubtotalTop String[]
- Fields with subtotals shown at the top.
- FieldVisibleItems Hashtable
- Field item captions to keep visible, hiding other known items.
- GrandTotalCaption String
- Optional grand total caption.
- HideDataDropDown SwitchParameter
- Hide the data drop-down.
- HideDataTips SwitchParameter
- Hide pivot data tips.
- HideDrill SwitchParameter
- Hide drill indicators.
- HideDropZones SwitchParameter
- Hide pivot drop zones.
- HideEmptyColumns SwitchParameter
- Hide empty columns.
- HideEmptyRows SwitchParameter
- Hide empty rows.
- HideHeaders SwitchParameter
- Hide field headers.
- HideMemberPropertyTips SwitchParameter
- Hide member property tips.
- Layout String
- Pivot layout (Compact, Outline, Tabular).
- MissingCaption String
- Optional missing-value caption.
- Name String
- Optional pivot table name.
- NoColumnGrandTotals SwitchParameter
- Disable column grand totals.
- NoCustomListSort SwitchParameter
- Disable Excel custom-list sorting.
- NoPreserveFormatting SwitchParameter
- Do not preserve pivot formatting when Excel refreshes the pivot table.
- NoRefreshOnOpen SwitchParameter
- Do not refresh the pivot cache when the workbook opens.
- NoRowGrandTotals SwitchParameter
- Disable row grand totals.
- NoSaveSourceData SwitchParameter
- Do not save pivot source cache records in the workbook package.
- PageField String[]
- Page fields (header names) used as filters.
- PageFieldSelection Hashtable
- Selected page-field item captions, for example @{ Product = 'Standard' }.
- PassThru SwitchParameter
- Emit the worksheet after creating the pivot table.
- PivotStyle String
- Optional pivot table style name.
- PreserveFormatting SwitchParameter
- Preserve pivot formatting when Excel refreshes the pivot table.
- RefreshOnOpen SwitchParameter
- Refresh the pivot cache when the workbook opens.
- RowField String[]
- Row fields (header names).
- RowHeaderCaption String
- Optional row header caption.
- SaveSourceData SwitchParameter
- Save pivot source cache records in the workbook package.
- ShowDataDropDown SwitchParameter
- Show the data drop-down.
- ShowDataTips SwitchParameter
- Show pivot data tips.
- ShowDrill SwitchParameter
- Show drill indicators.
- ShowDropZones SwitchParameter
- Show pivot drop zones.
- ShowEmptyColumns SwitchParameter
- Show empty columns.
- ShowEmptyRows SwitchParameter
- Show empty rows.
- ShowHeaders SwitchParameter
- Show field headers.
- ShowMemberPropertyTips SwitchParameter
- Show member property tips.
- SourceRange String
- Source data range including header row (e.g., "A1:D200").
Add-OfficeExcelPivotTable [-ColumnField <String[]>] [-ColumnHeaderCaption <String>] [-CustomListSort] [-DataDisplayName <String[]>] [-DataField <String[]>] [-DataFunction <String[]>] [-DataNumberFormat <String[]>] [-DataOnColumns] [-DataOnRows] -DestinationCell <String> [-DisableDrill] -Document <ExcelDocument> [-EnableDrill] [-ErrorCaption <String>] [-FieldCompact <String[]>] [-FieldHiddenItems <Hashtable>] [-FieldHideDropDowns <String[]>] [-FieldInsertBlankRow <String[]>] [-FieldInsertPageBreak <String[]>] [-FieldListSortAscending] [-FieldListSortDescending] [-FieldNoDefaultSubtotal <String[]>] [-FieldOutline <String[]>] [-FieldSort <Hashtable>] [-FieldSubtotalTop <String[]>] [-FieldVisibleItems <Hashtable>] [-GrandTotalCaption <String>] [-HideDataDropDown] [-HideDataTips] [-HideDrill] [-HideDropZones] [-HideEmptyColumns] [-HideEmptyRows] [-HideHeaders] [-HideMemberPropertyTips] [-Layout <String>] [-MissingCaption <String>] [-Name <String>] [-NoColumnGrandTotals] [-NoCustomListSort] [-NoPreserveFormatting] [-NoRefreshOnOpen] [-NoRowGrandTotals] [-NoSaveSourceData] [-PageField <String[]>] [-PageFieldSelection <Hashtable>] [-PassThru] [-PivotStyle <String>] [-PreserveFormatting] [-RefreshOnOpen] [-RowField <String[]>] [-RowHeaderCaption <String>] [-SaveSourceData] [-Sheet <String>] [-SheetIndex <Nullable`1>] [-ShowDataDropDown] [-ShowDataTips] [-ShowDrill] [-ShowDropZones] [-ShowEmptyColumns] [-ShowEmptyRows] [-ShowHeaders] [-ShowMemberPropertyTips] -SourceRange <String> [<CommonParameters>]Parameter set:
DocumentParameters
- ColumnField String[]
- Column fields (header names).
- ColumnHeaderCaption String
- Optional column header caption.
- CustomListSort SwitchParameter
- Use Excel custom-list sorting.
- DataDisplayName String[]
- Display names for data fields.
- DataField String[]
- Data fields (header names). Defaults to the last column when omitted.
- DataFunction String[]
- Aggregation functions (Sum, Count, Average, etc.).
- DataNumberFormat String[]
- Number format codes for data fields.
- DataOnColumns SwitchParameter
- Show data fields on columns.
- DataOnRows SwitchParameter
- Show data fields on rows.
- DestinationCell String
- Top-left destination cell for the pivot table (e.g., "F2").
- DisableDrill SwitchParameter
- Disable pivot detail drill interaction in Excel.
- Document ExcelDocument
- Workbook to operate on outside the DSL context.
- EnableDrill SwitchParameter
- Allow users to drill into pivot details in Excel.
- ErrorCaption String
- Optional error-value caption.
- FieldCompact String[]
- Fields using compact field layout.
- FieldHiddenItems Hashtable
- Field item captions to hide, for example @{ Region = @('Legacy') }.
- FieldHideDropDowns String[]
- Fields whose filter drop-downs should be hidden.
- FieldInsertBlankRow String[]
- Fields that insert blank rows after items.
- FieldInsertPageBreak String[]
- Fields that insert page breaks after items.
- FieldListSortAscending SwitchParameter
- Sort pivot field list ascending.
- FieldListSortDescending SwitchParameter
- Sort pivot field list descending.
- FieldNoDefaultSubtotal String[]
- Fields with default subtotal disabled.
- FieldOutline String[]
- Fields using outline field layout.
- FieldSort Hashtable
- Field sort map, for example @{ Region = 'Ascending' }.
- FieldSubtotalTop String[]
- Fields with subtotals shown at the top.
- FieldVisibleItems Hashtable
- Field item captions to keep visible, hiding other known items.
- GrandTotalCaption String
- Optional grand total caption.
- HideDataDropDown SwitchParameter
- Hide the data drop-down.
- HideDataTips SwitchParameter
- Hide pivot data tips.
- HideDrill SwitchParameter
- Hide drill indicators.
- HideDropZones SwitchParameter
- Hide pivot drop zones.
- HideEmptyColumns SwitchParameter
- Hide empty columns.
- HideEmptyRows SwitchParameter
- Hide empty rows.
- HideHeaders SwitchParameter
- Hide field headers.
- HideMemberPropertyTips SwitchParameter
- Hide member property tips.
- Layout String
- Pivot layout (Compact, Outline, Tabular).
- MissingCaption String
- Optional missing-value caption.
- Name String
- Optional pivot table name.
- NoColumnGrandTotals SwitchParameter
- Disable column grand totals.
- NoCustomListSort SwitchParameter
- Disable Excel custom-list sorting.
- NoPreserveFormatting SwitchParameter
- Do not preserve pivot formatting when Excel refreshes the pivot table.
- NoRefreshOnOpen SwitchParameter
- Do not refresh the pivot cache when the workbook opens.
- NoRowGrandTotals SwitchParameter
- Disable row grand totals.
- NoSaveSourceData SwitchParameter
- Do not save pivot source cache records in the workbook package.
- PageField String[]
- Page fields (header names) used as filters.
- PageFieldSelection Hashtable
- Selected page-field item captions, for example @{ Product = 'Standard' }.
- PassThru SwitchParameter
- Emit the worksheet after creating the pivot table.
- PivotStyle String
- Optional pivot table style name.
- PreserveFormatting SwitchParameter
- Preserve pivot formatting when Excel refreshes the pivot table.
- RefreshOnOpen SwitchParameter
- Refresh the pivot cache when the workbook opens.
- RowField String[]
- Row fields (header names).
- RowHeaderCaption String
- Optional row header caption.
- SaveSourceData SwitchParameter
- Save pivot source cache records in the workbook package.
- Sheet String
- Worksheet name when using Document.
- SheetIndex Nullable`1
- Worksheet index (0-based) when using Document.
- ShowDataDropDown SwitchParameter
- Show the data drop-down.
- ShowDataTips SwitchParameter
- Show pivot data tips.
- ShowDrill SwitchParameter
- Show drill indicators.
- ShowDropZones SwitchParameter
- Show pivot drop zones.
- ShowEmptyColumns SwitchParameter
- Show empty columns.
- ShowEmptyRows SwitchParameter
- Show empty rows.
- ShowHeaders SwitchParameter
- Show field headers.
- ShowMemberPropertyTips SwitchParameter
- Show member property tips.
- SourceRange String
- Source data range including header row (e.g., "A1:D200").