The PivotTable Formatting Reset
PivotTables are one of Microsoft Excel’s most powerful data analysis tools. However, they come with a highly frustrating default setting: every time you refresh the data or change a filter, Excel automatically recalculates and resizes the column widths to fit the new data. If you have spent ten minutes carefully adjusting your column widths to fit a specific layout for a printed report or a PDF export, hitting the “Refresh” button will instantly destroy all your hard work, snapping the columns back to their default, jagged sizes.
How to Disable Autofit Column Widths on Update
You can easily stop this destructive behaviour by modifying the internal options of the specific PivotTable, forcing it to permanently respect the column widths you manually define.
1. Open the Excel workbook containing your PivotTable.
2. Right-click anywhere inside the PivotTable data area.
3. Select PivotTable Options… from the context menu.
4. In the dialogue box that appears, ensure you are on the Layout & Format tab at the top.
5. Look under the “Format” section.
6. Locate the checkbox labelled Autofit column widths on update.
7. Untick this checkbox.
8. Click OK to save the changes.
Preserving Additional Formatting
While you are in the PivotTable Options menu, you should also verify the setting directly below the Autofit option: Preserve cell formatting on update. Ensure this box remains ticked. Disabling the Autofit stops the columns from stretching, but ensuring the “Preserve cell formatting” box is ticked guarantees that any custom background colours, bold text, or custom number formats (like currency symbols) you applied to the cells will also survive the next data refresh.