How to Duplicate a Spreadsheet in Excel

When working in Excel, you’ll sometimes need to create one or more copies of your spreadsheet in or from the workbook. Fortunately, creating duplicate spreadsheets is not a difficult task, unless you need to duplicate specific tabs rather than the whole workbook.

To confirm the terminology in Excel, a file is a workbook, the sheets in the workbook are spreadsheets, and the tabs at the bottom represent each spreadsheet’s page.

In this article, you’ll learn how to duplicate an Excel spreadsheet/tab to the same workbook or another one in multiple ways and on different platforms. Let’s get started.

How to Duplicate an Excel Spreadsheet in the Same File using Windows

The quickest way to duplicate a sheet/tab in the same workbook is by dragging and dropping it. Here’s how it’s done:

1. Open the workbook with the spreadsheets/tabs you want to duplicate.
2. At the bottom of the screen, select the “sheet tab” you want to copy. This step is important, or it won’t duplicate the tab in the next step.
   
3. While holding the “Ctrl” key, drag and drop the chosen “sheet tab” to the desired tab location with your mouse.
   
4. Your duplicated tab appears with “(1)” added to the end of the label. If there is already a duplicate tab, you will see “(2)” and so on to represent the number of copies.

Note: The “Shift” key only moves the tab. Therefore, you must use the “Ctrl” key to duplicate the tab in the workbook.

How to Duplicate an Excel Sheet to Another Workbook in Windows

The quickest way to duplicate a sheet/tab into another workbook is by dragging and dropping it, but you must select the tab first, then hold down “Ctrl” and slide it to other workbook tabs where the duplicate will reside. The method works but can be glitchy at times. The second method involves using the right-click method, which delivers accuracy every time. Here’s how it’s done.

If you need to copy/duplicate an Excel Sheet/tab from one workbook to another, do the following:

1. Right-click on the “sheet tab” you want to copy.
   
2. Select “Move or Copy…” from the options.
   
3. Choose the workbook where you want to place your duplicate using the “To book:” dropdown.
   
4. Select the sheet/tab that you want your duplicate to appear before using the “Before sheet:” dropdown.
   
5. Check off the “Create a copy” box. This ensures you are copying rather than moving the tab/sheet.
   
6. Click “OK.”
   

How to Duplicate an Excel Sheet to Another Tab on Mac

For Mac users, the dragging to another tab technique is the same as Windows, except you use the “Option” button instead of “Ctrl.”

1. While holding the “Option” key, click on the sheet tab and drag it where you want.
   
2. Drop the sheet tab and release the “Option” key. The duplicated tab will get labeled with “(1)” at the end. If there is an existing duplicate already, the label will include “(2).”

How to Copy an Excel Sheet to Another Workbook on Mac

If you want to copy your sheet to another workbook, the method is slightly different. Sliding one tab to another opened sheet also applies as an option like Windows, but here is how to duplicate the tab to another Excel sheet using the options.

1. Open the “Excel file” where you want to place your duplicate.
2. In the workbook containing the original sheet, right-click on the “sheet’s tab” at the bottom.
   
3. Select “Move or Copy…”
   
4. Choose the “workbook” where you want to paste your copied sheet.
   
5. Make sure that you check off the “Create a copy” box. This ensures that you are copying rather than moving.
   
6. Click “OK.”
   

How to Duplicate Multiple Excel Sheet Tabs in Windows

You can use the drag-and-drop technique to quickly duplicate several tabs at once in Excel, but there can be no gaps between the tabs, or it won’t work. Furthermore, the option does not work for tabs with Excel Tables in them. If you have no Excel Tables in your tabs and the desired tabs are adjacent, you can follow the steps below.

Use Drag-and-Drop to Duplicate Spreadsheets to Another Workbook

Note: Be sure to follow the steps below exactly as described or you may not get the results you expect.

