Copy Column Widths to a New Range

September 25, 2023 - by Bill Jelen

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.

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.

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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jakob Owens on Unsplash