Excel: Copy Column Widths to a New Range

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 small report in columns A:G. I made a copy of that report in column H. The column widths did not get copied over. It is a pain to individually look at each column width in A through G and then make the same column width in the new report.

Strategy: First, you don't have to open the Column Width dialog to see the column width. Between each column header, there is a spot where you can click and drag to change the column width. If you simply click there and hold the mouse button down without moving the mouse, you can see the width of the column.

  1. Reveal the size of this column.

    The fast solution to this problem is to use the relatively new Paste Column Widths. You can select A1:G1, press Ctrl+C to copy, select cell H1, and do Alt+E+S+W for Paste Special Column Widths.

    Microsoft quietly added Column Widths to the Paste Special dialog a few versions ago. It is much easier than using the Format Painter on entire columns to copy the column widths.

  2. Paste only the column widths.

    In Excel 2010, the Paste Options dialog has an icon for Keep Source Column Widths. Immediately after copying the report, you could use this paste option to bring the column widths along with the original paste.

    Gotcha: Using the W icon will copy the cell contents as well as the column widths. Using Paste Special and then Column Widths will paste the column widths without pasting the data.

For more resources for Microsoft Excel