1. Open the workbook where you want the duplicates to appear, then open the one with the existing tabs you want to duplicate. Ensure both are visible.
2. Click anywhere on the original to make it the active workbook.
3. Hold down the “Shift” or “Ctrl” key and click on the “tabs” you want to duplicate, then release the key. All clicked tabs should appear as selected.
4. Hold down the “Ctrl” key and click on the “first selected tab,” then drag the selection to the other workbook’s tab section.
5. The duplicated tabs should now appear in the other workbook.

Use the Right-Click Method to Duplicate Spreadsheets to Another Workbook

Note: Be sure to follow the steps below exactly as described or you may not get the results you expect.

1. Open the workbook you want the duplicates to appear in along with the one you want to duplicate.
2. Click on the original workbook to make it the active one.
3. Hold down the “Shift” or “Ctrl” key and click on each tab you want to duplicate.
4. Right-click on the “first selected tab” in the original workbook and choose “Move or copy…”
5. Choose the “workbook” where you want to paste your copied sheet.
6. Ensure the “Create a copy” box is checked or it will move the tabs rather than duplicate them.
7. Click the “OK” button to duplicate the selected tabs/sheets to the other workbook.

How to Duplicate a Sheet in Excel With a Shortcut?

If you want to create a larger number of sheet copies with the click of a button, you can create a keyboard shortcut:

1. Go to the “View” tab in the Ribbon.
   
2. Click on “Macros.”
   
3. In the drop-down menu, click “Record Macro.”
   
4. Enter the shortcut key you want to use (e.g. “D”).
   
5. Click “OK.”
   
6. Right-click on the sheet tab you want to duplicate.
7. Click “Move or Copy.”
   
8. Select the workbook in which you want to paste your copy.
   
9. Choose the sheet before which you want your duplicate to appear.
   
10. Check “Create a copy.”
    
11. Click on “Macros” again.
    
12. Select “Stop recording.”
    

Now, click on the sheet tab and press Ctrl + D to instantly duplicate the sheet. Use this shortcut as many times as you need.

How to Duplicate a Sheet in Excel in VBA?

Microsoft Office programs have their own programming language – Visual Basics for Application (VBA). With this, you can program Excel to make a copy of a sheet for you.

First, you will need to open VBA:

1. If you don’t see the “Developers” tab on the Ribbon, go to “File.”
   
2. Select “Options.”
   
3. In the “Customize Ribbon” section, check “Developers.”
   
4. Go back to your worksheet and open the “Developers” tab in the Ribbon.
   
5. Click on “Visual Basic.”
   

Note:  You can use a shortcut (Alt + F11) to open VBA, but this may not work for all users.

Now that you have VBA opened, you can create a code that will automate the duplication process:

1. Click on “Visual Basic” to open VBA.
   
2. Click the “Insert” tab and then “Module.”
   
3. Copy and paste the following code:
   Sub Copier ()
Dim x As Integer
x = InputBox("How many copies do you want?")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub
   
4. Instead of Sheet 1, enter the name of the sheet you want to copy.
5. Return to your worksheet and click “View” on the Ribbon.
   
6. Click on the “Macros” tab and then “View macro.”
   
7. Select the “Copier” macro and click “Run.”
   
8. Enter the number of copies that you want to make (e.g. “20”).
   
9. Click “OK.”
   

How to Duplicate a Sheet in Excel Online

If you are using Excel online, there is also an easy way to duplicate a sheet.

1. Right-click on the sheet tab you want to duplicate.
2. Click “Duplicate.”
   

How to Duplicate a Workbook in Excel?

First, you have to go to the dialog box that lets you open an existing Excel document. Accessing this dialog box varies depending on your version of Excel:

1. Excel 2007 – Office>Open
   Excel 2010 – File>Open
   Excel 2013 – File>Computer>Browse
   Excel 2016 – File>Browse
   
2. Navigate to the Excel document that you want to copy and select it.
3. Click the small arrow on the “Open” button.
   
