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.
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: 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: 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: 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 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:
How to Copy Conditional Formatting to Another Cell in Excel How to Copy Excel Table to MS Word (4 Easy Ways) How to Copy and Paste Formulas in Excel without Changing Cell References How to Quickly Copy Chart (Graph) Format in Excel How to Multiply in Excel Using Paste Special Copy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent) How to Cut a Cell Value in Excel (Keyboard Shortcuts)
