Excel: Change the Width of All Columns with One Command

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 have a large model set up in Excel. Some of the columns are hidden. I want to globally change the width of all unhidden columns to a width of 4. If I choose all columns in the worksheet and use Home, Format dropdown, Column Width, the hidden columns will unhide.

Strategy: To solve this problem, you can use Home, Format dropdown, Default Width.

The Default Width dialog allows you to enter one global column width. This change will affect all columns that have not been previously resized or hidden. The result is that you can change the width of all columns without unhiding the hidden columns.

  1. Change the default column width.

    Additional Details: Changing the default width will change the width of hidden columns, but will not unhide them. When they are later unhidden, they will have the new width.

    Gotcha: The Default Width command does not change the widths of columns that have previously been changed. To see this in action, open a new workbook. Manually change column C to be 20 wide. Use Home, Format dropdown and set Default Width to be 1 wide. All the columns except C will be changed.

For more resources for Microsoft Excel