• Excel Book Excerpt

Excel Preserve Column Widths

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Preserve Column Widths

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

LE10000822.jpg 

Figure 793 After manually applying column widths.

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

LE10000823.jpg 

Figure 794 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 Options ribbon tab. In the Layout and Format tab, uncheck the option for Autofit Column Widths On Update.

LE10000824.jpg  

Figure 795 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+One to display format cells. On the Alignment tab, open the Horizontal Alignment dropdown and choose Center Across Selection.

LE10000825.jpg 

Figure 796 Column widths stay as you set them.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: