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.
Not to mention, we have used the ” Microsoft 365” version. You can use any other version at your convenience.
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.
- Firstly, select the entire column that you want to copy and press the CTRL + C button for copying.
- 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.
- 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.
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.
- 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.
- 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.
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.
- 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.
- 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.
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.
- Firstly, go to the Developer tab >> pick Visual Basic.
- 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
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.
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.
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.
- Firstly, choose the column that you want to copy and press CTRL + C for copying.
- 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.
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
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.