When working with Excel spreadsheets, copying and pasting data is a frequent task.
And many times, you will need to copy and paste an entire column (or multiple columns) in Excel. It could be a copy-paste in the same worksheet, or in any other worksheet or workbook.
In this tutorial, I will cover everything that you need to know about copy-pasting columns in Excel.
There there are multiple ways to do it. You can choose to copy and paste an entire column or multiple columns as is, or only copy the values or formatting, or formulas from a column.
Let’s see how to do all this.
This Tutorial Covers:
Copying a Column As-Is Using a Keyboard Shortcut
Suppose you have a dataset as shown below and you want to copy column A and paste it as Column D.
For the purpose of illustration, I have also highlighted Column A in yellow color.
If all you want is to copy a column and paste it, below are the steps to get this done:
- Select the column that you want to copy. To do this, click on the column header letter (which is at the top of the column)
- With the entire column selected, use the keyboard shortcut – Control + C (or Command + C if using Mac). This will copy the entire selected column (you will see dancing ants at the borders)
- Select the destination column where you want to paste the copied column
- Paste it using the keyboard shortcut – Control + V (or Command + V if using a Mac)
The above steps would copy the selected column and paste it into the destination.
Note that this would copy everything from the source column to the destination column (including values, formatting, and formulas).
In case there is conditional formatting applied in the column, it would also be copied.
For this to work, you will have to select an entire destination column. If you only select a cell or a range in the destination column (as not the whole column), you will get an error.
Note: In this example, I have used the keyboard shortcuts Control + C and Control + V. You can also get the same thing by right-clicking on the selection and then clicking on Copy or Paste options.
Copying a Column As-Is Using a Keyboard+ Mouse Trick
Another really quick way to copy a column and paste it into the destination is by using a simple keyboard and mouse combo.
Suppose you have a dataset as shown below and you want to copy column A and paste it over Column D.
Below are the steps to do this:
- Select the column that you want to copy
- Hold the Control key (or Command key in Mac)
- Place the mouse cursor at the edge of the selected column. You will notice that it changes to pointed arrow with a plus sign
- With the Control/Command key pressed, press the left mouse key and drag the column to the position where you want it copied
The above steps would copy and then paste the columns that you dragged to the destination location.
Note that this technique will also work with multiple contiguous columns, but it won’t work with non-contiguous columns. For example, if you select columns A and D, then you won’t be able to use this, but you can use this if you select columns A and B.
Copy Paste a Column using Paste Special (Copy Value or Formatting or Formula Only)
Many times, you don’t need to copy the entire column with all the data and the formatting and formulas.
You may only need to copy the formatting or only copy the values without the formatting.
This can easily be done using the paste special feature in Excel.
Paste Special allows to copy and then paste specific elements from the copied data (such as values or formulas or formatting)
Suppose you have a dataset as shown below, and you want to copy and paste only values from column A to column D.
Below are the steps to do this using Paste Special:
- Select the column that you want to copy (column A in this example)
- Copy the column (or the range in the column). You can do this using Control + C (or Command + C) or right-click on the selection and then click on Copy
- Right-clcik on the destination cell (D1 in this example)
- Click on Paste Special option. This will open the Paste Special dialog box
- Select Values
- Click Ok
The above steps copy the entire column A, but only paste the values and not the formatting.
If you only want to paste the formatting, you can select the ‘Formats’ option in the Paste Special dialog box.
In case you want to copy values as well as formatting (but nothing else such as formulas), you can repeat the process twice. So copy Column A, then first paste values and then paste formatting.
Shortcut to Open Paste Special dialog box: ALT + E + S
Some other options that you get when using Paste Special:
- Column width
- Formulas and Number formats
- Values and Number formats
- Everything except the borders
- Comments and Notes
- Data validation (drop down lists)
So these are some of the ways you can use to copy and paste columns in Excel.
If you want to copy the entire column, you can use the first two methods, and in case you want to selectively copy something from the column, then you can use the Paste Special technique.
I hope you found this tutorial useful!
Other Excel tutorials you may also find useful: