PivotTable Methods (Excel)
Contribute to this content Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation. |
Methods
Name |
Description |
---|---|
Adds a data field to a PivotTable report. Returns a PivotField object that represents the new data field. |
|
Adds row, column, and page fields to a PivotTable report or PivotChart report. |
|
Performs a writeback operation for all edited cells in a PivotTable report based on an OLAP data source. |
|
Returns a CalculatedFields collection that represents all the calculated fields in the specified PivotTable report. Read-only. |
|
Changes the connection of the specified PivotTable. |
|
Changes the PivotCache of the specified PivotTable. |
|
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. |
|
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. |
|
Performs a commit operation on the data source of a PivotTable report based on an OLAP data source. |
|
The ConvertToFormulas method is new in Microsoft Office Excel 2007 and is used for converting a PivotTable to cube formulas. Read/write Boolean. |
|
Creates a cube file from a PivotTable report connected to an Online Analytical Processing (OLAP) data source. |
|
Discards all changes in the edited cells of a PivotTable report based on an OLAP data source. |
|
Enables you to drill down into the data within an OLAP or PowerPivot based cube hierarchy. |
|
Enables you to drill to a location within an OLAP or PowerPivot based cube hierarchy. |
|
Enables you to drill up into the data within an OLAP or PowerPivot based cube hierarchy. |
|
Returns the value for the a data filed in a PivotTable. |
|
Returns a Range object with information about a data item in a PivotTable report. |
|
Creates a list of calculated PivotTable items and fields on a separate worksheet. |
|
Returns a PivotCache object that represents the cache for the specified PivotTable report. Read-only. |
|
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. |
|
Selects part of a PivotTable report. |
|
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. |
|
Retrieve the PivotValueCell Object (Excel) object for a given PivotTable provided certain row and column indices. |
|
Retrieves the current values from the data source for all edited cells in a PivotTable report that is in writeback mode. |
|
Refreshes the PivotTable report from the source data. Returns True if it’s successful. |
|
Specifies whether to repeat item labels for all PivotFields in the specified PivotTable. |
|
This method is used for simultaneously setting layout options for all existing PivotFields. |
|
Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet. |
|
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. |
|
Updates the PivotTable report. |