Your pivot table column widths change back to the default every time you refresh the data. This forces you to manually resize columns repeatedly. The issue occurs because Excel’s default behavior is to reset the layout when updating the pivot cache. This article explains how to change a specific pivot table setting to preserve your custom column widths permanently.
Key Takeaways: Lock Pivot Table Column Width
- PivotTable Options > Layout & Format > Preserve cell formatting on update: This is the main setting that stops column widths from resetting during a refresh.
- PivotTable Analyze > Options: The menu path to access the pivot table options dialog box where the setting is located.
- Disable Autofit column widths on update: A related option that prevents Excel from automatically adjusting widths, which can also disrupt your layout.
Why Pivot Table Column Widths Reset Automatically
Excel pivot tables are designed to be dynamic. When you refresh the data, Excel rebuilds the pivot cache and reapplies its default formatting rules. Column width is considered part of the table’s layout formatting. By default, Excel does not preserve manual adjustments to column widths after a refresh. This is intended to ensure the table structure adapts to new data volumes, but it overrides your preferred visual layout.
The core setting that controls this behavior is named “Preserve cell formatting on update.” When this option is disabled, Excel clears many manual format changes, including column width. Enabling it instructs Excel to maintain the formatting you have applied. A second setting, “Autofit column widths on update,” can also interfere by automatically resizing columns based on new content, even if preservation is on.
Steps to Lock Pivot Table Column Widths
Follow these steps to configure your pivot table so it keeps your chosen column widths after every refresh. You only need to do this once for each pivot table.
- Select any cell inside your pivot table
Click on a cell within the pivot table data area. This activates the PivotTable Analyze and Design tabs on the ribbon. - Open the PivotTable Options dialog
Go to the PivotTable Analyze tab on the ribbon. In the PivotTable group, click the Options button. A dialog box will appear. - Navigate to the Layout & Format tab
In the PivotTable Options dialog, click on the Layout & Format tab. This tab contains the formatting preservation controls. - Enable the key formatting setting
Find the Formatting section. Check the box next to “Preserve cell formatting on update.” This is the primary setting that locks your column widths. - Disable the auto-resize option
In the same Formatting section, uncheck the box for “Autofit column widths on update.” This prevents Excel from making its own width adjustments. - Apply the changes and test
Click OK to close the dialog box. Manually adjust your column widths to your preference. Right-click the pivot table and select Refresh to confirm the widths now remain fixed.
Using a Macro to Apply the Setting to Multiple Tables
If you have many pivot tables, you can use a VBA macro to apply the setting automatically. This method is for advanced users.
- Open the Visual Basic Editor
Press Alt + F11 on your keyboard to open the VBA editor. - Insert a new module
From the menu, go to Insert > Module. A blank code window will appear. - Paste the macro code
Copy and paste the following code into the module window:Sub LockPivotColumnWidths()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.PreserveFormatting = True
pt.HasAutoFormat = False
Next pt
End Sub - Run the macro
Close the editor and return to Excel. Press Alt + F8, select the macro named “LockPivotColumnWidths,” and click Run. This will apply the settings to all pivot tables on the active sheet.
If Column Widths Still Change After Configuration
Pivot Table Style Overrides the Setting
Some built-in pivot table styles have their own width rules. Check your pivot table design. Go to the Design tab and look in the PivotTable Styles gallery. If you are using a style with banded columns or special formatting, try applying the plain “Pivot Style Light 1” to see if the issue stops. You can then customize this basic style.
Workbook Saved in an Older File Format
If your workbook is saved as .xls (Excel 97-2003), advanced pivot table features may not work correctly. Save a copy of your file in the modern .xlsx format. Go to File > Save As, choose a location, and in the “Save as type” dropdown, select “Excel Workbook (*.xlsx).” Reconfigure the pivot table options in the new file.
Macro or Add-in Interfering with Layout
A third-party add-in or a workbook macro might be programmatically refreshing the pivot table and resetting its properties. Test by starting Excel in Safe Mode. Hold the Ctrl key while clicking the Excel icon to launch it. Open your file in Safe Mode and refresh the pivot table. If the column widths stay, an add-in is likely causing the conflict.
Manual Adjustment vs. Preserve Formatting Setting
| Item | Manual Column Adjustment Only | With Preserve Formatting Enabled |
|---|---|---|
| Behavior on Refresh | Widths reset to default | Custom widths are maintained |
| Impact on Other Formatting | Only column width is lost | All cell formatting (fonts, colors, number formats) is also preserved |
| Table Adaptability | High – table fully adjusts to new data size | Low – fixed layout may cause clipping if new data is wider |
| Best Use Case | Temporary analysis where layout is not important | Final reports and dashboards requiring consistent presentation |
You can now stop your pivot table from automatically resizing columns. The Preserve cell formatting on update setting is the definitive solution for locking your layout. For related control, explore the PivotTable Options dialog to also disable grand totals or subtotals. An advanced tip is to save your customized pivot table as a template; right-click the table, select PivotTable Options, and use the “Save as template” button on the Data tab for future use.