Contextures

Macro Lists All Pivot Table Fields

Use these macros to create a list of the fields in a pivot table, and details for each field, such as caption and position. Also see Macro to List All Pivot Fields and Pivot Items

Introduction

Create a list of the pivot fields in a pivot table, with information about each field, such as the caption, layout location, and position. Use these Excel macros to make the list, then add your comments in the Notes column, if necessary, to document what each field contains.

list of pivot fields

For OLAP-based pivot tables, the list will show bracketed source names and sample items.

list of pivot fields

Create List of Pivot Table Fields

The following code adds a new sheet, named "Pivot_Fields_List", to the workbook. Then it creates a list of all the pivot fields in the first pivot table on the active sheet.

  • NOTE: If there is an existing sheet with that name, it is deleted. If you want to keep previous lists, rename the sheets before running the macro again.

The list includes the pivot field's caption, source name, layout location (orientation), position in that location, sample data from the field, and a formula (for calculated fields). The Notes column is blank, and you can add comments there, about each field.

Because of a problem with the Orientation method, all the data fields are identified as "Hidden". Additional code checks the "Hidden" fields, to see if they are in the DataFields.go to top

Sub OrderList_PivotFields()
'contextures.com
'use the Notes column
' for comments about fields
Dim lRow As Long
Dim wsList As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim strList As String
Dim strLoc As String
strList = "Pivot_Fields_List"
Application.DisplayAlerts = False

On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
If pt Is Nothing Then
  MsgBox "No pivot table on active sheet"
  GoTo exitHandler
End If
Sheets(strList).Delete
On Error GoTo errHandler

Set wsList = Sheets.Add
lRow = 2

With wsList
  .Name = strList
  .Cells(1, 1).Value = "Caption"
  .Cells(1, 2).Value = "Source Name"
  .Cells(1, 3).Value = "Location"
  .Cells(1, 4).Value = "Position"
  .Cells(1, 5).Value = "Sample Item"
  .Cells(1, 6).Value = "Formula"
  .Cells(1, 7).Value = "Notes"
  .Rows(1).Font.Bold = True
  
  For Each pf In pt.PivotFields
    If pf.Caption <> "Values" Then
        .Cells(lRow, 1).Value = pf.Caption
        .Cells(lRow, 2).Value = pf.SourceName
        Select Case pf.Orientation
          Case xlHidden
            strLoc = "Hidden"
          Case xlRowField
            strLoc = "Row"
          Case xlColumnField
            strLoc = "Column"
          Case xlPageField
            strLoc = "Page"
          Case xlDataField
            strLoc = "Data"
        End Select
        If strLoc = "Hidden" Then
          For Each df In pt.DataFields
            If df.SourceName _
                = pf.SourceName Then
              strLoc = "Data"
              Exit For
            End If
          Next df
        End If
        .Cells(lRow, 3).Value = strLoc
        .Cells(lRow, 4).Value = pf.Position
        
        On Error Resume Next
        If pf.PivotItems.Count > 0 Then
          .Cells(lRow, 5).Value _
              = pf.PivotItems(1).Value
        End If
        On Error GoTo errHandler
  
        'print the formula for calculated fields
        If pf.IsCalculated = True Then
          .Cells(lRow, 6).Value = _
              Right(pf.Formula, Len(pf.Formula) - 1)
        End If
  
        lRow = lRow + 1
      End If
  Next pf
  .Columns("A:G").EntireColumn.AutoFit
End With

exitHandler:
  Application.DisplayAlerts = True
  Exit Sub
errHandler:
  MsgBox "Could not create list"
  Resume exitHandler

End Sub

Create List of Pivot Fields By Location

The following code adds a new sheet, named "Pivot_FieldLoc_List", to the workbook. Then it creates a list of all the pivot fields in the first pivot table on the active sheet.

  • NOTE: If there is an existing sheet with that name, it is deleted. If you want to keep previous lists, rename the sheets before running the macro again.

The list is organized by location type, and includes the pivot field's caption, source name, location (orientation), position at that location, sample data from the field, and a formula (calculated fields). The Notes column is blank, and you can add comments there, about each field.

The Value fields are listed as "Data", but no sample data or formulas are shown for those fields. go to top

Sub LocList_PivotFields()
'contextures.com
'use the Notes column
' for comments about fields
Dim lRow As Long
Dim wsList As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim strList As String
Dim lLoc As Long
strList = "Pivot_FieldLoc_List"
Application.DisplayAlerts = False

On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
If pt Is Nothing Then
  MsgBox "No pivot table on active sheet"
  GoTo exitHandler
