8 Ways to Copy Paste an Entire Column in Excel

If you want to change the content of your excel column while keeping the original data intact, simply copy it to a new sheet.

You can use the standard method of copying and pasting the cells with static values. If your column contains references, cell formatting, or formulas, you must use a different method.

Copy Paste Using Keyboard Shortcuts

Excel supports Windows keyboard shortcuts for copy-paste.

If you’re fond of using shortcuts, select your data and use the following combination to copy your column:

Ctrl+C

After copying your data, you will see a dashed box surrounding your column. Select the column you wish to paste your data onto and use the following shortcut:

Ctrl+V

Copy Paste an Entire Column Using Mouse

In Excel, you can copy-paste columns using your mouse pointer. Here’s how you can do it.

1. Select the column header.
2. On your keyboard, press and hold the Ctrl key.
3. Place your pointer on the edge of the selected column.
4. Once the pointer has a plus sign above it, drag the column to create a copy.
5. Drop the copied content to the destination column to paste it.
   

Use Context Menu

You also have the option to use Excel’s context menu to copy-paste an entire column.

1. Right-click on the column header you wish to copy.
2. From the context menu, select Copy.
   
3. Right-click on the destination header.
4. Choose the first option under Paste Options.
   

Copy Data from the Adjacent Column

If your data is in the adjacent column, use a special Excel function to copy data from the left. Simply select the cells from your column and hit the following combination to paste your data to the right:

Ctrl+R

Copy Using Clipboard

Once you copy another data from your sheet, you can no longer conventionally paste the previous data from your sheet.

If you want to save your copied data for future use, use the Clipboard utility. You can store up to 24 copied items within and outside your spreadsheet using Clipboard.

1. Copy your columns from your sheet using the above-mentioned steps.
2. From the Home tab, expand the Clipboard section.
   
3. Select the cell you want to paste the data on.
4. From the sidebar, click on the copied items to paste them.
   

Copy Paste Columns with Formulas

If you’ve used relative referencing on the columns, Excel will automatically change your references when you paste them into a different column.

In cell F2, I have used the asterisk operator to multiply data in E2 and D2. If I copy the content of cell F2 to G2, I receive a different value. This is because the referencing changed from E2 and D2 to F2 and E2.

We can create an absolute reference to fix this error. However, this means you can no longer use flash fill to apply the formula to the column.

Therefore, we’ll use a workaround using the Find and Replace tool.

1. On your keyboard, use Ctrl + H to open Find and Replace.
2. In the window, enter the following values and hit Replace all:
   • Find what: =
   • Replace with: #
     

3. Copy your column and paste it using the above-mentioned steps.
   
4. Hit Ctrl+H again.
5. In the window, enter the following details and select Replace all:
   • Find what: #
   • Replace with: =
     

Use Paste Special

As the name suggests, paste special is a special utility in Excel that allows you to paste formulas, formatting, values, and many more.

Additionally, if you’re pasting numbers, you can operate on them even before pasting.

In this sheet, we have a data table in the range A1:D11.

Let’s copy the formatting from range C2:C16 to A2:A16.

1. Select range C2:B16 and hit Ctrl + C on your keyboard.
2. Select range A2:A16 and right-click on it.
3. Choose Paste Special in the context menu.
   
4. Under Paste, choose Formats > OK.
   

Copy the Entire Column Excluding Hidden Rows

Many times, we find the need to hide rows that do not add much value to the data set. However, Excel takes such hidden rows into account when copying columns from the sheet.

To ignore such hidden elements, you’ll have to use an extra command before copying the column.

1. Select the target column.
2. On your keyboard, hit the Alt +; combination.
   
3. Copy the visible content from the column (Ctrl + C).
4. Head to an empty cell and paste it (Ctrl + V).