sodcalculator

10 Tech How-To

How to Duplicate Data in Excel: 5 Easy Ways

Guide 10-45920 · Updated · Step by step

Duplicating data in Excel is a core skill that saves time and reduces errors, whether you’re copying a formula down a column, duplicating a sheet, or repeating a value across a range. This guide covers five reliable methods—from simple copy-paste to using the Fill Handle and Paste Special—so you can choose the right one for your task. No special setup is needed beyond having your workbook open and the data you want to duplicate visible.

What You’ll Need

  • Microsoft Excel (2016, 2019, 2021, or Microsoft 365) on Windows or Mac.
  • A workbook with the data you want to duplicate (values, formulas, or formatting).
  • Basic familiarity with selecting cells and using the ribbon or right-click menu.

Step-by-Step

  1. Select the data you want to duplicate. Click and drag over a range of cells, or click a single cell. For an entire column or row, click the letter or number header. To select non-adjacent areas, hold Ctrl (Windows) or Cmd (Mac) while clicking.
  2. Copy the selection. Press Ctrl+C (Windows) or Cmd+C (Mac). You’ll see a moving dashed border (the “marching ants”) around the selection. Alternatively, right-click and choose Copy.
  3. Choose the destination. Click the cell where you want the top-left corner of the duplicated data to appear. If you’re copying a single cell, click the target cell. If you’re copying a range, click the top-left cell of the destination area—make sure there’s enough space to avoid overwriting existing data.
  4. Paste the data. Press Ctrl+V (Windows) or Cmd+V (Mac). The data appears, including formulas, formatting, and data validation. If you want to paste only specific elements (like values only), use Paste Special (right-click → Paste Special) and choose from options like Values, Formats, or Formulas.
  5. Use the Fill Handle for quick repetition. If you need to duplicate a value or formula down a column or across a row, select the original cell(s), then hover over the small square in the bottom-right corner of the selection until the cursor changes to a thin crosshair. Click and drag in the direction you want to fill (down, up, left, or right). Release the mouse button to duplicate the data.
  6. Duplicate an entire worksheet. Right-click on the sheet tab at the bottom of the window (e.g., “Sheet1”) and choose Move or Copy. In the dialog, select the position for the new sheet (e.g., “move to end”), check the box Create a copy, and click OK. This duplicates all data, formatting, and formulas on that sheet.
  7. Use the Fill command for larger ranges. Select the range you want to fill (including the original cell as the first cell), then go to the Home tab → Editing group → Fill → choose Down, Right, Up, or Left. This works well when you know the exact range size.

Common Mistakes

  • Overwriting existing data: Pasting over cells that contain data will replace it without warning. Always double-check the destination area before pasting.
  • Using the Fill Handle on a filtered list: If data is filtered, the Fill Handle may fill only visible cells, causing gaps. Unfilter the data or use Copy/Paste instead.
  • Copying formulas with relative references: When you copy a formula like =A1*2 down, Excel changes the reference to =A2*2, =A3*2, etc. If you need the same reference, use absolute references (e.g., =$A$1*2) or use Paste Special → Formulas.
  • Forgetting to check merged cells: Copying a range that includes merged cells can cause errors or unexpected results. Unmerge before copying, or be prepared to re-merge at the destination.

Tips

  • Use Paste Special for values only: When you copy a formula and want to paste only the result (not the formula), use Ctrl+Alt+V (Windows) or Cmd+Ctrl+V (Mac) and select Values.
  • Double-click the Fill Handle to auto-fill down: If you have data in adjacent columns and want to fill down to the same row count, double-click the Fill Handle instead of dragging—Excel stops at the last row of adjacent data.
  • Use Ctrl+D and Ctrl+R: Select a range and press Ctrl+D to fill down from the top row, or Ctrl+R to fill right from the leftmost column.
  • Copy formatting only: Use Paste Special → Formats to duplicate just the cell formatting (colors, borders, number formats) without changing the values.

Troubleshooting

  • Paste doesn’t work or shows an error: Make sure the destination cells are not protected. Go to Review tab → Unprotect Sheet if needed. Also check that the workbook is not in edit mode (e.g., a cell is being edited). Press Esc to exit edit mode.
  • Fill Handle doesn’t appear: Go to File → Options → Advanced and under Editing options, ensure Enable fill handle and cell drag-and-drop is checked.
  • Copying a sheet gives a “Cannot shift objects” error: This often happens when there are images or charts on the sheet. Try deleting or moving the objects, or copy the data manually instead.
  • Formulas show #REF! after pasting: This occurs when the formula references cells that don’t exist in the new location. Check the formula references and adjust them, or use Paste Special → Values to avoid the issue.

Frequently Asked Questions

  1. How do I duplicate a row in Excel?
    Select the row by clicking its number, press Ctrl+C, then right-click the row number where you want the duplicate and choose Insert Copied Cells. This inserts a new row with the duplicated data.
  2. How do I copy a formula down an entire column without dragging?
    Select the cell with the formula, then double-click the Fill Handle (the small square at the bottom-right of the cell). Excel fills down to the last row of data in the adjacent column. Alternatively, use Ctrl+Shift+Down to select the range, then press Ctrl+D.
  3. Why does my Fill Handle copy formatting but not values?
    This can happen if the source cells have a specific number format or if the Fill Handle is set to “Fill Formatting Only”. After dragging, click the Auto Fill Options icon and choose Fill Without Formatting or Copy Cells.
  4. Can I duplicate a sheet to another workbook?
    Yes. Right-click the sheet tab, choose Move or Copy, in the “To book” dropdown select the other workbook, and check Create a copy. Both workbooks must be open.
  5. How do I duplicate data without changing formulas?
    Use Paste Special → Formulas to paste only the formula text, or use Values to paste the calculated results. If you need the same cell references, use absolute references (e.g., $A$1) in the original formula.
  6. What is the fastest way to duplicate data in Excel?
    For most tasks, selecting the data, pressing Ctrl+C, then Ctrl+V is fastest. For filling down a column, the Fill Handle or Ctrl+D is quicker.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →