Home > Pivot > Macros > Pivot Fields Remove Pivot Fields With Macros Use these sample macros to remove pivot fields from the pivot table layout. Read the instructions for using the macros, then get the code from each section, or download the free workbook. NOTE: If some pivot tables are OLAP-based (Data Model), use these OLAP macros instead. Also see: List all Pivot Fields with Details

Using the Remove Fields Macros

The macros shown below will remove pivot fields from the pivot table layout.

You can copy these macros to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.

Multiple Pivot Tables on Sheet

If there are multiple pivot tables on the sheet, these macros will affect the first pivot table only.

Set pt = ActiveSheet.PivotTables(1)

If you would prefer to select a pivot table, and then run the macro, change the Set pt line to the following:

Set pt = Activecell.PivotTable

Remove All Fields

This macro will remove all the pivot fields from the pivot table layout. Be very careful if using this macro! Make a backup of your file first.

The ClearTable command in this macro will also remove calculated fields, calculated items, custom items and grouping. This is a macro version of use the Clear All command on the Ribbon's Design tab.

When you run the macro, if you see a warning message, click Cancel, if you don't want to go ahead with the clearing.

Sub SelPivotClear()
'CAUTION - clears layout,
'deletes grouping, calc fields & items
Dim pt As PivotTable
On Error Resume Next

Set pt = ActiveSheet.PivotTables(1)
pt.ClearTable

End Sub

Remove All Row Fields

This macro will remove all the row fields from the pivot table layout. If there are multiple Value fields in the pivot table, the Values button might be in the Row area.

If so, the macro will show a message, asking if you want to remove the Values field too.

Click Yes to remove the Values field, and click No to keep it in the Row area.

Sub RemoveAllFieldsRow()
Dim pt As PivotTable
Dim pf As PivotField
Dim lRmv As Long
Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.RowFields
  lRmv = vbYes
  If pf.Name = "Data" Or pf.Name = "Values" Then
    lRmv = MsgBox("Click Yes to remove Values field from Row area." _
      & vbCrLf _
      & "Click No to keep the Values field.", _
        vbQuestion + vbYesNo, "Remove Values?")
  End If
  If lRmv = vbYes Then
    pf.Orientation = xlHidden
  End If
Next pf

End Sub

Remove All Column Fields

This macro will remove all the column fields from the pivot table layout. If there are multiple Value fields in the pivot table, the Values button might be in the Column area.

If so, the macro will show a message, asking if you want to remove the Values field too.

Click Yes to remove the Values field, and click No to keep it in the Column area.

Sub RemoveAllFieldsCol()
Dim pt As PivotTable
Dim pf As PivotField
Dim lRmv As Long

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.ColumnFields
  lRmv = vbYes
  If pf.Name = "Data" Or pf.Name = "Values" Then
    lRmv = MsgBox("Click Yes to remove Values field from Column area." _
      & vbCrLf _
      & "Click No to keep the Values field.", _
        vbQuestion + vbYesNo, "Remove Values?")
  End If
  If lRmv = vbYes Then
    pf.Orientation = xlHidden
  End If
Next pf

End Sub

Remove All Filter Fields

This macro will remove all the filter fields from the pivot table layout.

Sub RemoveAllFieldsFilter()
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.PageFields
  pf.Orientation = xlHidden
Next pf

End Sub

Remove All Value Fields

This macro will remove all the value fields from the pivot table layout. First, any calculated fields are removed, and then the remaining value fields are removed.

Sub RemoveAllFieldsValue()
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField

Set pt = ActiveSheet.PivotTables(1)

'remove calculated fields first,
'if any exist
For Each pf In pt.CalculatedFields
    For Each df In pt.DataFields
      If df.SourceName = pf.Name Then
        With df
          .Parent.PivotItems(.Name) _
            .Visible = False
        End With
        Exit For
      End If
    Next df
Next pf

For Each pf In pt.DataFields
  pf.Orientation = xlHidden
Next pf

End Sub

Download the Sample File

To see the code, and to test the macros, you can download the Remove Pivot Fields Macros file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the code.

More Pivot Table Resources

FAQs - Pivot Tables

Remove Pivot Fields (Normal or OLAP)

Excel Slicer Macros

Pivot Table Introduction

List all Pivot Fields with Details

Grouping Data

Summary Functions

Clear Old Items in Pivot Table