If you’ve ever copied data between two worksheets in Microsoft Excel, you may have noticed that Excel doesn’t copy the column widths of the original worksheet. I find this extremely annoying, particularly when I’ve spent any amount of time re-sizing the columns. Once I copy the column data to the other worksheet, I have to re-size the columns all over again.
Fortunately, there is a workaround! And that’s the Excel Paste Special feature!
Note: In previous versions of Excel, you could accomplish this using Smart Tags. However, in Excel 2007 the process has changed. It is now a two step process.
To copy column widths along with column data in Excel 2007:
- Within the source worksheet, select the column data you want to copy.
- Press Ctrl + C to copy the data.
- Switch to the destination worksheet.
- Click the first cell in the column where you want to paste the data.
- Press Ctrl + V to paste the data. Notice the column widths are not copied at this point.
- Repeat steps 1 through 3 again.
- Select the first cell in the column.
- Click the drop down arrow under the Paste Option and click Paste Special.
- From the Paste Special window, select Column widths and click OK. Excel automatically changes the column widths to match those of the source worksheet.