End If

Sheets(strList).Delete
On Error GoTo errHandler

Set wsList = Sheets.Add
lRow = 2

With wsList
  .Name = strList
  .Cells(1, 1).Value = "Caption"
  .Cells(1, 2).Value = "Source Name"
  .Cells(1, 3).Value = "Location"
  .Cells(1, 4).Value = "Position"
  .Cells(1, 5).Value = "Sample Item"
  .Cells(1, 6).Value = "Formula"
  .Cells(1, 7).Value = "Notes"
  .Rows(1).Font.Bold = True
  
  For Each pf In pt.PageFields
    lLoc = pf.Orientation
    .Cells(lRow, 1).Value = pf.Caption
    .Cells(lRow, 2).Value = pf.SourceName
    .Cells(lRow, 3).Value = lLoc & " - Page"
    .Cells(lRow, 4).Value = pf.Position
    On Error Resume Next
    If pf.PivotItems.Count > 0 Then
      .Cells(lRow, 5).Value _
          = pf.PivotItems(1).Value
    End If
    On Error GoTo errHandler
    lRow = lRow + 1
    lLoc = 0
  Next pf
  
  For Each pf In pt.RowFields
    lLoc = pf.Orientation
    If pf.Caption <> "Values" Then
      .Cells(lRow, 1).Value = pf.Caption
      .Cells(lRow, 2).Value = pf.SourceName
      .Cells(lRow, 3).Value = lLoc & " - Row"
      .Cells(lRow, 4).Value = pf.Position
      On Error Resume Next
      If pf.PivotItems.Count > 0 Then
        .Cells(lRow, 5).Value _
            = pf.PivotItems(1).Value
      End If
      On Error GoTo errHandler
      lRow = lRow + 1
      lLoc = 0
    End If
  Next pf
  
  For Each pf In pt.ColumnFields
    lLoc = pf.Orientation
    If pf.Caption <> "Values" Then
      .Cells(lRow, 1).Value = pf.Caption
      .Cells(lRow, 2).Value = pf.SourceName
      .Cells(lRow, 3).Value = lLoc & " - Column"
      .Cells(lRow, 4).Value = pf.Position
      On Error Resume Next
      If pf.PivotItems.Count > 0 Then
        .Cells(lRow, 5).Value _
            = pf.PivotItems(1).Value
      End If
      On Error GoTo errHandler
      lRow = lRow + 1
      lLoc = 0
    End If
  Next pf
  
  For Each pf In pt.DataFields
    lLoc = pf.Orientation
    Set df = pt.PivotFields(pf.SourceName)
    .Cells(lRow, 1).Value = df.Caption
    .Cells(lRow, 2).Value = df.SourceName
    .Cells(lRow, 3).Value = lLoc & " - Data"
    .Cells(lRow, 4).Value = df.Position
    On Error Resume Next
      'print formula for calculated fields
      '.Cells(lRow, 6).Value = " " & pf.Formula
        If df.IsCalculated = True Then
          .Cells(lRow, 6).Value = _
              Right(df.Formula, Len(df.Formula) - 1)
        End If
    On Error GoTo errHandler
    lRow = lRow + 1
    lLoc = 0
    Set df = Nothing
  Next pf
      
  For Each pf In pt.HiddenFields
    lLoc = pf.Orientation
    If pf.Caption <> "Values" Then
      .Cells(lRow, 1).Value = pf.Caption
      .Cells(lRow, 2).Value = pf.SourceName
      .Cells(lRow, 3).Value = lLoc & " - Hidden"
      .Cells(lRow, 4).Value = pf.Position
      
      On Error Resume Next
      'sample item in field, where applicable
      .Cells(lRow, 5).Value = pf.PivotItems(1).Value
      'print formula for calculated fields
      .Cells(lRow, 6).Value = " " & pf.Formula
      On Error GoTo errHandler
      
      lRow = lRow + 1
      lLoc = 0
    End If
  Next pf
  .Columns("A:G").EntireColumn.AutoFit
End With

exitHandler:
    Application.DisplayAlerts = True
    Exit Sub
errHandler:
    MsgBox "Could not create list"
    Resume exitHandler

End Sub

Download the Sample File

Get the Pivot Fields List Macros sample file, with two pivot tables for testing, and the macros from this page. The zipped file is in xlsm format, and be sure to enable macros when you open the workbook, if you want to try the macros.

More Pivot Table Tutorials

How to Plan and Set Up a Pivot Table

Excel Slicer Macros

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Table Errors

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

go to top

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: March 19, 2019 4:25 PM
Contextures RSS Feed