PivotTable Properties
Include Protected Members
Include Inherited Members
The PivotTable type exposes the following members.
Properties
Name | Description | |
---|---|---|
_Default | Reserved for internal use. | |
ActiveFilters | Indicates the currently active filter in the specified PivotTable. Read-only. | |
Allocation | Gets or sets whether to run an UPDATE CUBE statement for each cell is edited, or only when the user chooses to calculate changes when performing what-if analysis on a PivotTable based on an OLAP data source. | |
AllocationMethod | Gets or sets what method to use to allocate values when performing what-if analysis on a PivotTable report based on an OLAP data source. | |
AllocationValue | Gets or sets what value to allocate when performing what-if analysis on a PivotTable report based on an OLAP data source. | |
AllocationWeightExpression | Gets or sets the MDX weight expression to use when performing what-if analysis on a PivotTable report based on an OLAP data source. | |
AllowMultipleFilters | Sets or retrieves a value that indicates whether a PivotField can have multiple filters applied to it at the same time. Read/write. | |
AlternativeText | Gets or sets the descriptive (alternative) text string for the specified PivotTable. | |
Application | Returns an Application object that represents the Microsoft Excel application. Read-only. | |
CacheIndex | Returns or sets the index number of the PivotTable cache. Read/write Integer. | |
CalculatedMembers | Returns a CalculatedMembers collection representing all the calculated members and calculated measures for an OLAP PivotTable. | |
CalculatedMembersInFilters | Gets or sets whether to evaluate calculated members from OLAP servers in filters. | |
ChangeList | Gets the PivotTableChangeList collection that represents the list of changes that have been made to the specified PivotTable based on an OLAP data source. | |
ColumnFields | Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently shown as column fields. Read-only. | |
ColumnGrand | True if the PivotTable report shows grand totals for columns. Read/write Boolean. | |
ColumnRange | Returns a Range object that represents the range that contains the column area in the PivotTable report. Read-only. | |
CompactLayoutColumnHeader | Specifies the caption that is displayed in the column header of a PivotTable when in compact row layout form. Read/write. | |
CompactLayoutRowHeader | Specifies the caption that is displayed in the row header of a PivotTable when in compact row layout form. Read/write. | |
CompactRowIndent | Returns or sets the indent increment for PivotItems when compact row layout form is turned on. Read/write. | |
Creator | Returns a 32-bit integer that indicates the application in which this object was created. If the object was created in Microsoft Excel, this property returns the string XCEL, which is equivalent to the hexadecimal number 5843454C. Read-only XlCreator. | |
CubeFields | Returns the CubeFields collection. Each CubeField object contains the properties of the cube field element. Read-only. | |
DataBodyRange | Returns a Range object that represents the range that contains the data area in the list between the header row and the insert row. Read-only. | |
DataFields | Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently shown as data fields. Read-only. | |
DataLabelRange | Returns a Range object that represents the range that contains the labels for the data fields in the PivotTable report. Read-only. | |
DataPivotField | Returns a PivotField object that represents all the data fields in a PivotTable. Read-only. | |
DisplayContextTooltips | Controls whether or not tooltips are displayed for PivotTable cells. Read/write. | |
DisplayEmptyColumn | Returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the value axis. The OLAP provider will not return empty columns in the result set. Returns False when the non-empty keyword is omitted. Read/write Boolean. | |
DisplayEmptyRow | Returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the category axis. The OLAP provider will not return empty rows in the result set. Returns False when the non-empty keyword is omitted. Read/write Boolean. | |
DisplayErrorString | True if the PivotTable report displays a custom error string in cells that contain errors. The default value is False. Read/write Boolean. | |
DisplayFieldCaptions | Controls whether or not filter buttons and PivotField captions for rows and columns are displayed in the grid. Read/write. | |
DisplayImmediateItems | Returns or sets a Boolean that indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. Set this property to False to hide the items in the row and column areas when the data area of the PivotTable is empty. The default value is True. | |
DisplayMemberPropertyTooltips | Controls whether or not to display member properties in tooltips. Read/write. | |
DisplayNullString | True if the PivotTable report displays a custom string in cells that contain null values. The default value is True. Read/write Boolean. | |
EnableDataValueEditing | True to disable the alert for when the user overwrites values in the data area of the PivotTable. True also allows the user to change data values that previously could not be changed. The default value is False. Read/write Boolean. | |
EnableDrilldown | True if drilldown is enabled. The default value is True. Read/write Boolean. | |
EnableFieldDialog | True if the PivotTable Field dialog box is available when the user double-clicks the PivotTable field. The default value is True. Read/write Boolean. | |
EnableFieldList | False to disable the ability to display the field list for the PivotTable. If the field list was already being displayed it disappears. The default value is True. Read/write Boolean. | |
EnableWizard | True if the PivotTable Wizard is available. The default value is True. Read/write Boolean. | |
EnableWriteback | Gets or sets whether writing back to the data source is enabled for the specified PivotTable. | |
ErrorString | Returns or sets the string displayed in cells that contain errors when the DisplayErrorString property is True. The default value is an empty string (""). Read/write String. | |
FieldListSortAscending | Controls the sort order of fields in the PivotTable Field List. Read/write. | |
GrandTotalName | Returns or sets the text string label that is displayed in the grand total column or row heading in the specified PivotTable report. The default value is the string "Grand Total". Read/write String. | |
HasAutoFormat | True if the PivotTable report is automatically formatted when it’s refreshed or when fields are moved. Read/write Boolean. | |
HiddenFields | Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently not shown as row, column, page, or data fields. Read-only. | |
InGridDropZones | This property is used to toggle in-grid drop zones for a PivotTable object. In some cases, it also affects the layout of the PivotTable. Read/write. | |
InnerDetail | Returns or sets the name of the field that will be shown as detail when the ShowDetail property is True for the innermost row or column field. Read/write String. | |
LayoutRowDefault | This property specifies the layout settings for PivotFields when they are added to the PivotTable for the first time. Read/write. | |
Location | Gets or sets a String that represents the top-left cell in the body of the specified PivotTable. Read/write. | |
ManualUpdate | True if the PivotTable report is recalculated only at the user's request. The default value is False. Read/write Boolean. | |
MDX | Returns a String indicating the MDX (Multidimensional Expression) that would be sent to the provider to populate the current PivotTable view. Read-only. | |
MergeLabels | True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells. Read/write Boolean. | |
Name | Returns or sets the name of the object. Read/write String. | |
NullString | Returns or sets the string displayed in cells that contain null values when the DisplayNullString property is True. The default value is an empty string (""). Read/write String. | |
PageFieldOrder | Returns or sets the order in which page fields are added to the PivotTable report’s layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default constant is xlDownThenOver. Read/write Integer. | |
PageFields | Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently showing as page fields. Read-only. | |
PageFieldStyle | Returns or sets the style used in the bound page field area. The default value is a null string (no style is applied by default). Read/write String. | |
PageFieldWrapCount | Returns or sets the number of page fields in each column or row in the PivotTable report. Read/write Integer. | |
PageRange | Returns a Range object that represents the range that contains the page area in the PivotTable report. Read-only. | |
PageRangeCells | Returns a Range object that represents only the cells in the specified PivotTable report that contain the page fields and item drop-down lists. | |
Parent | Returns the parent object for the specified object. Read-only | |
PivotColumnAxis | Returns a PivotAxis object representing the entire column axis. Read-only. | |
PivotFormulas | Returns a PivotFormulas object that represents the collection of formulas for the specified PivotTable report. Read-only. | |
PivotRowAxis | Returns a PivotAxis object representing the entire row axis. Read-only. | |
PivotSelection | Returns or sets the PivotTable selection in standard PivotTable report selection format. Read/write String. | |
PivotSelectionStandard | Returns or sets a String indicating the PivotTable selection in standard PivotTable report format using English (United States) settings. Read/write. | |
PreserveFormatting | This property is True if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items. Read/write Boolean. | |
PrintDrillIndicators | Specifies whether or not drill indicators are printed with the PivotTable. Read/write. | |
PrintTitles | True if the print titles for the worksheet are set based on the PivotTable report. False if the print titles for the worksheet are used. The default value is False. Read/write Boolean. | |
RefreshDate | Returns the date on which the PivotTable report was last refreshed. Read-only DateTime. | |
RefreshName | Returns the name of the person who last refreshed the PivotTable report data or the PivotTable cache. Read-only String. | |
RepeatItemsOnEachPrintedPage | True if row, column, and item labels appear on the first row of each page when the specified PivotTable report is printed. False if labels are printed only on the first page. The default value is True. Read/write Boolean. | |
RowFields | Returns an object that represents either a single field in a PivotTable report (a PivotField object) or a collection of all the fields (a PivotFields object) that are currently showing as row fields. Read-only. | |
RowGrand | True if the PivotTable report shows grand totals for rows. Read/write Boolean. | |
RowRange | Returns a Range object that represents the range including the row area on the PivotTable report. Read-only. | |
SaveData | True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean. | |
SelectionMode | Returns or sets the PivotTable report structured selection mode. Read/write XlPTSelectionMode. | |
ShowCellBackgroundFromOLAP | True if the MDX (Multidimensional Expression) that Microsoft Excel sends to the server includes the BackColor property for each cell in the data area that corresponds to a cell in the Online Analytical Processing (OLAP) data set. The default value is False. Read/write Boolean. | |
ShowDrillIndicators | The ShowDrillIndicators property is used for toggling the display of drill indicators in the PivotTable. Read/write. | |
ShowPageMultipleItemLabel | When set to True (default), "(Multiple Items)" will appear in the PivotTable cell on the worksheet whenever items are hidden and an aggregate of non-hidden items is shown in the PivotTable view. Read/write Boolean. | |
ShowTableStyleColumnHeaders | The ShowTableStyleColumnHeadersproperty is set to True if the coulmn headers should be displayed in the PivotTable. Read/write. | |
ShowTableStyleColumnStripes | The Boolean property displays banded columns in which even columns are formatted differently from odd columns. This makes PivotTableseasier to read. Read/write. | |
ShowTableStyleLastColumn | Reserved for internal use. | |
ShowTableStyleRowHeaders | The ShowTableStyleRowHeaders property is set to True if the row headers should be displayed in the PivotTable. Read/write. | |
ShowTableStyleRowStripes | The ShowTableStyleRowStripes property displays banded rows in which even rows are formatted differently from odd rows. This makes PivotTables easier to read. Read/write. | |
ShowValuesRow | Gets or sets whether the values row is displayed. | |
Slicers | Gets the Slicers collection for the specified PivotTable. | |
SmallGrid | True if Microsoft Excel uses a grid that’s two cells wide and two cells deep for a newly created PivotTable report. False if Excel uses a blank stencil outline. Read/write Boolean. | |
SortUsingCustomLists | The SortUsingCustomLists property controls whether custom lists are used for sorting items of fields, both initially when the PivotField is initialized and the PivotItems are ordered by their captions; and later when the user applies a sort. Read/write. | |
SourceData | Returns the data source for the PivotTable report. Read-write Object. | |
SubtotalHiddenPageItems | True if hidden page field items in the PivotTable report are included in row and column subtotals, block totals, and grand totals. The default value is False. Read/write Boolean. | |
Summary | Gets or sets the description associated with the alternative text string for the specified PivotTable. | |
TableRange1 | Returns a Range object that represents the range containing the entire PivotTable report, but doesn’t include page fields. Read-only. | |
TableRange2 | Returns a Range object that represents the range containing the entire PivotTable report, including page fields. Read-only. | |
TableStyle | Returns or sets the style used in the body of the PivotTable report. The default value is a null string (no style is applied by default). Read/write String. | |
TableStyle2 | The TableStyle2 property specifies the PivotTable style currently applied to the PivotTable. Read/write. | |
Tag | Returns or sets a string saved with the PivotTable report. Read/write String. | |
TotalsAnnotation | True if an asterisk (*) is displayed next to each subtotal and grand total value in the specified PivotTable report if the report is based on an OLAP data source. The default value is True. Read/write Boolean. | |
VacatedStyle | Returns or sets the style applied to cells vacated when the PivotTable report is refreshed. The default value is a null string (no style is applied by default). Read/write String. | |
Value | The name of the PivotTable report. Read/write String. | |
Version | Returns the Microsoft Excel version number. Read-only XlPivotTableVersionList. | |
ViewCalculatedMembers | When set to True (default), calculated members for Online Analytical Processing (OLAP) PivotTables can be viewed. Read/write Boolean. | |
VisibleFields | Returns an object that represents either a single field in a PivotTable report (a PivotField object) or a collection of all the visible fields (a PivotFields object). Visible fields are shown as row, column, page, or data fields. Read-only. | |
VisualTotals | True (default) to enable Online Analytical Processing (OLAP) PivotTables to recalculate the total after an item has been hidden from view. Read/write Boolean. | |
VisualTotalsForSets | Gets or sets whether to include filtered items in the totals of named sets for the specified PivotTable. |
Top