To duplicate a column in Excel, right-click the column letter and choose Copy, then right-click the column letter where you want the copy and choose Insert Copied Cells. Excel inserts an exact copy, with its formatting, formulas and width, and shifts the other columns right so nothing is overwritten. Below are faster drag-and-drop options, a formula that keeps the copy in sync, and fixes for formulas that change when copied.
Method 1: Copy and Insert Copied Cells
- Click the letter at the top of the column, such as B, to select the whole column.
- Press Ctrl + C. A moving border appears around it.
- Right-click the letter of the column that should sit to the right of the copy.
- Choose Insert Copied Cells.
The copy appears in that spot and existing columns move over. To copy several side-by-side columns at once, drag across their letters in step 1. Columns that aren’t next to each other are easiest to copy one group at a time.
Paste into an empty column instead
If the destination column is empty, click its letter and press Ctrl + V. Plain paste overwrites whatever is in the destination, so only use it on an empty column. Press Ctrl + Z if you paste over data by mistake.
Method 2: Drag a copy with the mouse
- Select the column by clicking its letter.
- Point at the edge of the selection until the pointer becomes a four-headed arrow.
- Hold Ctrl + Shift and drag. A thin vertical bar shows where the copy will go between columns.
- Release the mouse button, then the keys.
Holding only Ctrl while dragging drops a copy that replaces the destination column instead of inserting. Dragging with no keys moves the column rather than copying it. On a Mac, hold Option to copy, and Option + Shift to insert the copy.
Method 3: Use a formula for a live copy
If you want the duplicate to update when the original changes, link to it instead of copying:
- In Excel for Microsoft 365, Excel 2021 and later, click the first cell of the new column and type
=B1:B500. The values spill down the column. - In older versions, type
=B1and double-click the fill handle to copy it down. See how to use the fill handle in Excel.
Linked cells show values but not formatting. Use Home > Format Painter to copy the formatting across. Blank cells in the source show as 0. Wrap the formula as =IF(B1="","",B1) to keep them blank.
Paste only values, formulas or formatting
After copying a column, right-click the destination and use the Paste Options icons, or choose Paste Special (Ctrl + Alt + V):
| Paste option | What it copies | Use it when |
|---|---|---|
| Values (V) | The results only | You want a fixed copy that won’t change |
| Formulas (F) | Formulas without formatting | The destination already has formatting |
| Formatting (R) | Colors, fonts, number formats | You want the look but not the data |
| Keep Source Column Widths (W) | Data plus the column width | The pasted column comes out too narrow |
More on paste options in how to copy and paste data into Excel.
Why copied formulas change
Formulas with relative references, like =C2*D2, shift when you copy them to a new column. If the duplicate should point to the same cells as the original, either paste values or make the references absolute with dollar signs, as in =$C2*$D2. Press F4 while editing a reference to add them. See how to keep one cell constant in Excel.
Duplicate a column in an Excel table
In a formatted table, copy the column’s cells, right-click a cell in the column to its right and choose Insert > Table Columns to the Left, then paste. Excel gives the new column a header like “Column1”. Rename it, because tables can’t have two columns with the same header.
Troubleshooting
Insert Copied Cells is missing from the menu
The copy was cancelled. The moving border disappears if you press Esc or type in a cell. Copy the column again and right-click straight away.
“This operation will cause some cells in a table to shift”
You’re inserting into a range that crosses a table or merged cells. Insert the copy outside the table, or unmerge the cells first.
Hidden rows didn’t copy the way I expected
A normal copy includes hidden rows. If you filtered the list and want only the visible rows, select them and press Alt + ; to select visible cells only before copying.
Frequently asked questions
How do I duplicate a column onto another sheet?
Copy it, go to the other sheet, right-click the destination column letter and choose Insert Copied Cells or Paste. To copy an entire sheet, see how to copy a sheet to another workbook.
Can I duplicate a row the same way?
Yes. Click the row number instead of the column letter. Insert Copied Cells and Ctrl + Shift + drag both work for rows.
Summary
- Select the column and press Ctrl + C.
- Right-click the destination column letter and choose Insert Copied Cells.
- Or hold Ctrl + Shift and drag the column edge.
- Use
=B1:B500for a copy that stays in sync.
Related: add a column in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.