If you have a spreadsheet with multiple columns that you need to copy, and you want to make sure that all columns maintain their original column widths after pasting to the destination, you can copy the column widths and apply them to other columns. This post will introduce two ways to copy and paste cell data with the same column width as the original data.
Read More: Copy and Paste Cell or Row Heights to Another in Excel
Create a table containing two columns with a width of “15” for column A and “20” for column B. In an open Excel worksheet, the default column width is 10. Now we want to copy them from the range A1:B6 to D1:E6, with the same dimensions after copying the table.
Copy Column Widths via Paste Special Feature in Excel
To copy the width of a column or columns in Excel, you can follow these steps:
1. Select the columns whose widths you want to copy. In this example, select the range A1:B6.
2. Press the “Ctrl+C” keys on your keyboard to copy the columns.
3. Select a cell or range where you want to apply the pasting with column widths. In this example, you can select cell D1 or range D1:E6 to paste the table.
4. Right-click on the selection and choose “Paste Special” -> “Keep Source Column Width” from the context menu.
After this step, the original table is copied and pasted to the destination D1:E6 with the column widths.
Copy Column Widths via Format Painter Tool in Excel
The Format Painter tool can copy the selected row or column’s formatting and apply it to the selected row or columns. You can follow the steps below to copy column widths by Format Painter.
1. Select the columns whose width you want to copy. Click on the column index on the column header to select the entire column. In this example, hold down the mouse and select column indexes A and B by dragging the mouse, then these two columns are selected.
2. Click on the “Format Painter” button in the “Clipboard” group on the “Home” tab of the Excel ribbon. This button looks like a paintbrush.
3. Click on the column or columns where you want to apply the same widths. In this example, hold down the mouse and select the D and E column indexes by dragging the mouse so that these two columns are selected. This step will apply the copied width to the selected columns.
4. You can then use the normal “Copy” and “Paste” functions to copy the cell values to the destination with the same column width as the source table.