Excel PivotTable Refresh Breaks Column Widths: Fix

When you refresh a PivotTable in Excel, the column widths often snap back to a default size instead of keeping the widths you carefully set. This happens because Excel’s AutoFit behavior for PivotTable fields overrides your manual column width adjustments after each refresh. This article explains why the widths reset and gives you four reliable methods to stop the resizing. You will learn how to lock your column widths using PivotTable options, VBA code, and formatting tricks.

Why PivotTable Refresh Resets Column Widths

By default, Excel applies an AutoFit behavior to PivotTable columns every time you refresh the data. The setting is controlled by the checkbox Autofit column widths on update inside PivotTable Options. When this checkbox is turned on, Excel recalculates the optimal width for each column based on the longest text or number currently displayed. If you have manually adjusted a column to be wider or narrower than the AutoFit result, the refresh overwrites your adjustment.

A second setting, Preserve cell formatting on update, also affects column widths. When this option is turned off, Excel discards all manual formatting including column widths after a refresh. Both options are located in the same dialog, and they work together to control whether your layout survives a data refresh.

Steps to Disable AutoFit and Preserve Column Widths

The quickest fix is to turn off the AutoFit option and turn on the preserve formatting option. These steps work in Excel 2016, Excel 2019, Excel 2021, and Microsoft 365.

1. Right-click any cell inside the PivotTable
   Select PivotTable Options from the context menu. The PivotTable Options dialog opens.
2. Go to the Layout & Format tab
   Click the Layout & Format tab at the top of the dialog.
3. Disable Autofit column widths on update
   In the Format section, uncheck the box labeled Autofit column widths on update. This stops Excel from resizing columns automatically after a refresh.
4. Enable Preserve cell formatting on update
   Check the box labeled Preserve cell formatting on update. This tells Excel to keep your manual column widths and other cell formatting after a data refresh.
5. Click OK and test a refresh
   Close the dialog by clicking OK. Right-click the PivotTable and choose Refresh. The column widths should remain unchanged.

What to Do If the Widths Still Change

If the column widths still reset after applying the steps above, the problem is likely caused by a PivotTable style that applies column-specific widths or by conditional formatting that triggers a layout recalculation. Try clearing all conditional formatting rules from the PivotTable range. Also switch to a plain PivotTable style by selecting PivotTable Design > PivotTable Styles > Clear.

Use a VBA Macro to Lock Column Widths Permanently

For users who refresh PivotTables frequently, the manual settings above may not be enough if the preserve formatting option fails due to a corrupted workbook or a shared environment. A VBA macro can save the current column widths and reapply them after every refresh.

1. Open the VBA Editor
   Press Alt + F11 to open the Visual Basic for Applications editor.
2. Insert a new module
   In the Project Explorer, right-click VBAProject (YourWorkbookName) and select Insert > Module.
3. Paste the macro code
   Copy and paste the following code into the module:

   
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim rng As Range
    Dim col As Range
    Dim colWidths() As Double
    Dim i As Long
    
    Set ws = Sh
    Set pt = Target
    Set rng = pt.TableRange1
    
    ReDim colWidths(1 To rng.Columns.Count)
    For i = 1 To rng.Columns.Count
        colWidths(i) = rng.Columns(i).ColumnWidth
    Next i
    
    Application.EnableEvents = False
    For i = 1 To rng.Columns.Count
        rng.Columns(i).ColumnWidth = colWidths(i)
    Next i
    Application.EnableEvents = True
End Sub


4. Save the workbook as a macro-enabled file
   Press Ctrl + S. In the Save As dialog, choose Excel Macro-Enabled Workbook (xlsm) from the file type dropdown.
5. Test the macro
   Close the VBA editor. Refresh any PivotTable in the workbook. The macro saves the widths before the refresh and reapplies them after the refresh completes.

Alternative Method: Copy and Paste Values With Widths

If you do not want to use VBA and the settings above still fail, you can copy the entire PivotTable and paste it as values. This removes the PivotTable functionality but preserves your column widths permanently.

1. Select the entire PivotTable
   Click any cell inside the PivotTable, then press Ctrl + A twice to select the entire PivotTable range including the report filter area.
2. Copy the selection
   Press Ctrl + C.
3. Paste as values with source column widths
   Right-click the starting cell where you want the static data. Under Paste Options, click the Keep Source Column Widths icon. The data is pasted as static values with the exact column widths you set.

If Excel Still Has Issues After the Main Fix

PivotTable Column Widths Change After Refreshing With New Data

If you add new rows to the source data and the new data contains longer text, the PivotTable may expand even with AutoFit disabled. This occurs because the column width is set to a fixed value, but Excel recalculates the width if the cell content is longer than the column width and the Wrap Text option is turned off. To prevent this, manually set the column width to a value wider than any expected content, or enable Wrap Text for the PivotTable range.

Column Widths Reset After Opening a Shared Workbook

When multiple users edit a shared workbook, the PivotTable settings may revert because each user’s Excel client applies its own default options. Ask all users to apply the same PivotTable Options as described in the main fix. Alternatively, use the VBA macro approach because it runs automatically for every user regardless of their local settings.

PivotTable Columns Disappear or Collapse to Zero Width

A column width of zero happens when the PivotTable field is empty after a refresh. This is not a bug but a result of the data source not returning any values for that field. Check the source data for missing or filtered records. To hide empty columns automatically, use the PivotTable field list to remove the field or apply a filter that excludes empty values.

Quick Repair vs Online Repair: Key Differences

Item	PivotTable Options Setting	VBA Macro
Description	Disables AutoFit and preserves formatting via built-in dialog	Saves and reapplies column widths programmatically after each refresh
Ease of setup	Simple, no coding required	Requires VBA knowledge and macro-enabled workbook
Works for all users	Only applies to the user who sets the option	Runs for all users who open the workbook with macros enabled
Handles new longer data	No, column width stays fixed	Yes, macro can be modified to adjust widths dynamically
Risk of corruption	None	Low, but macros can be blocked by security settings

Now you can refresh your PivotTables without losing your column width adjustments. Start by turning off Autofit column widths on update and enabling Preserve cell formatting on update in PivotTable Options. If the problem persists, use the VBA macro to lock widths permanently. For a completely static report, paste the PivotTable as values with source column widths.