Copy Column Widths & Contents to Another Sheet in Excel
Posted: Sat Jan 14, 2017 4:43 pm
Let's say, I would like to copy and paste the following Excel table as it is to another sheet.
Here you see, one student is in red as he is below the pass mark 45. Two are above 75 – very good, and marked with blue and bold.
![Image](http://shamskm.com/forum/exceltutor/excel2/how_to_use_paste_special_in_excel.jpg)
So I would like to keep the column width and the format as it is while pasting it to another sheet.
I will do it with Paste Special option.
First, Select the range (A1:B10) > right mouse click > Copy
Go to the sheet where you want to paste it. Click on the cell where you would like to paste it > right mouse click > click on Paste Special... > select Column Widths > click OK > you will see the column widths have changed.
![Image](http://shamskm.com/forum/exceltutor/excel2/paste_special_option_in_excel_for_column_width.jpg)
Click again on the top left cell > right mouse click > click on Paste Special... > select Formats > click OK.
Click again on the top left cell > right mouse click > click on Paste Special... > select Values > click OK.
So everything is copied to the new sheet like the one in the previous sheet.
If you have formulas in your selected range and you would like it to remain along with the column widths and format, then you first select from Paste Special: > Column Widths > OK, then Formats > OK, then Formulas > OK. That's it.
Just play a little bit with Paste Special option, you will discover a lot more![Smile :-)](./images/smilies/icon_smile.gif)
Extra:
By the way, if you have a large range or you have selected a tiny portion from a large range, and you are confused about the range size but you need this information for any reason (either to put it in your report or to tell your colleague), then do as follows:
Select your range > right mouse click > click Name a Range. Here under Refers to you will see the current sheet no./sheet name and the range. You can also name your selected range here.
![Image](http://shamskm.com/forum/exceltutor/excel2/name_a_range_in_excel.jpg)
Here you see, one student is in red as he is below the pass mark 45. Two are above 75 – very good, and marked with blue and bold.
![Image](http://shamskm.com/forum/exceltutor/excel2/how_to_use_paste_special_in_excel.jpg)
So I would like to keep the column width and the format as it is while pasting it to another sheet.
I will do it with Paste Special option.
First, Select the range (A1:B10) > right mouse click > Copy
Go to the sheet where you want to paste it. Click on the cell where you would like to paste it > right mouse click > click on Paste Special... > select Column Widths > click OK > you will see the column widths have changed.
![Image](http://shamskm.com/forum/exceltutor/excel2/paste_special_option_in_excel_for_column_width.jpg)
Click again on the top left cell > right mouse click > click on Paste Special... > select Formats > click OK.
Click again on the top left cell > right mouse click > click on Paste Special... > select Values > click OK.
So everything is copied to the new sheet like the one in the previous sheet.
If you have formulas in your selected range and you would like it to remain along with the column widths and format, then you first select from Paste Special: > Column Widths > OK, then Formats > OK, then Formulas > OK. That's it.
Just play a little bit with Paste Special option, you will discover a lot more
![Smile :-)](./images/smilies/icon_smile.gif)
Extra:
By the way, if you have a large range or you have selected a tiny portion from a large range, and you are confused about the range size but you need this information for any reason (either to put it in your report or to tell your colleague), then do as follows:
Select your range > right mouse click > click Name a Range. Here under Refers to you will see the current sheet no./sheet name and the range. You can also name your selected range here.
![Image](http://shamskm.com/forum/exceltutor/excel2/name_a_range_in_excel.jpg)