How to Lock a Pivot Table in Excel

When working with Excel, you may find yourself using pivot tables to organize and analyze your data more efficiently.

However, there might be instances where you want to lock and protect your Pivot Table from accidental changes or modifications by other users.

This tutorial presents three techniques for locking Pivot Tables in Excel.

Method #1: Protect the Worksheet But Allow Changing of Column and Row Labels

You can protect the worksheet with the Pivot Table in a way that limits user access to only changing column and row labels.

Let’s consider the following Pivot Table showing the annual sales of a particular company.

We want to protect the worksheet so users can only change the column and row labels to view the data.

Below are the steps to do this:

1. On the Review tab, click the Protect Sheet button on the Protect group:

2. On the Protect Sheet dialog box, optionally enter a password on the Password to unprotect sheet box, select the options: Select locked cells, Select unlocked cells, and Use PivotTable and PivotChart.

3. Click OK
4. If you entered a password to unprotect the sheet, reenter it on the Confirm Password dialog box and click OK.

Once the worksheet is protected, many Pivot Table commands are disabled and cannot be used, for example, the following:

• Format Cells, Refresh, and Group and Ungroup.

Also read: How to Connect Slicer to Multiple Pivot Tables

Method #2: Use a VBA Macro to Disable Changing of Column and Row Labels

We can use an Excel VBA macro to disable selection on a Pivot Table.

Suppose we have the following Pivot Table showing the annual sales of a specific company.

We want to use an Excel VBA macro to remove the drop-down arrows from the Pivot Table so that users cannot change the displayed items.

We use the below steps:

1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
2. Click Insert and choose Module to insert a module in the Visual Basic Editor.

3. Copy the following sub-procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub DisableDropDown()
Dim ptbl As PivotTable
Dim pfld As PivotField
On Error Resume Next
Set ptbl = ActiveSheet.PivotTables(1)
  For Each pfld In ptbl.PivotFields
      pfld.EnableItemSelection = False
  Next pfld
End Sub

4. Save your workbook as an Excel Macro-Enabled Workbook (*.xlsm).
5. Press the shortcut Alt + F11 to switch to the active worksheet containing the PivotTable.
6. Press the shortcut Alt + F8 to open the Macro dialog box.
7. On the Macro dialog box, select the DisableDropdown macro and click the Run button.

The DisableDropdown macro is executed, and the drop-down arrows are removed from the Pivot Table:

When the drop-down arrows are no longer available in the Pivot Table, it restricts the user’s ability to modify the displayed items’ view.

Also read: How to Move Pivot Table in Excel?

Method #3: Use Excel VBA to Disable Multiple Features of Pivot Table in Excel

We can apply an Excel macro to disable specific features in Pivot Tables.

For example, we can restrict users from moving fields or utilizing essential functions, such as the Field List, Field Settings, Drilldown, or Refresh.

Suppose we have the following Pivot Table showing the annual sales of a specific company.

We want to create and apply a macro to prevent users from moving fields, using Field List, Drilldown, Field Settings, and Refresh.

We use the below steps:

1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
2. Click Insert and choose Module to insert a module in the Visual Basic Editor.

3. Copy the following sub-procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub LockPivotTable()
Dim pfld As PivotField
On Error Resume Next
With ActiveSheet.PivotTables(1)
  .EnableDrilldown = False
  .EnableFieldList = False
  .EnableFieldDialog = False
  .PivotCache.EnableRefresh = False
  For Each pfld In .PivotFields
    With pfld
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pfld
End With
End Sub

4. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
5. Press the shortcut Alt + F11 to switch to the active worksheet containing the Pivot Table.
6. Press Alt + F8 to open the Macro dialog box.
7. On the Macro dialog box, select the LockPivotTable macro and click the Run button.

The code runs and restricts users from moving fields or utilizing essential functions, such as the Field List, Field Settings, Drilldown, or Refresh.

For example, notice that when you select the Pivot Table, the PivotTable Field List task pane does not appear, and many options on the PivotTable Analyze tab are disabled:

Also read: How to Refresh Pivot Table in Excel? (Shortcut + VBA)

How to Stop Excel From Asking to Enable Macros

By default, Excel disables macros and notifies users, who can manually enable them if desired.

However, this means that with macros disabled, users can alter the Pivot Table, rendering the macro useless.

Suppose you do not want your macro disabled by default.

In that case, you can digitally sign it and select the Disable macros except digitally signed macros option on the Trust Center dialog box.

How to digitally sign a Macro

To digitally sign a macro in Excel, follow these steps:

1. Activate the Visual Basic Editor by pressing Alt+F11. Alternatively, go to the Developer tab and click the Visual Basic button.

2. On the Visual Basic Editor, select the module containing the macro you want to sign digitally.
3. From the Tools menu, select Digital Signature.

4. On the Digital Signature dialog box, click the Choose button to select a certificate.

If you have a certificate installed, select it from the list of certificates and click OK on the Digital Signature dialog box.

After you have digitally signed the macro, the user will see a message stating that the macro is from a trusted source when they open the workbook containing the macro.

The message can reassure the user that the macro is safe to run.

How to Protect Macro From Editing

You can protect your macro to prevent users from unlocking the Pivot Table by accessing and editing the macro.

We use the steps below:

1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
2. Click Tools and select VBAProject Properties.

3. On the VBAProject – Project Properties dialog box, open the Protection tab, select the Lock project for viewing option, enter the password to view project properties on the Password box, reenter it on the Confirm password box and click OK.

4. Close and re-open the workbook.

Users will now be required to enter the password to view the macro.

Also read: How to Copy a Pivot Table in Excel?

Benefits of Locking Pivot Tables in Excel

Locking a pivot table in Excel is not a built-in feature per se, but you can protect or lock cells containing pivot tables using the methods covered above.

This can prevent other users from accidentally or intentionally changing, moving, or deleting important data in the pivot table. This is especially useful when you’re sharing the spreadsheet with others.

Some advantages of locking a Pivot Table include the following:

1. Data Integrity: Locking a pivot table can help maintain data integrity by preventing unintentional edits, deletions, or additions to the data.
2. Consistency: It ensures that the pivot table and its related calculations remain consistent, especially if the Excel file is being shared with multiple users.
3. Security: By protecting the cells, you can keep confidential or sensitive data safe from viewing or editing by unauthorized users. Only authorized users can modify the Pivot Table.
4. Preventing Errors: If a user accidentally changes data in a pivot table, it can lead to errors in calculations and analysis. Locking the pivot table prevents these potential issues.
5. Simplification: Locking a pivot table can make a worksheet less confusing for less experienced users by restricting them from making changes.

Remember that Excel’s protection features aren’t intended as a security measure. They’re more about preventing mistakes.

If a malicious user gets access to a protected worksheet, they can easily find ways around these protections (the protection that you get using VBA is not very robust).

If you need to keep data secure from unauthorized access, you should use other means of data protection.

This tutorial showed three techniques for locking a Pivot Table in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Lock Cells in Excel [Mac, Windows]
• Lock Cells in Excel (Shortcut)
• How to Group by Months in Excel Pivot Table?
• How to Group by Quarters in Excel Pivot Table?
• How to Remove Drop-down List in Excel?