4. From the drop-down menu, select “Open as Copy.”
   

You now have two identical workbooks. Rename the copy of the new workbook if needed.

How to Move a Sheet in Excel?

There are two easy ways to move a sheet in Excel.

You can simply select the sheet tab you want to move and drag it to the desired location.

Or, if you have too many sheets, you can:

1. Right-click on the sheet tab you want to move.
2. Click “Move or Copy.”
   
3. Select the sheet tab before which you want your sheet to appear.
   
4. Click “OK.”
   

How to Move a Sheet in Excel With a Shortcut?

To create a shortcut for moving a sheet in Excel, you will need to create a Macro:

1. Go to the “View” tab on the Ribbon.
   
2. Click on the “Macros” tab.
   
3. Select “Record Macro.”
   
4. Insert the key you want to use as a shortcut (e.g. “M”).
   
5. Right-click on the sheet tab you want to move.
   
6. Select “Move or Copy.”
   
7. Choose where you want to move your sheet.
   
8. Click “OK.”
   
9. Go back to “Macros.”
   
10. Click “Stop recording.”
    

Each time you click Ctrl + M, Excel will move your sheet to the location you selected.

How to Copy Multiple Sheets Multiple Times in Excel?

The quickest way to copy multiple sheets is to:

1. Select the sheet tabs you want to copy while holding Ctrl.
   
2. Right-click on any of the selected sheet tabs.
3. Select “Move or Copy.”
   
4. Click on the sheet before which you want the copies to appear.
   
5. Check “Create a copy.”
   
6. Click “OK.”
   

Repeat this process until you have the desired number of copies.

Additional FAQs

How Do I Unhide Sheets in Excel?

There may be some hidden sheets in an Excel file that you didn’t create. You can check that easily and unhide any hidden sheets:

1. Right-click on any sheet tab.

2. Click “Unhide.”

3. Select the sheet that you want to unhide and click “OK.”

Unfortunately, you can’t unhide all sheets at once. You will have to repeat this process for each hidden sheet.

Also, if there are no hidden sheets in the Excel file, the “Unhide” button will not be clickable.

How Do I Copy Sheet and Rename Automatically in Excel?

You will first have to open VBA in Excel and create a new Module:

1. Go to the “Developers” tab on the Ribbon.

2. Click on “Visual Basic.”

3. Click “Insert” and then “Module.”

4. Copy and paste the following code:

Sub Create()

'Updateby Extendoffice

    Dim I As Long

    Dim xNumber As Integer

    Dim xName As String

    Dim xActiveSheet As Worksheet

    On Error Resume Next

    Application.ScreenUpdating = False

    Set xActiveSheet = ActiveSheet

    xNumber = InputBox("How many copies do you want?")

    For I = 1 To xNumber

        xName = ActiveSheet.Name

        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)

        ActiveSheet.Name = "NewName" & I

    Next

    xActiveSheet.Activate

    Application.ScreenUpdating = True

End Sub

5. Instead of “NewName”, enter the desired name of your copy. If you create multiple copies, Excel will assign suffixes (“-1”, “-2”, “-3” etc.) to each copy.

6. Select the sheet tab you want to make renamed copies of.

7. Go to the “View” tab on the Ribbon.

8. Click on “Macros” and then “View Macros.”

9. Select the “Create” macro and click “Run.”.

10. Enter the number of copies that you need (e.g. “5”).

11. Click “OK.”

Note: Pressing F5 on the keyboard can substitute steps 7. and 8, but this may not work for all users.

Now you have five renamed copies of your original sheet (i.e. “NewName-1”, “NewName-2” …)

Creating a Duplicate Sheet in Excel

Whether you own a Mac, a PC, or work in Excel Online, you now know that creating duplicate spreadsheets is a simple task. We have even equipped you with the codes you need to copy and paste into your MS Excel.

Have you ever had any problems duplicating a sheet in Excel? How did you resolve the issue? Let us know in the comments section below.