Pivot Table Issues

Fix Excel PivotTable Column Width Changing After Double-Clicking Details

Camila Milosovich Oct 10, 2026 871 views

Question details

The user is experiencing an issue where Excel automatically changes custom PivotTable column widths after double-clicking a value to generate a detail worksheet, even when formatting settings are configured correctly.

How to Fix Excel PivotTable Column Widths Changing Unexpectedly After Double-Clicking Details

Product: Microsoft Excel
Device & OS: not provided
Scenario: Double-clicking a summarized data value within a PivotTable to extract and view the underlying detail data in a new worksheet.
Observed behavior: The column widths in the original PivotTable unexpectedly resize or lose their custom formatting when the detail worksheet is created, despite Autofit being disabled.

Before you start

Verify that your Microsoft Office application is updated to the latest version, as unexpected formatting changes despite correct settings are often linked to known Excel software defects.

Solution 1Recommended

Verify PivotTable Autofit and Formatting Settings

Ensure the built-in PivotTable options are correctly configured to preserve your custom column layouts during data interactions.

Excel has default settings that automatically adjust column widths whenever a PivotTable is updated, refreshed, or interacted with. Disabling this feature is the primary way to lock your column widths.

Open PivotTable Options

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

Navigate to Layout & Format

In the PivotTable Options dialog box, ensure you are on the 'Layout & Format' tab.

Adjust Formatting Rules

Check the box next to 'Preserve cell formatting on update'. Then, uncheck the box next to 'Autofit column widths on update'.

Apply and Test

Click 'OK' to save the settings. Double-click a value in your PivotTable to generate the detail worksheet and verify if the column widths remain intact.

Verify PivotTable Autofit and Formatting Settings

Setting Persistence: These settings apply only to the current PivotTable. You must configure these options individually if your workbook contains multiple PivotTables.

Solution 2

Test in a New Workbook and Report the Defect

If settings are correctly applied but the issue persists, the behavior may be a known software defect requiring a fresh file or an official bug report.

Free Microsoft Office alternative

Experience Flawless PivotTables with WPS Office

Tired of persistent Excel bugs disrupting your spreadsheet formatting? WPS Office provides a lightweight, highly compatible alternative. Enjoy stable PivotTables, familiar interfaces, and seamless workflow migration without unexpectedly losing your custom layouts.

1. Download and Install: Get the free version of WPS Office from the official website and install it on your device.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the problematic PivotTable.
3. Analyze Data Without Glitches: Double-click your PivotTable data to view details confidently, knowing your custom column formatting will remain securely in place.

100% format compatibility with Microsoft Excel (.xlsx, .xls) filesStable PivotTable features that strictly respect your custom column widths and formattingFree to use with a highly familiar, intuitive spreadsheet user interfaceLightweight application that runs smoothly on almost any computer hardware

microsoft office alternative - wps office

Frequently Asked Questions

Why do my PivotTable columns automatically resize when I refresh the data?

By default, Excel enables the 'Autofit column widths on update' setting. This forces the columns to expand or shrink to fit the longest text string upon every data refresh or interaction. You can disable this feature in the PivotTable Options under the Layout & Format tab.

Can I set a default column width for all future PivotTables I create?

No, Excel does not offer a global application setting to dictate default PivotTable column widths or to permanently disable the Autofit feature for all future files. You must adjust the layout and disable the Autofit setting manually for each new PivotTable.

What exactly does 'Show Details' do when I double-click a PivotTable value?

Double-clicking a summarized numeric value in a PivotTable triggers the 'Show Details' feature. This generates a brand new worksheet within your workbook, extracting and displaying only the specific underlying source data rows that make up that summarized value.

Excel & Data Problems