Tuesday, February 26, 2008

How do I copy column widths in Excel?

One of the annoying and time-consuming things about Excel in the past has been the need to set up column widths on each sheet of a workbook. When you copied information accross, the columns widths didn't replicate themselves and you had to format them all over again.

The good news is that those days are over. Excel does allow you to copy and paste your column widths, saving time and keeping everything uniform.

Once you have the columns set up on a sheet you want to have the same widths in other sheets, simply select the columns and copy. Head to the sheet you want to paste those widths into. Instead of the paste command use Paste Special. The Paste Special dialogue box will appear as shown below.


You will notice that there is an option to paste Column Widths. Select this and click OK. Now you can quickly pre-format all the column widths in your worksheet!

4 Comments:

Jean Peuxpu said...

Thanks. Too bad you can't select more than one item in the list and copy column widths, number formats, and anything else you would want to copy.

Anonymous said...

Any way to set a button on the quick access toolbar for this?

Unknown said...

Yes special paste is definetely an option to carry over your formatting, and yes its annoying that its limited to one option. Thats why i would suggest to set excel to automatically adjust the column width. That way, you can special paste a more important format, and still have large enough columns. Further explanation how to do this would be a little too much for a comment, so i just tell you where to read about the automatic column width change: http://www.excel-aid.com/excel-column-widthadapting-row-height-and-column-width.html . Should be useful for new excel users too, maybe a little less so for experience ones. I hope i could help someones with his column width problems :)

Anonymous said...

What do you do if the "Paste Special" dialog box doesn't look like what you have shown?

It thinks I'm trying to copy from a program other than Excel, but I'm not.

Annoying is an understatement.