Pivot Table Issues

How to Prevent Excel PivotTable Column Widths from Changing

Aamir Naveed Akram Oct 9, 2026 870 views

Question details

The user wants to stop PivotTable columns from automatically adjusting their width every time the data is refreshed.

How to Prevent Excel PivotTable Column Widths from Changing

Product: Excel
Device & OS: not provided
Scenario: Refreshing a PivotTable with new data while trying to maintain a consistent spreadsheet layout.
Observed behavior: When the PivotTable refreshes, the columns automatically resize to fit the data, often causing headers to expand unnecessarily and requiring the user to manually readjust the column widths.

Before you start

Before proceeding, manually adjust your PivotTable columns to your preferred widths so they are perfectly set when you lock the formatting.

Solution 1Recommended

Disable Autofit Column Widths in PivotTable Options

Use this solution to permanently lock the column widths for a specific PivotTable so it stops resizing upon refresh.

By default, Excel is configured to automatically adjust the column widths of a PivotTable whenever you refresh the data. You can easily turn off this default behavior within the PivotTable Options menu.

Open PivotTable Options

Right-click anywhere inside your existing PivotTable and select 'PivotTable Options...' from the context menu.

Navigate to Layout & Format

In the PivotTable Options dialog box, click on the 'Layout & Format' tab located at the top.

Disable Autofit

Look for the checkbox labeled 'Autofit column widths on update' and clear it (uncheck the box).

Apply Changes

Click 'OK' at the bottom of the dialog box. Your column widths will now remain locked even when you refresh the data.

Disable Autofit Column Widths in PivotTable Options

Formatting Saved: Your column widths are now locked. Keep in mind that this setting applies only to the selected PivotTable; you will need to repeat this for other PivotTables.

Efficient Data Analysis

Easily Manage PivotTable Formatting with WPS Spreadsheet

WPS Spreadsheet provides powerful PivotTable tools that let you fully customize and lock your layout. Create, refresh, and format data summaries without losing your preferred column widths.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your PivotTable.
2. Access Options: Right-click any cell within the PivotTable and select 'PivotTable Options'.
3. Adjust Layout Settings: Go to the 'Layout & Format' tab.
4. Lock Column Width: Uncheck 'Autofit column widths on update' and click OK to preserve your layout permanently.

Easily lock PivotTable layouts and preserve formatting upon refreshFully compatible with Microsoft Excel .xlsx files and PivotTable structuresFamiliar interface makes it easy to transition without a learning curveLightweight software that processes large data models quickly

microsoft office alternative - wps office

Frequently Asked Questions

How do I preserve cell background colors when updating a PivotTable?

In the same PivotTable Options dialog under the 'Layout & Format' tab, make sure the box for 'Preserve cell formatting on update' is checked. This will keep your colors, borders, and fonts intact after a refresh.

Will disabling autofit truncate my data if new text is longer?

Yes, if the new data entering the PivotTable exceeds the manual column width you set, it may visually cut off the text. You can manually double-click the column boundary to expand it if a specific field needs more room later.

Is there a way to apply this setting to all PivotTables at once?

By default, the autofit setting must be adjusted for each PivotTable individually through its specific options menu. Changing it globally for all existing tables simultaneously would require the use of a custom VBA macro.

Excel & Data Problems