How to Make a Copy of an Excel Sheet (Every Method Explained)

Copying an Excel worksheet sounds simple — and mostly it is — but there are at least five different ways to do it, and which one works best depends on what you're actually trying to accomplish. Are you duplicating a sheet within the same workbook? Moving it to a different file? Preserving formulas versus just the values? Each scenario calls for a slightly different approach.

Why Copying a Sheet Isn't Always the Same as Copying Data

When you copy an Excel sheet, you're not just copying the visible content in the cells. You're also copying:

• Cell formatting (fonts, colors, borders, number formats)
• Formulas — which may reference other sheets or named ranges
• Named ranges scoped to that worksheet
• Conditional formatting rules
• Data validation settings
• Charts and embedded objects tied to that sheet

This is important because a copy that looks identical on screen can behave very differently once you start editing it — especially if formulas reference other sheets in the original workbook that don't exist in the destination.

Method 1: Drag and Drop (Quickest for Same-Workbook Copies)

This is the fastest method when you want to duplicate a sheet within the same workbook.

1. Right-click the sheet tab at the bottom of your workbook
2. Hold Ctrl (Windows) or Option (Mac)
3. Click and drag the tab to a new position along the tab bar
4. Release — a copy appears with the same name plus a (2) suffix

No menus required. The copy contains everything the original sheet has, including formulas that reference other sheets in the same workbook.

Method 2: Move or Copy Dialog Box (Most Control)

This method gives you the most options and works for both same-workbook and cross-workbook copying. 🗂️

1. Right-click the sheet tab
2. Select Move or Copy…
3. In the dialog box:
   • Use the "To book:" dropdown to choose the destination workbook (or select "new book" to create a fresh one)
   • Choose where in the tab order the copy should appear
   • Check the "Create a copy" box — if you skip this, you'll move the sheet instead of copying it
4. Click OK

This is the method to use when copying a sheet into another open workbook, since the target file must already be open to appear in the "To book" dropdown.

What Happens to Formulas When You Copy Across Workbooks

This is where things get nuanced. When a sheet is copied to a different workbook, any formulas that reference other sheets in the original workbook will update to include external references — links pointing back to the source file. That can be useful or it can be a problem, depending on whether you want the copy to remain connected to the original data or stand on its own.

If you want a fully independent copy without external links, you'll need to break those references or paste values only (see Method 4).

Method 3: Keyboard Shortcut (Windows)

On Windows, you can open the Move or Copy dialog quickly:

• Select the sheet tab, then use Alt → H → O → M (pressing each key in sequence, not simultaneously)

This navigates through the ribbon: Home → Format → Move or Copy Sheet. Not the most intuitive shortcut, but useful if you work primarily from the keyboard.

Method 4: Copy Sheet Contents as Values Only

Sometimes you don't want a true sheet copy — you want the data without the formulas. This is common when sharing a snapshot of a report or when formula references would create errors in a new workbook.

1. Select all cells on the sheet with Ctrl+A (or click the corner cell selector above row numbers)
2. Copy with Ctrl+C
3. Open or create the destination sheet
4. Use Paste Special → Values (Ctrl+Shift+V or right-click → Paste Special → Values)

This strips out formulas and pastes only the calculated results. Formatting won't transfer automatically with a values-only paste, so you may need a second Paste Special pass for formats, or use "Values and Source Formatting" as a combined option.

Method 5: Copying Multiple Sheets at Once

You can copy several sheets in one move:

• Hold Ctrl and click each sheet tab you want to select
• Right-click any selected tab → Move or Copy…
• Follow the same dialog steps as Method 2

All selected sheets will be copied together, preserving their relative order. This is especially useful when your workbook uses a multi-sheet setup where data flows between tabs.

Key Variables That Affect Your Outcome

📋 When to Use Each Method

• Same workbook, quick duplicate → Ctrl/Option drag
• Cross-workbook copy with full content → Move or Copy dialog
• Independent data snapshot without formulas → Copy + Paste Special (Values)
• Multiple sheets at once → Multi-select tabs + Move or Copy dialog
• Preserving a template for reuse → Copy to a new book and save as a separate file

The method that works cleanly in one scenario can cause broken links or unexpected behavior in another. Whether a straightforward drag-copy is enough — or whether you need to think carefully about formula dependencies, named ranges, and destination file formats — comes down to how your specific workbook is structured and what you intend to do with the copy once it exists.