How to Copy Column Widths in Excel?

by

Microsoft Excel is an excellent choice for creating a small database for storing information such as employee records, test scores, or even small inventory items.

In some scenarios, we want to copy data from one column to the other or even another sheet.

In such cases, you may want to copy the data and paste it as is, including copying the column widths.

However, when we copy the data from one column to another, the column widths do not carry over.

Instead of adjusting the column width manually, let me show you a better way to copy and paste data.

In this tutorial, I will show you different methods of copying data, where it also copies the column widths.

Method 1: Copying Column Width Using the Context Menu

In this method, we will look at how we can copy data, spanning over multiple columns, including the column width, using the context menu.

This menu appears when you right-click over a cell.

As an example, I will use the following dataset, which shows the position and annual salary of employees working in a company.

dataset data set that we need to copy

Below are the steps to copy the dataset along with the column width:

  1. Select the entire dataset as shown.
select the data set
  1. Right-Click anywhere inside the selected text to bring up the context menu as shown.
right click on the selection
  1. From this context menu, select Copy.
click on copy
  1. Now right-click on the cell where you want to copy this data (cell E1 in my example). Right-clicking will show the context menu.
right click on the destination cell
  1. In the context menu, hover your mouse cursor over the arrow icon next to the Paste Special option.
hover the cursor over the paste special arrow icon
  1. The following sub-menu will appear.
sub menu appears
  1. From this sub-menu, click on the Keep Source Column Widths option under the Paste section (as shown below).
click on the Keep Source Column Widths option
  1. The final result will look like the one shown below.
Data copied along with column widths

You can see that the data copied in columns E, F, and G have the same column width as the original data in columns A, B, and C.

Also read: Get Text to Fit in Cell in Excel

Method 2: Copying Column Width Using the Home Tab Toolbar

In this method, we will see how to copy data, including the column widths using the toolbar available under the Home Tab.

We will use the same dataset as we did in the previous method.

Here are steps to do this:

  1. Select the entire dataset as shown.
select the data set
  1. Click on the Home Tab.
click the home tab
  1. Under the Home Tab, click on the Copy button.
click on copy
  1. Select cell E1 (where I want to paste the copied data).
select the cell where you want to paste
  1. Under the Home Tab, click on the little arrow under the Paste option (as shown below).
Click on the paste option
  1. This will bring up the Paste options menu as shown.
Paste option menu
  1. From the Paste options menu, click on the Keep Source Column Widths option under the Paste section.
click on the Keep Source Column Widths
  1. The result will look like the one shown below.
Data is pasted along with column widths

You can see that the copied data has the same column widths as the original data. This is similar to what we saw in the first method as well.

Also read: How to Make Excel Columns the Same Width

Method 3: Copy Column Width Using the Paste Special Options Menu

In this method, we will see how we can copy data, including column widths using the Paste Special options menu.

Once again, we will use the sample dataset, which was used in the previous methods as well.

  1. Select the entire dataset as shown.
select the data set
  1. Click on the Home Tab.
click the home tab
  1. Under the Home Tab, click on the Copy button.
click on copy
  1. Select cell E1 as shown.
select the destination cell
  1. Under the Home Tab, click on the little arrow under the Paste option (as shown below).
click on paste
  1. This will bring up the Paste Options menu (as shown below).
paste special menu
  1. From the Paste Options menu, click on the Paste Special option.
take on the paste special icon
  1. The Paste Special window will appear as shown.
paste special window
  1. Select the Column Widths option.
select column width
  1. Click on OK.
click OK
  1.  The end result will look like the one shown below.
only column widths are pasted

Unlike the previous methods, you can see that this method has only copied the column widths and not the data.

If you want to copy the data as well, you will have to do that additionally. For that, follow the additional steps below.

  1.  Select the original data as shown.
select the original data
  1.  Press ‘Ctrl + C’ on your keyboard to copy the data.
  2.  Select cell E1 as shown.
select the destination cell
  1.  Press ‘Ctrl + V’ on your keyboard to paste the copied data.
  2.  The result will be as shown.
paste the data

In this method, we saw how to copy the column widths only (without copying data). If we want to copy the data as well, we have to perform additional steps in order to do that.

Method 4: Copy Column Width Using the Column Width Option

In this method, we will use the Column Width option, which is available through the context menu for copying column widths.

Let’s see an example using the sample dataset used in the previous methods.

  1. Select the entire dataset as shown.
  1. Press Ctrl + C on your keyboard to copy the data.
  2. Select cell E1 as shown.
select the destination cell
  1. Press ‘Ctrl + V’ on your keyboard to paste the copied data.
  2. The result will be as shown.
paste the data

You can see that the data has been copied, but the column width has not. 

  1. Right-Click on the top of the first column from the original dataset. In this case, this is column A.
right click on the column label header
  1. From the Context Menu that appears, select the Column Width option.
select column width option
  1. The Column Width window for column A will appear as shown.
column width dialog box
  1. Note down the value shown against the Column width field. In this case, it is 19.29.
  2.  Press the OK button to close the window.
click OK
  1. Right-click on the top of the first column of the copied data. In this case, it is column E.
select the column in destination range
  1.  From the context menu that appears, click on the Column Width option.
click on column width
  1.  The Column width window for column E will appear as shown.
column width dialog box
  1.  In the Column width field, type the value noted previously in step 9. In this case, this was the column width value of column A i.e. 19.29.
enter the column width value
  1.  Click the OK button.
click OK
  1.  The result will look like the one shown below.
column width has been changed

As you can see, the width of column E is now the same as that of column A. 

  1.  Repeat steps 6 to 16 for the other columns as well so that the result looks like the one below.
repeat the process for all columns

So in this tutorial, I showed you different methods of copying column widths.

In the first two methods, the column width, along with the data, is copied. Use the first two methods if you want to copy data in addition to the column width.

In the third method, only the column width is copied. Use this method if only the column width needs to be copied while leaving out the data.

This may be useful if you want to enter new data on a new column or sheet and just want the column widths to match.

The fourth method is helpful if you want to keep the column width the same for data that is similar in multiple excel files.

If you have data in separate files, then noting down the individual column widths from one file and changing them in the other will allow you to keep the same visual feel across both files.

If you simply want the width of two columns to match, method 4 is the most useful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Copy Column Widths in Excel?”

Leave a Comment