Home > Pivot > Layout > Protection Excel Pivot Table Protection Use macros to protect a pivot table, or specific features of a normal pivot table (not Data Model). Also, use code to refresh a pivot table on a protected worksheet For protection and restrictions on Data Model pivot tables, go to the Pivot Table Data Model Restrictions page

Pivot Table on Protected Sheet

You can protect a worksheet and allow users to make some changes to the pivot tables. However, there are some restrictions on the pivot table functions, when the sheet is protected. Watch this video to see how to set up the protections, and there are written details below the video.

Allow Pivot Table Use on Protected Sheet

When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables.

Pivot Table Limitations on Protected Sheet

Even if you allow the use of pivot tables on a protected worksheet, there are limitation to what you can do. NOTE: Pivot tables on other sheets may be limited too, if they are based on the same pivot cache.

The following pivot table commands (and others) are disabled when a sheet is protected:

• Report Layout
• Subtotals and Grand Totals
• Refresh
• Group and Ungroup
• PivotTable Options
• Calculated Field and Calculated Item
• PivotTable Styles

To use these features, temporarily unprotect the worksheet.

Disable Selection

To protect the pivot table selections from being changed, you can use a macro to disable selection. The dropdown arrows will disappear from the field buttons, and you won't be able to change the displayed items.

To see the steps in a video, go to the Pivot Table Filter Videos page.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub DisableSelection()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = False
  Next pf
End Sub   

Note: You could also disable selection for a specific field. In this example, the arrow is removed from the first Report Filter field (PageFields).

Sub DisableSelectionSelPF()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField


On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields(1)

    pf.EnableItemSelection = False

End Sub

Enable Selection

The following macro enables the features that were turned off in the DisableSelection macro, shown above. The drop down arrows are visible again.

To see the steps in a video, go to the Pivot Table Filter Videos page.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub EnableSelection()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField


On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = True
  Next pf
End Sub

Note: You could also enable selection for a specific field. In this example, the arrow is shown only for the "Region" field. To do this, run the DisableSelection macro (above), then run this EnableSelectionSelPF macro.

Sub EnableSelectionSelPF()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField


On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Region")
    pf.EnableItemSelection = True
End Sub

Restrict PivotTable

You can use a macro to disable many of the features in a pivot table. The following code prevents users from dragging any of the fields to a different location, or off the pivot table. It also prevents them from using the Field List, Field Settings, or Drilldown, and disables the Refresh feature.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub RestrictPivotTable()
'pivot table tutorial by contextures.com
Dim pf As PivotField
On Error Resume Next

With ActiveSheet.PivotTables(1)
  .EnableDrilldown = False
  .EnableFieldList = False   
  .EnableFieldDialog = False
  .PivotCache.EnableRefresh = False
  For Each pf In .PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
End With

End Sub

Allow PivotTable

The following macro enables the features that were turned off in the RestrictPivotTable macro, shown above. For example, you are able to move the fields to a different area.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub AllowPivotTable()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next

Set pt = ActiveSheet.PivotTables(1)
With pt
  .EnableDrilldown = True
  .EnableFieldList = True 
  .EnableFieldDialog = True
  .PivotCache.EnableRefresh = True
  For Each pf In pt.PivotFields
    With pf
      .DragToPage = True
      .DragToRow = True
      .DragToColumn = True
      .DragToData = True
      .DragToHide = True
    End With
  Next pf
End With

End Sub

Change Macro to Run on All Pivot Tables or Sheets

The macros in the previous sections are set up to run on the first pivot table on the active worksheet. You can change the macros slightly, so they run on all pivot tables on the active sheet, or all pivot tables, on all sheets in the active workbook.

All pivot tables on the active sheet

To have the macro run on all pivot tables on the active sheet, remove the line:

    Set pt = ActiveSheet.PivotTables(1)

and replace it with

    For Each pt in ActiveSheet.PivotTables

At the end of the section, create a new line

    Next pt

For example, here is the EnableSelection macro, revised to run on all pivot tables in the active sheet.

Sub EnableSelectionAllPT()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField


On Error Resume Next
For each pt in ActiveSheet.PivotTables
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = True
  Next pf
Next pt
End Sub

NOTE: If you are using "For Each pf in pt. PivotFields", add an If...Then section, to ensure that the code doesn't run on the "Values" field (called "Data" in older versions), which is created when there are multiple value fields. The example below will run on all pivot tables on the active sheet..

Sub AllowPivotTableSheet()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
For Each pt In ws.PivotTables
   With pt
     .EnableWizard = True
     .EnableDrilldown = True
     .EnableFieldList = True
     .EnableFieldDialog = True
     .PivotCache.EnableRefresh = True
     For Each pf In pt.PivotFields '<== uses .PivotFields
       With pf
         If .Name <> "Values" And .Name <> "Data" Then
            .DragToPage = True
            .DragToRow = True
            .DragToColumn = True
            .DragToData = True
            .DragToHide = True
         End If
       End With
     Next pf
   End With
Next pt
End Sub

All pivot tables in the active workbook

To have the macro run on all pivot tables, on all sheets, in the active workbook, add the line:

    dim ws as Worksheet

At the start of the code, add this line

    For Each ws in ActiveWorkbook.Worksheets

At the end of the section, create a new line

    Next ws

For example, here is the EnableSelectionAllPT macro, revised to run on all pivot tables, on all sheets, in the active workbook.

Sub EnableSelectionAllPTAllSheets()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Dim ws as Worksheet

On Error Resume Next
For each ws in ActiveWorkbook.Worksheets
  For each pt in ActiveSheet.PivotTables
    For Each pf In pt.PivotFields
        pf.EnableItemSelection = True
    Next pf
  Next pt
Next ws
End Sub

Refresh Pivot Table on Protected Sheet

When a worksheet is protected, you can't refresh the pivot tables on that sheet. You could manually unprotect the worksheet, refresh the pivot table, and then protect the sheet again.

If you're trying to refresh the pivot table with a macro, add code to unprotect the worksheet, refresh the pivot table, and then protect the sheet again. This macro shows an example.

Sub UnprotectRefresh()
On Error Resume Next

With Activesheet
  .Unprotect Password:="mypassword"
  .PivotTables(1).RefreshTable
  .Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
End With
    
End Sub

NOTE: If other pivot tables are connected to the same pivot cache are on protected sheets, you can use the code below, to unprotect all the sheets.

Refresh Pivot Table Connected to Protected Sheet

If you try to refresh a pivot table, you might see a message that says: "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data."

This occurs if the pivot table that you're trying to refresh shares the same pivot cache as another pivot table, and that pivot table is on a protected sheet.

As the message says, you could manually unprotect the other sheet, and then refresh the pivot table.

Another option, if you're trying to refresh the pivot table programmatically, is to unprotect all the sheets, refresh the pivot tables, and then protect the sheets again.

Sub UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
    ws.Unprotect Password:="mypassword"
Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
  ws.Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
Next ws
    
End Sub

Get the Sample File

Get the sample pivot table tutorial file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when opening the file, if you want to test the macros.

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Data Model Restrictions

Pivot Table Introduction

Grouping Data

Pivot Cache Macros

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table