PivotTable Methods (Excel)

GitHub-Mark-64px

Contribute to this content

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

Name

Description

AddDataField

Adds a data field to a PivotTable report. Returns a PivotField object that represents the new data field.

AddFields

Adds row, column, and page fields to a PivotTable report or PivotChart report.

AllocateChanges

Performs a writeback operation for all edited cells in a PivotTable report based on an OLAP data source.

CalculatedFields

Returns a CalculatedFields collection that represents all the calculated fields in the specified PivotTable report. Read-only.

ChangeConnection

Changes the connection of the specified PivotTable.

ChangePivotCache

Changes the PivotCache of the specified PivotTable.

ClearAllFilters

The ClearAllFilters method deletes all filters currently applied to the PivotTable. This includes deleting all filters in the PivotFilters collection of the PivotTable object, removing any manual filtering applied and setting all PivotFields in the Report Filter area to the default item.

ClearTable

The ClearTable method is used for clearing a PivotTable. Clearing PivotTables includes removing all the fields and deleting all filtering and sorting applied to the PivotTables. This method resets the PivotTable to the state it had right after it was created, before any fields were added to it.

CommitChanges

Performs a commit operation on the data source of a PivotTable report based on an OLAP data source.

ConvertToFormulas

The ConvertToFormulas method is new in Microsoft Office Excel 2007 and is used for converting a PivotTable to cube formulas. Read/write Boolean.

CreateCubeFile

Creates a cube file from a PivotTable report connected to an Online Analytical Processing (OLAP) data source.

DiscardChanges

Discards all changes in the edited cells of a PivotTable report based on an OLAP data source.

DrillDown

Enables you to drill down into the data within an OLAP or PowerPivot based cube hierarchy.

DrillTo

Enables you to drill to a location within an OLAP or PowerPivot based cube hierarchy.

DrillUp

Enables you to drill up into the data within an OLAP or PowerPivot based cube hierarchy.

GetData

Returns the value for the a data filed in a PivotTable.

GetPivotData

Returns a Range object with information about a data item in a PivotTable report.

ListFormulas

Creates a list of calculated PivotTable items and fields on a separate worksheet.

PivotCache

Returns a PivotCache object that represents the cache for the specified PivotTable report. Read-only.

PivotFields

Returns an object that represents either a single PivotTable field (a PivotField object) or a collection of both the visible and hidden fields (a PivotFields object) in the PivotTable report. Read-only.

PivotSelect

Selects part of a PivotTable report.

PivotTableWizard

Creates and returns a PivotTable object. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.

PivotValueCell

Retrieve the PivotValueCell Object (Excel) object for a given PivotTable provided certain row and column indices.

RefreshDataSourceValues

Retrieves the current values from the data source for all edited cells in a PivotTable report that is in writeback mode.

RefreshTable

Refreshes the PivotTable report from the source data. Returns True if it’s successful.

RepeatAllLabels

Specifies whether to repeat item labels for all PivotFields in the specified PivotTable.

RowAxisLayout

This method is used for simultaneously setting layout options for all existing PivotFields.

ShowPages

Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet.

SubtotalLocation

This method changes the subtotal location for all existing PivotFields. Changing the subtotal location has an immediate visual effect only for fields in outline form, but it will be set for fields in tabular form as well.

Update

Updates the PivotTable report.

Show: