Preserve Column Widths

December 09, 2022 - by Bill Jelen

Problem: I’ve nicely formatted my pivot table, including using narrow column widths.

Before choosing from the Filter, the Customer drop-down in B1 says (All) and the column is narrow. — Figure 852. After manually applying column widths.

When I choose a new customer from the filter dropdown in B1, Excel changes all of my column widths.

When you choose Compton Petroleum from B1, the width of column B changes. — Figure 853. Column widths change automatically.

Strategy: There is an option setting to prevent this behavior. Select any cell in the pivot table. Choose the Options button at the left side of the Analyze ribbon tab. In the Layout and Format tab, uncheck the option for Autofit Column Widths On Update.

In PivotTable Options, on the Layout & Format tab, uncheck Autofit column widths on update. — Figure 854. Uncheck Autofit checkbox.

Gotcha: After choosing this setting, you will have to fix your column widths one last time. The column widths don’t miraculously change back to the way they were.

Gotcha: When the column widths are not changing, you may not be able to see the customer selected in B1. To solve this problem, add some fill formatting to C1:E1. Select B1:E1 and press Ctrl+1 to display format cells. On the Alignment tab, open the Horizontal Alignment dropdown and choose Center Across Selection.

Even with longer fields in the filter, the text will extend out into the blank column C1. — Figure 855. Column widths stay as you set them.

This article is an excerpt from Power Excel With MrExcel

Title photo by Siora Photography on Unsplash