How to Lock Pivot Table Column Width

By default, a Pivot Table automatically adjusts column widths to fit new or updated content when refreshed or filtered.

This default setting can disorganize your worksheet’s layout and be frustrating especially if you have shapes (e.g. slicers), charts, or data outside the Pivot Table. 

In this tutorial, I’ll show you how to lock your Pivot Table’s column widths to preserve your worksheet’s layout. 

Method #1: Turn Off Autofit Column Widths on Update

You can disable the ‘Autofit column widths on update’ option on the Pivot Table Options dialog box.

This will ensure the column widths you have set for your Pivot Table remain unchanged when the Pivot Table is refreshed or filtered regardless of the size of new or updated content.

Suppose you have the Pivot Table below showing a particular bank’s new deposits, broken down by branch and account type. You want the current column widths of the Pivot Table unchanged when refreshing or filtering.

Here’s how you can lock the Pivot Table’s column widths:

1. Right-click any cell in the Pivot Table and select Pivot Table Options on the shortcut menu.

The above step opens the Pivot Table Options dialog box.

2. On the Layout & Format tab of the PivotTable Options dialog box, deselect the ‘Autofit column widths on update’ option and click OK.

After the above step, when you refresh the Pivot Table its column widths are preserved regardless of the size of the new or updated content.

Note: After locking the column widths, if new or updated data contains longer text or larger numbers than the column widths allow, the content may appear truncated or cut off, making it harder to read. To mitigate this, set column widths that can accommodate all possible scenarios.

Turn Off Autofit Column Widths on Update in All New Pivot Tables

You can disable the ‘Autofit column widths on update’ option at the workbook level if you want it to apply to all NEW Pivot Tables in the workbook.

Here’s how to do it:

1. Click the File button on the Ribbon to open the Backstage view window.
2. Click Options on the left sidebar of the Backstage view window.

The above step opens the Excel Options dialog box.

3. On the Excel Options dialog box, click Data on the left sidebar, and then click on the ‘Edit Default Layout’ button on the right.

The above step opens the Edit Default Layout dialog box.

4. Click the PivotTable Options button on the Edit Default Layout dialog box.

The above step opens the Pivot Table Options dialog box.

5. On the Layout & Format tab of the PivotTable Options dialog box, deselect the ‘Autofit column widths on update’ option and click OK.

6. Click OK on the Edit Default Layout dialog box.
7. Click OK on the Excel Options dialog box.

All NEW Pivot Tables you create in the workbook will now have locked column widths.

Also read: How to Clear Pivot Table Cache?

Method #2: Protect Sheet to Lock Pivot Table’s Column Widths

You can protect a sheet to prevent users from adjusting a Pivot Table’s column widths.

Suppose you have the Pivot Table below showing a particular bank’s new deposits, broken down by branch and account type. You want to prevent users from changing the column widths of the Pivot Table.

Here’s how you can do it.

1. Open the Review tab and click the Protect Sheet button on the Protect group.

The above step opens the Protect Sheet dialog box.

2. On the Protect Sheet dialog box, enter a password to unprotect the sheet, select the ‘Use PivotTable and PivotChart’ option, and click OK.

The steps above will protect the sheet while allowing users to interact with the Pivot Table. However, the users will be prevented from refreshing the Pivot Table or manually adjusting its column widths.

Also read: How to Lock a Pivot Table in Excel

Method #3: Turn Off Autofit Column Widths on Update Using VBA 

If you have many Pivot Tables in your workbook, turning off the ‘Autofit column widths on update’ option in each Pivot Table can be time-consuming.

Use the VBA code below to disable the ‘Autofit column widths on update’ setting for all existing Pivot Tables in your current workbook.

Sub UncheckAutofitColumnWidths()
Dim ws As Worksheet
Dim pt As PivotTable
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      pt.HasAutoFormat = False
    Next pt
  Next ws
End Sub

When you execute the code, it cycles through all worksheets in the current workbook, locates Pivot Tables in each sheet, and turns off the ‘Autofit column widths on update’ option for each Pivot Table.

I have shown you ways to lock a Pivot Table’s column widths in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• Cannot Group That Selection Error in Pivot Tables
• How to Change Data Source in Pivot Table
• How to Add Calculated Field to Pivot Table?
• How to Group by Months in Excel Pivot Table?
• How to Delete a Pivot Table in Excel?
• Change a Pivot Table to Tabular Form