Excel: Preserve Column Widths

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

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

  1. After manually applying column widths.

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

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

  3. 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.

  4. Column widths stay as you set them.