Most valuable professional
  • Excel Book Excerpt

Excel Copy Column Widths to a New Range

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.

Copy Column Widths to a New Range

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.

LE10001223.jpg 

Figure 1182 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.

LE10001224.jpg 

Figure 1183 Paste only the 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.

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.

LE10001225.jpg 

Figure 1184 Copy cells and column widths.

Gotcha: Using the icon above 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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: