VBA- Pivot Table Fields: The Pivot Fields collection contains all the fields from the data source, including any calculated fields. The main aspect of adding a field is its Position and Orientation. Each pivot field has its own orientation i.e. column orientation, row orientation, page orientation, or data orientation. It is always advisable to explicitly define the field parameter as the developer decides the positioning at the beginning of any report creation. These actions only affect the given Pivot Table, not the Pivot Cache.

The commonly used Pivot fields are as follows:

  1. RowFields
  2. ColumnFields
  3. PageFields
  4. DataFields
  5. HiddenFields
  6. VisibleFields
  7. AddFields

RowFields

It returns a single field PivotTable object or the collection of all row fields. It is used to add the PivotTable’s row field names to a list in the worksheet. It can be created using ‘AddField’ feature.

Program:

Output

RowFields

ColumnField

It returns a single field PivotTable object or the collection of all column fields. It is used to add the PivotTable’s column field names to a list in the worksheet. It can be created using ‘AddField’ feature.

In the below example, we have used to “Region” Field in column orientation with Central, East, and West as its values.

Program:

ColumnField

PageFields

It returns a single field PivotTable object or the collection of all page fields. It is used to add the PivotTable’s page field names to a list in the worksheet. It can be created using ‘AddField’ feature.

Program:

Output

PageFields

DataFields

It returns the “sum of” the fields for the PivotTable object. These are not displayed in the PivotFields collection. It is different from the other three fields as it cannot be added using AddField property.

In the below example, we have used the “Units” field as our DataFields and have calculated the sum of each Region’s unit individually. At last, we have formatted the value using number format with two zeros.

Program:

Output

DataFields

HiddenFields

This field is used to return the hidden fields in a Pivot Table.

Program:

Output

HiddenFields

VisibleFields

VisibleFields is the vice-versa of the Hidden Field. It is used to display all the fields used up in the Pivot Table. 

Program:

Output

VisibleFields

AddFields

The AddFields method is used to add multiple fields (RowFields, ColumnFields, AddtoTable, or PageFields) in our Pivot Table. It accepts all types of fields to accept the DataField. The user can even apply Array Function to add multiple fields in one location.

Program:

Output

AddFields

Pin It on Pinterest

Share This