Home > Pivot > Macros > Field List Macros List 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.

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

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.

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.

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

List All Pivot Fields All Pivot Tables

The following macro adds a new sheet to the active workbook. On that sheet, it creates a list of all the pivot fields, in all the pivot tables, on all sheets, in the active workbook.

The fields for each pivot table are listed by location type (row, column, filter or value), and by their position within that location.

Each row in the list includes the sheet name, pivot table name, pivot table address, pivot field's caption, field heading cell location, source name, location (orientation), position at that location, sample data* from the field, formula (calculated fields), and OLAP-based (True or False).

*Sample data and formulas are NOT shown for value fields, or OLAP-based pivot tables (e.g. Data Model).

NOTE: Hidden fields are not included in this list.

Macro to List All Pivot Fields All Pivot Tables

Copy this macro code to a regular code module in your workbook, then run the macro when required.

Sub LocList_ALL_PTs_PFs()
'contextures.com
'lists all pivot tables in
' active workbook
'use the Notes column to
' add comments about fields
Dim lRow As Long
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim lLoc As Long
Dim lPos As Long
Dim pfCount As Long
Dim myList As ListObject
Dim bOLAP As Boolean
Application.DisplayAlerts = False

On Error GoTo errHandler

Set wsList = Sheets.Add
lRow = 2

With wsList
  .Cells(1, 1).Value = "Sheet"
  .Cells(1, 2).Value = "PT Name"
  .Cells(1, 3).Value = "PT Address"
  .Cells(1, 4).Value = "Caption"
  .Cells(1, 5).Value = "Heading"
  .Cells(1, 6).Value = "Source Name"
  .Cells(1, 7).Value = "Location"
  .Cells(1, 8).Value = "Position"
  .Cells(1, 9).Value = "Sample Item"
  .Cells(1, 10).Value = "Formula"
  .Cells(1, 11).Value = "OLAP"
  .Rows(1).Font.Bold = True
  
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      bOLAP = pt.PivotCache.OLAP
      
      For pfCount = 1 To pt.RowFields.Count
        Set pf = pt.RowFields(pfCount)
        lLoc = pf.Orientation
        If pf.Caption <> "Values" Then
        .Cells(lRow, 1).Value = ws.Name
        .Cells(lRow, 2).Value = pt.Name
        .Cells(lRow, 3).Value = pt.TableRange2.Address
        .Cells(lRow, 4).Value = pf.Caption
        .Cells(lRow, 5).Value = pf.LabelRange.Address
        .Cells(lRow, 6).Value = pf.SourceName
        .Cells(lRow, 7).Value = lLoc & " - Row"
        .Cells(lRow, 8).Value = pfCount
          On Error Resume Next
          If pf.PivotItems.Count > 0 _
            And bOLAP = False Then
            .Cells(lRow, 9).Value _
                = pf.PivotItems(1).Value
          End If
          On Error GoTo errHandler
        .Cells(lRow, 11).Value = bOLAP
          lRow = lRow + 1
          lLoc = 0
        End If
      Next pfCount
      
      For pfCount = 1 To pt.ColumnFields.Count
        Set pf = pt.ColumnFields(pfCount)
        lLoc = pf.Orientation
        If pf.Caption <> "Values" Then
        .Cells(lRow, 1).Value = ws.Name
        .Cells(lRow, 2).Value = pt.Name
        .Cells(lRow, 3).Value = pt.TableRange2.Address
        .Cells(lRow, 4).Value = pf.Caption
        .Cells(lRow, 5).Value = pf.LabelRange.Address
        .Cells(lRow, 6).Value = pf.SourceName
        .Cells(lRow, 7).Value = lLoc & " - Column"
        .Cells(lRow, 8).Value = pfCount
          On Error Resume Next
          If pf.PivotItems.Count > 0 _
            And bOLAP = False Then
            .Cells(lRow, 9).Value _
                = pf.PivotItems(1).Value
          End If
          On Error GoTo errHandler
        .Cells(lRow, 11).Value = bOLAP
          lRow = lRow + 1
          lLoc = 0
        End If
      Next pfCount
      
      For pfCount = 1 To pt.PageFields.Count
        Set pf = pt.PageFields(pfCount)
        lLoc = pf.Orientation
        .Cells(lRow, 1).Value = ws.Name
        .Cells(lRow, 2).Value = pt.Name
        .Cells(lRow, 3).Value = pt.TableRange2.Address
        .Cells(lRow, 4).Value = pf.Caption
        .Cells(lRow, 5).Value = pf.LabelRange.Address
        .Cells(lRow, 6).Value = pf.SourceName
        .Cells(lRow, 7).Value = lLoc & " - Filter"
        .Cells(lRow, 8).Value = pfCount
        On Error Resume Next
          If pf.PivotItems.Count > 0 _
            And bOLAP = False Then
          .Cells(lRow, 9).Value _
              = pf.PivotItems(1).Value
        End If
        On Error GoTo errHandler
        .Cells(lRow, 11).Value = bOLAP
        lRow = lRow + 1
        lLoc = 0
      Next pfCount
      
      For pfCount = 1 To pt.DataFields.Count
        Set pf = pt.DataFields(pfCount)
        lLoc = pf.Orientation
        
        Set df = pt.PivotFields(pf.SourceName)
        .Cells(lRow, 1).Value = ws.Name
        .Cells(lRow, 2).Value = pt.Name
        .Cells(lRow, 3).Value = pt.TableRange2.Address
        .Cells(lRow, 4).Value = df.Caption
        .Cells(lRow, 5).Value = _
              pf.LabelRange.Cells(1).Address
       .Cells(lRow, 6).Value = df.SourceName
        .Cells(lRow, 7).Value = lLoc & " - Data"

        .Cells(lRow, 8).Value = pfCount
        'sample data not shown for value fields
        On Error Resume Next
          'print formula for calculated fields
          '.Cells(lRow, 6).Value = " " & pf.Formula
            If df.IsCalculated = True Then
              .Cells(lRow, 10).Value = _
                  Right(df.Formula, Len(df.Formula) - 1)
            End If
        On Error GoTo errHandler
         .Cells(lRow, 11).Value = bOLAP
       lRow = lRow + 1
        lLoc = 0
        Set df = Nothing
      Next pfCount
            
    Next pt
  Next ws
  .Columns("A:K").EntireColumn.AutoFit
  Set myList = .ListObjects.Add(xlSrcRange, _
      Range("A1").CurrentRegion)

End With
MsgBox "Done"

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

End Sub

Get 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