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

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.
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
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.
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
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.
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 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.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
How to Plan and Set Up a Pivot Table
Last updated: December 27, 2024 11:53 AM