When you copy a cell or range of cells in Excel and paste it somewhere else, it pastes the entire content of the cells, including the formatting.
But do you know that you can also copy the column width?
Excel allows you to copy cells and paste selectively (such as only pasting the values or formatting or column width)
In this short tutorial, I will show you how to use Paste Special options to copy and paste column widths in Excel. I will also give you the keyboard shortcut to copy column width in Excel.
So let’s get started!
This Tutorial Covers
Copy Paste Column Width Using Context Menu
The best way to copy and paste column width is by using the options you get in the context menu when you right-click while pasting.
Below I have a data set where I have the sales values of different stores for three months, and you can see that the column width for the months has been reduced.
Below are the steps to copy and paste this data along with the column width:
- Select the data set that you want to copy
- Copy the data set (you can use the shortcut Control + C or right-click and then click on the Copy option)
- Right-click on the destination cell where you want to paste this data
- Hover the cursor over the icon to the right of the Paste Special option. It will show you some additional options there
- Click on the Keep Source Column Width(s) option
That’s it – the above steps would paste the copy data as well as adhere to the column widths from the copy data (as you can see in the screenshot below)
Copy Paste Column Width Using Paste Special
Below I have a data set where I have the sales values of different stores for three months.
Suppose I copy this data and paste it into another worksheet.
In that case, it will copy and paste the values and the formatting (including cell fill color and border), but it will not copy the column width (as you can see in the screenshot below).
The column width in the data we copied is more
While this may not be an issue if you’re working with a small dataset, as you can adjust the columns widths manually, doing this with large datasets would be time-consuming and inefficient.
In such cases, you can use the steps below to copy and paste the column width using Paste Special:
- Select the cells that you want to copy
- Copy the selected cells. You can use the keyboard shortcut Control + C (for Windows) or Command + C (for Mac), or right-click on the selected cells and then click on Copy
- Select the destination cell where you want to paste the copied cells
- Right-click and then click on paste or use the shortcut Control + V (for Windows) or Command + V (for Mac)
- Right-click again and then click on Paste Special
- In the Paste Special dialog box, select the Column widths option
- Click OK
The above steps would copy and paste the data as well as the column width in the destination range.
Note that in this case, I had to paste the data two times:
- First, I copied and pasted the data using the regular paste option. This would paste the cell values as well as the formatting but not the column widths
- So in step 5, I again had to right-click and use the Paste Special option to paste only the column widths
At the time of writing this article, there is no way for you to copy and paste the data, including the column widths.
Sometimes, you may want only to copy the formatting and the column widths, but not the cell content.
In such a case, you can copy the cells, go to the destination cell, right-click and then click on Paste Special, and then only paste the Column widths.
Shortcut to Copy Paste Column Width in Excel
Below is the keyboard shortcut to paste column width in Excel:
ALT + E + S + W + Enter
Here is how to use this shortcut to paste column widths only:
- Copy the cells from which you want to copy and paste the column width
- Select the destination cell
- Use the keyboard shortcut ALT + E + S + W + Enter (press these keys one after the other)
The above keyboard shortcut works by opening the Paste Special dialog box, selecting the Column widths option in the dialog box, and pasting it.
Copy Column Width Using Format Painter
Another easy way to copy column widths in Excel is by using the Format Painter option.
Format painter works by copying the format from the selected cells that can then be applied to any other range of cells you select using your mouse.
Below are the steps to use format painter to copy column width in excel:
- Select the columns from which you want to copy the column width. To do this, you will have to select the column labels at the top of the columns (which is the letter at the top of each column). you can select one or more than one columns at a time
- Copy these columns
- In the Home tab, within the Clipboard group, click on the Format Painter icon.
- Go to the destination cell and click on the column header where you want to copy the column widths.
One limitation of the Format Painter when copying column width is that it is going to copy the entire formatting from one column and copy it to another column (which would include the column width as well)
Pro Tip: If you want the Format Painter to remain active so you can copy the formatting to multiple columns, double-click on the format painter icon in step 3. Double-clicking on the icon would keep it active until you hit the escape key or click on the format painter icon again
In this tutorial, I covered three methods you can use to copy and paste column widths in Excel.
I like the keyboard shortcut method as it’s fast and easy (Once you get used to using the shortcut).
If you’re not a fan of shortcuts, you can use the Paste Special dialog box to copy column widths.
And in specific cases where you want to copy the column width and apply it to multiple columns, you can use the Format Painter method.
Other articles you may also like:
- Copy Row Height in Excel
- How to Copy Conditional Formatting to Another Cell in Excel
- Excel AUTOFIT: Make Rows/Columns Fit the Text Automatically
- Row vs Column in Excel – What’s the Difference?
- Select Till End of Data in a Column in Excel (Shortcuts)
- How to Copy and Paste Column in Excel?
- How to Change Row Height in Excel
- How to Lock Row Height & Column Width in Excel