Copying a column in Excel sounds simple, but there are several ways to do it, and the right one depends on whether you want to overwrite existing data, insert the column without replacing anything, keep formatting and column width, or paste only the values. This guide covers every method for Excel on Windows, Mac, and the web, plus how formulas behave when you copy them and how to fix common problems.
Quick Answer
Click the column letter to select the whole column, press Ctrl + C (Cmd + C on a Mac), click the letter of the destination column, and press Ctrl + V. To add the copy without overwriting anything, right-click the destination column letter and choose Insert Copied Cells instead.
Which Method Should You Use?
| You want to | Use |
|---|---|
| Copy a column over an empty or unwanted column | Copy and Paste (Method 1) |
| Add a copy without overwriting existing data | Insert Copied Cells (Method 2) |
| Copy quickly with the mouse | Ctrl + drag (Method 3) |
| Copy only values, formats, or column width | Paste Special (Method 4) |
| Copy to another sheet or workbook | Method 5 |
| Copy only visible cells from a filtered column | Go To Special or Alt + ; (Method 6) |
Method 1: Copy and Paste a Column
- Click the column letter at the top (for example, B) to select the entire column.
- Press Ctrl + C, or right-click and choose Copy. A moving dashed border appears.
- Click the letter of the destination column.
- Press Ctrl + V, or right-click and choose Paste.
This replaces anything already in the destination column. If you only need part of a column, select those cells instead (click the first cell, then Ctrl + Shift + Down Arrow to extend to the last filled cell) and paste into the top cell of the destination.
Method 2: Insert a Copied Column Without Overwriting
- Select and copy the column as above.
- Right-click the column letter where you want the copy to go.
- Choose Insert Copied Cells.
Excel inserts the copied column there and shifts the existing columns to the right. Nothing is overwritten. The keyboard equivalent is Ctrl + Shift + = (Ctrl + plus) after selecting the destination column.
Method 3: Copy by Dragging
- Select the column.
- Hover over the edge of the selection until the pointer becomes a four-headed arrow.
- Hold Ctrl (Option on a Mac) and drag the column to its new location. A small plus sign means you are copying, not moving.
- Release the mouse, then the key.
To insert the copy between columns instead of overwriting, hold Ctrl + Shift while dragging and drop it on the border between two columns.
Method 4: Paste Special (Values, Formats, Width)
After copying, right-click the destination and choose Paste Special (or press Ctrl + Alt + V). Useful options include:
- Values: pastes the results only, without formulas. Great for freezing calculated numbers.
- Formats: copies colors, number formats, and borders without the data.
- Formulas: copies formulas without formatting.
- Column widths: matches the destination width to the source.
- Keep Source Column Widths: in the Paste Options menu, pastes data and width together.
- Transpose: turns a column into a row (select a range of cells rather than an entire column to use this).
The small Paste Options icon that appears after pasting also lets you switch to these options after the fact.
Method 5: Copy a Column to Another Sheet or Workbook
- Select and copy the column.
- Click the destination sheet tab at the bottom, or switch to the other workbook.
- Click the destination column letter and press Ctrl + V, or right-click and choose Insert Copied Cells.
Formulas that refer to other cells may now point to cells in the new sheet. Use Paste Special > Values if you only need the numbers.
Method 6: Copy Only Visible Cells
If the column is filtered or has hidden rows, a normal copy may include the hidden data in some situations. To copy only what you can see:
- Select the cells.
- Press Alt + ; (semicolon) on Windows, or go to Home > Find & Select > Go To Special > Visible cells only.
- Press Ctrl + C and paste as usual.
On a Mac, use Cmd + Shift + Z or Go To Special.
How Formulas Change When You Copy a Column
Excel adjusts relative references when you paste into a different column. For example, a formula =A2*2 copied from column B to column D becomes =C2*2. To keep a reference fixed, make it absolute with dollar signs, such as =$A2*2 or =$A$2*2. Press F4 while editing a reference to cycle through the options.
If you want the exact same formulas without adjustment, copy the formula text from the formula bar, or paste as values.
Copying a Column in Excel for the Web and Mac
- Excel for the web: the same steps work. Use Ctrl + C and Ctrl + V; some Paste Special options appear under Home > Paste. Your browser may ask for clipboard permission.
- Excel for Mac: use Cmd + C and Cmd + V, and Option + drag to copy with the mouse. Insert Copied Cells is in the right-click menu.
Troubleshooting
"The copy area and paste area are not the same size"
You selected a full column to copy but a single cell or different range to paste. Click the destination column letter or a single top cell, then paste.
Pasted numbers show as #####
The column is too narrow. Double-click the right border of the column letter to autofit, or use Keep Source Column Widths.
Formulas show #REF! after pasting
The references now point outside the sheet or to deleted cells. Paste as values, or adjust the references to absolute before copying.
Insert Copied Cells is missing
It only appears when something is on the clipboard with the dashed border active. Copy again and right-click the destination immediately.
Frequently Asked Questions
What is the shortcut to copy a column in Excel?
Select a cell in the column, press Ctrl + Space to select the entire column, then press Ctrl + C.
How do I copy a column and keep the formatting?
A regular paste keeps formatting. Use Keep Source Column Widths in Paste Options to keep the width too.
How do I duplicate a column right next to itself?
Copy the column, right-click the next column letter, and choose Insert Copied Cells.
Can I copy multiple columns at once?
Yes. Drag across adjacent column letters, or hold Ctrl to select non-adjacent columns. Non-adjacent selections can be copied only when they have the same number of rows.
Summary
- Click the column letter and press Ctrl + C.
- Paste with Ctrl + V, or right-click and choose Insert Copied Cells to avoid overwriting.
- Use Paste Special for values, formats, or column widths.
- Use absolute references if formulas should not change.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.