How to Copy and Paste a Column in Excel (5 Handy Ways)

When you work with a large amount of data, it is obvious that you need to copy and paste multiple columns or rows to another cell. It reduces your workload and time and helps you organize your data more accurately. There are lots of ways to copy and paste an entire column in Excel. That’s why you don’t need to panic. These methods are quite simple and easy to use. In this article, we are going to show you some handy and straightforward methods to copy and paste a column in Excel. So, let’s get started.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


5 Methods to Copy and Paste a Column in Excel

Copying and pasting is a common task in an Excel file. You can copy a cell or multiple cells, an entire row or column in Excel. For copying and pasting, we have taken a dataset of “Sales of Jan” and “Sales of Feb” of “Sales Rep“. Now we want to copy column B and then paste it into another column.

Dataset

Not to mention, we have used the ” Microsoft 365” version. You can use any other version at your convenience.


1. Using Keyboard Shortcut

The keyboard shortcut is the most common and widely used feature while copying and pasting in Excel. Almost all people, from beginners to advanced levels, usually know how to copy and paste a certain column by using a keyboard. But we have discussed the steps here to give a proper idea to the readers who don’t know how to copy and paste using a keyboard shortcut. Follow the steps below.

📌 Steps:

  • Firstly, select the entire column that you want to copy and press the CTRL + C button for copying.

Using Keyboard Shortcut to copy and paste a column in excel

  • Secondly, go to the cell where you want to put the copied column. In our example, we choose column F for pasting. So, go to cell F4 and simply press CTRL + V. It pastes the entire column to this new column like the image below.


2. Applying Keyboard and Mouse

You can also copy your column and paste it into another column by using the keyboard and mouse trick. Though it is an unfamiliar method, it is an extremely easy and time-saving task. Follow the below steps to do this operation.

📌 Steps:

  • Initially, you have to select the column that you want to copy and then press and hold the CTRL key.
  • Secondly hover the mouse pointer on the edge of the selected column and you will see a plus (+) sign beside the pointer just like the image. Then left-click on it.

Applying Keyboard and Mouse to copy and paste a column in excel

Eventually, go to the column where you want to paste the column and left-click there.

That’s it! You have pasted your column. So, the output image will be as follows.


3. Using Paste Special

Excel possesses some built-in features to paste-like Paste Special. It helps to paste certain things into your dataset. Follow the simple steps to do that.

📌 Steps:

  • Firstly, select the entire dataset column and press CTRL + C from the keyboard for copying the column.
  • Secondly, right-click on any of the selected cells and choose Paste Special.

Using Paste Special to copy and paste a column in excel

  • A Paste Special dialog wizard pops out. Check the All box if you want to paste all the elements of your column. There are also other options to choose from.
  • Subsequently, press OK.

Paste Special Dialog box

Eventually, you have pasted your column like the image below.


4. Using the Copy Option from Excel Ribbon

There is also an option to copy and paste the formula from the ribbon under the Home tab. It is a very simple and easy task. We have demonstrated the steps to you below. Go through the steps for better clarity.

📌 Steps:

  • First of all, choose the column that you want to copy and go to the Home tab and choose the Copy command from the Clipboard ribbon group.

Using the copy option from the ribbon to copy and paste a column in excel

  • Eventually, move to cell F4 as we want to paste the column there and again go to the Home tab >> pick Paste from the Clipboard.

Paste option from ribbon to copy and paste a column in Excel

Finally, the copied column is pasted into another column just like in the image below.


5. Employing VBA Macros

Also, VBA Macros can copy and paste a cell or a certain column or row. All you need to do is write the code with the Copy and Paste command. Please follow the below steps to use the VBA code.

📌 Steps:

  • Firstly, go to the Developer tab >> pick Visual Basic.

Employing VBA Macros to copy and paste a column in excel

  • A dialog box appears. Choose the Insert tab >> Module >> pick Module1.

  • Write the VBA code in the General box. We have added the code here.
Sub Copy_Single_Cell_Value()
Copy_Sheet = "Employing VBA Macros"
Copy_Cell = "B4:B12"
Paste_Sheet = "Employing VBA Macros"
Paste_Cell = "F4:F12"
Worksheets(Copy_Sheet).Range(Copy_Cell).Copy
Worksheets(Paste_Sheet).Range(Paste_Cell).PasteSpecial Paste:=xlPasteAll
End Sub

VBA code

In the above code, we specified the subject first. Then we assigned our corresponding sheet i.e. “Employing_VBA_Macros” as the input of Copy_Sheet and Paste_Sheet. Later, we added the cell range B4:B12 to copy and F4:F12 to paste. After assigning the input values, we used the Range.Copy method to copy the inputs and Range.PasteSpecial method to paste the copied column. That’s how simple it is. However, you must change the cell range and sheet name if you want to copy the code.

  • Consequently, press the F5 key to run the code, and then your column has been pasted.


How to Copy a Column in Excel with a Formula

We have used the OFFSET function to copy and paste a column in Excel. It is an array function that starts from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.

  • For completing this operation, go to cell F4 and write the formula.
=OFFSET(B4:B12,0,0)

The OFFSET(B4:B12,0,0) syntax takes the cell reference as B4:B12 and the rows as 0 and the width/height is also 0.

How to Copy a Column in Excel with a Formula

Finally, you have pasted the column like the image shown above.


How to Copy and Paste a Column to Another Sheet in Excel

You can copy a column and paste it into another worksheet by using simple keyboard hacks. All you need to know is how to copy and paste certain values with the keyboard. We mentioned it in method 1. We have attached the steps for better visualization.

📌 Steps:

  • Firstly, choose the column that you want to copy and press CTRL + C for copying.

How to Copy and Paste a Column to Another Sheet in Excel

  • Then, go to a new sheet where you want to paste the column and press CTRL + V to paste it.

Sequentially, your pasted column will look like the image above we have shown here.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice section


Conclusion

That’s all about today’s session. And these are some easy methods to copy and paste a column in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

2 Comments
  1. I want paste specific value of a column instead of all the cell inputs. Can it be possible? I will be helpful for me

    • Reply
      Fahim Shahriyar Dipto Nov 30, 2022 at 3:58 PM

      Hello Mr. Wilson,
      Thanks for your valuable comment. If you want to paste only the value you can do it through Paste Special feature.
      Firstly, create a dataset with the RANDBETWEEN function.

      Whenever you press anything opening the file the numbers refresh automatically.

      But, after copying the column with the CTRL + C key, right-click on the cell and choose Paste Special.

      Paste Special dialog box appears. Check the Valuebox there and hit OK.

      Finally, you have pasted the values without dragging anything.

      I hope you got the solution. keep supporting us.
      Regards,
      Fahim Shahriyar Dipto
      Excel & VBA content Developer.

Leave a reply

ExcelDemy
Logo