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

Duplicating a worksheet in Excel sounds simple — and it usually is. But depending on how you copy the sheet, where you want it to go, and what's on it, the results can vary in ways that catch people off guard. Here's a clear breakdown of every method available and what actually happens under the hood.

Why You'd Want to Copy a Sheet in Excel

Before jumping into the steps, it's worth knowing why this matters. Copying a sheet lets you:

• Preserve a template while working on a new version
• Duplicate a formatted layout without rebuilding it manually
• Move data between workbooks while keeping formulas intact
• Create backups of sheets before making major edits

Each of these use cases is straightforward — but they can call for slightly different approaches.

Method 1: Drag and Drop (Fastest for Same Workbook)

This is the quickest way to copy a sheet within the same workbook.

1. Right-click the sheet tab at the bottom of the screen
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 number in parentheses (e.g., Sheet1 (2))

What to know: If you forget to hold Ctrl/Option, you'll move the sheet rather than copy it. Nothing is lost — just undo with Ctrl+Z and try again.

Method 2: Right-Click Menu (Most Reliable)

This method gives you full control over where the copy goes.

1. Right-click the sheet tab you want to copy
2. Select "Move or Copy…"
3. In the dialog box, choose where to place the sheet (before which existing sheet)
4. Check the "Create a copy" box — this is the step people most often skip
5. Click OK

This method also lets you copy the sheet into a different open workbook by choosing it from the "To book" dropdown. If you want to copy it into a brand-new workbook, select "(new book)" from that same list.

Method 3: Using the Ribbon

If you prefer navigating through menus:

1. Click the sheet tab you want to copy
2. Go to the Home tab in the ribbon
3. Click Format (in the Cells group)
4. Select "Move or Copy Sheet…"
5. Follow the same dialog steps as Method 2

This is essentially the same operation as right-clicking — just accessed differently. Useful if you're more comfortable working through the ribbon than context menus.

Method 4: Keyboard Shortcut Path

Excel doesn't have a single one-keystroke shortcut for copying a sheet, but you can navigate to the Move or Copy dialog entirely from the keyboard:

• Press Alt → H → O → M (Windows) in sequence to open the Move or Copy dialog

This is a ribbon keyboard shortcut sequence, not a simultaneous combination. Each key is pressed one at a time.

Copying a Sheet to Another Workbook 📋

This is where things get a bit more nuanced.

When you copy a sheet to a different workbook, Excel also copies:

• Cell values and formatting
• Most formulas — but formulas that reference other sheets in the original workbook will still point back to that workbook (shown as external references)
• Named ranges defined at sheet level
• Conditional formatting and data validation

What doesn't transfer cleanly:

• Named ranges defined at the workbook level may not behave as expected
• Formulas referencing other sheets in the original workbook become external links, which can break if the source file is moved or renamed
• Macros assigned to sheet-level buttons won't copy with their associated VBA code unless you also copy the module

If you're moving a sheet to a new workbook and want it to be fully self-contained, review the formulas for external references after copying.

What Happens to Formatting and Formulas?

Variables That Affect How Well This Works 🔍

Not every copy operation behaves identically. Several factors shape what you actually get:

Excel version — The ribbon layout and available options differ between Excel 2016, 2019, Microsoft 365, and Excel for Mac. The core feature works the same, but menu paths vary slightly.

File format — If you're working in .xlsx (standard) vs. .xlsm (macro-enabled) vs. older .xls formats, some features may behave differently during copying, particularly around macros and compatibility mode.

Sheet complexity — Sheets with heavy pivot tables, external data connections, or embedded objects (like images or charts linked to external data) require extra attention. The copy will include those elements, but their connections may need to be reestablished.

Shared workbooks or co-authoring — If the workbook is shared or open in collaborative mode (via OneDrive or SharePoint), some operations may be restricted depending on your permissions.

Protected sheets — You can copy a protected sheet, but the copy will retain the same protection settings. If you don't have the password, you won't be able to edit the copy either.

User Profiles Who See Different Results

Someone copying a clean, formula-free data table into a new workbook will have a near-seamless experience. A financial analyst duplicating a model where dozens of formulas reference other tabs — and whose new workbook needs to be fully standalone — will need a more deliberate approach to clean up external references after copying.

Power users working with VBA macros need to understand that sheet copying doesn't carry code modules along with it. The button might copy; the macro it calls won't be there unless transferred separately.

Whether the built-in copy methods are sufficient — or whether you'd be better served by a more manual approach like rebuilding references or using Paste Special — depends on what's on your sheet and where it's going.