How to Copy a Sheet in Excel (Same or Another Workbook)

To copy a sheet in Excel, hold Ctrl (Option on a Mac) and drag the sheet tab sideways, or right-click the tab, choose Move or Copy and tick Create a copy. Both make an exact duplicate, with data, formulas, formatting and column widths. This guide covers both methods, copying to another workbook, copying several sheets at once, and the formula links that can follow a copied sheet around.

The fastest way: drag the tab with Ctrl#

Dragging is the quickest way to duplicate a sheet in the same workbook:

1. Click the sheet tab you want to copy, for example Jan.
2. Hold Ctrl (Mac: Option).
3. Drag the tab left or right. A small page icon with a plus sign shows that you're copying, and a black arrow shows where the copy will go.
4. Release the mouse button first, then the key.

The copy appears with the name Jan (2). Double-click the tab and rename it, for example to Feb. The guide on how to rename a sheet in Excel covers the naming rules, such as the 31-character limit.

If you drag without holding Ctrl, you move the sheet instead of copying it.

How to copy a sheet with Move or Copy#

The Move or Copy dialog lets you choose exactly where the copy goes, including another workbook:

1. Right-click the sheet tab and choose Move or Copy. You can also use Home > Format > Move or Copy Sheet.
2. In To book, keep the current workbook.
3. In Before sheet, pick where the copy should go, or choose (move to end).
4. Tick Create a copy. Without it, Excel moves the sheet.
5. Click OK.

On Windows you can open the dialog from the keyboard by pressing Alt, H, O, M one after another. Once the copy exists, you can jump between tabs with Ctrl+Page Down and Ctrl+Page Up; the article on switching sheets with the keyboard lists the Mac versions too.

In Excel for the web, right-click the tab and choose Duplicate.

How to copy a sheet to another workbook#

To copy a sheet into a different file, both workbooks must be open in the same Excel window:

1. Open the source workbook and the destination workbook.
2. In the source, right-click the sheet tab and choose Move or Copy.
3. In To book, pick the destination workbook. Choose (new book) to create a new workbook that contains only the copy.
4. Pick a position in Before sheet.
5. Tick Create a copy and click OK.

If the destination doesn't appear in the list, it isn't open, or it's open in a separate Excel instance. Close it and open it again from File > Open in the same window.

You can also drag a tab from one workbook window to another while holding Ctrl (Mac: Option). Arrange the windows side by side with View > Arrange All first.

Not sure which is the workbook and which is the sheet? The guide to workbook vs worksheet explains the difference.

How to copy several sheets at once#

Select the sheets first, then copy them in one go:

• Sheets next to each other: click the first tab, then Shift+click the last.
• Sheets anywhere: click the first tab, then Ctrl+click (Mac: ⌘+click) each of the others.

Now Ctrl+drag one of the selected tabs, or right-click it and use Move or Copy with Create a copy ticked. Every selected sheet is copied, in the same order.

The selected sheets stay grouped, and anything you type then goes into all of them. Right-click any tab and choose Ungroup Sheets as soon as you're done. If you're copying sheets to stack their data into one list, the guide on combining sheets in Excel shows a better approach.

How to copy only the contents of a sheet#

Sometimes you want the data in an existing sheet, not a whole new tab. Copy the cells instead:

1. Click the triangle in the top-left corner of the grid, or press Ctrl+A (Mac: ⌘A), to select every cell.
2. Copy with Ctrl+C (Mac: ⌘C).
3. Go to the other sheet, click A1, and paste with Ctrl+V (Mac: ⌘V).

Selecting the whole sheet this way copies column widths and row heights too. If you selected only a range, the widths are lost; use Paste > Keep Source Column Widths to bring them across.

To copy the results but not the formulas, paste with Paste Special > Values (Windows: Ctrl+Alt+V, then V). The copy then shows fixed numbers that won't change.

What happens to formulas when you copy a sheet#

Formulas that point to cells on the same sheet now point to the copy. If Jan had =SUM(B2:B6) in B7, the copy Feb has =SUM(B2:B6) that adds Feb's own cells. That's usually exactly what you want for a monthly template.

Formulas that point to other sheets keep pointing at them. If Jan had =B2*Rates!B1, the copy also uses the Rates sheet. The guide on how to reference another sheet explains that format.

Watch for three things when the copy goes to another workbook:

• Links to the old file. =B2*Rates!B1 becomes something like =B2*[Budget.xlsx]Rates!B1, because Rates isn't in the new file. Copy the Rates sheet too, or use Data > Edit Links (called Workbook Links in newer versions) to change the source or break the link.
• Name conflicts. If both files use the same defined name, Excel asks which version to keep. Click Yes to use the destination's version, or No to rename the copy's.
• Tables are renamed. A table on the copied sheet gets a new name, because table names must be unique in a workbook. Update any formulas that refer to it by its old name.

Charts follow the same rules as formulas. A chart whose data is on the copied sheet now reads the copy's data, but a chart that reads another sheet still points there, or to the old file after a copy to another workbook. Check them before you send the file.

Practise it#

Copying a sheet is easy. Getting the formulas on the copy to point at the right place is the skill employers test. Practise worksheets and cross-sheet references in a live spreadsheet, then practise copy, cut and paste to see how references shift when you paste formulas. Each lesson checks your work instantly. Then try the exercise on this page and compare two months side by side.