Copy Sheet in Excel: A Simple Trick that Not Everyone Knows

There may be multiple situations where you need to copy a Sheet in Excel, such as when you only need to make some changes to a Sheet with existing data, or when you need a copy of that data Sheet from the workbook (Excel files that contain one or more worksheets), or simply when you want to copy a Sheet in Excel to reuse that table template.

Regardless of the reason, copying a Sheet in Excel is very easy and quick and anyone can do it. However, if you are new to Excel and don’t know how to do it, follow the detailed instructions below to learn how to copy a Sheet in Excel.

7 ways to copy a Sheet in Excel that everyone should know

In an Excel workbook, Excel provides three options to copy a Sheet in Excel to create multiple different Sheets. Depending on your usage habits, you can use the function buttons on the ribbon toolbar to copy a Sheet in Excel, use the mouse or keyboard to copy a Sheet in Excel. Follow the details of each method below.

1. Copy a Sheet in Excel by dragging and dropping

Usually, you can use the drag-and-drop feature to move data from one place to another. But this method also works to copy tabs and, in fact, it is the quickest way to copy a Sheet in Excel when needed.

To copy a Sheet in Excel, simply click and hold the left mouse button on the name of the Sheet you want to copy, then press the Ctrl key on the keyboard and drag and drop that Sheet to the new position, release the left mouse button and then the Ctrl key.

2. Copy a Sheet in Excel using right-click

You can also use the mouse to copy a Sheet in Excel, but by using the right-click menu. Follow the simple steps below to easily copy a Sheet in Excel.

Step 1: Right-click on the name of the Sheet you want to copy in Excel, from the context menu that appears, select the option Move or Copy.

Step 2: In the Move or Copy dialog box displayed, under the Before sheet section, select the position where you want to place the copied Sheet.

Step 3: Then click the option Create a copy and click the OK button to complete the process of copying a Sheet in Excel using the right-click menu.

3. Copy a Sheet in Excel through the ribbon toolbar

The ribbon toolbar is where all the features available in Excel are located, including the feature to copy a Sheet in Excel. Instead of searching for the hidden feature to copy a Sheet in Excel in other menus, you can now find this feature directly on the familiar ribbon toolbar. To copy a Sheet in Excel, follow the steps below.

Step 1: Open the data sheet that needs to be copied in Excel.

Step 2: Access the Home tab, then click the Format button in the Cell group feature.

Step 3: From the dropdown menu, select the option Move or Copy Sheet…

Step 4: The Move or Copy dialog box appears and you follow the same steps as copying a Sheet in Excel using the right-click menu above.

4. Copy a Sheet in Excel to another workbook

If the above solutions to copy a Sheet in Excel can only be performed within the same Excel file with multiple different Sheets, then here we will learn the solution to copy a Sheet in Excel between different workbooks (Excel files with one or more Sheets) separate. The popular way to copy a Sheet in Excel to another workbook (different Excel file) is as follows:

Step 1: Right-click on the name of the Sheet you want to copy, then select the option Move or Copy Sheet…

Step 2: In the Move or Copy dialog box displayed, do as follows:

• Click on the dropdown menu of the To book item and find another Excel workbook (different Excel file) that you want to copy this Sheet to. In case you want to create a copy for a new workbook, select the option (new book).
• Under the Before sheet item, select the position to place the copy.

Step 3: Click the option Create a copy at the bottom of the window. Then click the OK button to complete copying a Sheet in Excel through the right-click menu.

5. Copy a Sheet in Excel to another workbook by drag and drop

If Excel allows you to copy a Sheet within the same workbook by dragging and dropping, then why not try using this method to copy a Sheet from this workbook to another workbook? To perform copying a Sheet in Excel in this way, you need to prepare both files at the same time. Perform the following:

Step 1: Open both workbooks (Excel files), a source workbook and a destination workbook.

Step 2: Access the View tab on the ribbon toolbar, in the Windows group, click the View Side by Side button option. This command will arrange two Excel worksheets side by side.

Step 3: In the source workbook (source Excel file), click and hold the left mouse button on the Sheet you want to copy and simultaneously press the Ctrl key and drag this Sheet from the source workbook to any position below the Sheets of the destination workbook.

Great, isn’t it? Next time when you need to copy information from this Excel file to another Excel file, forget about copying/pasting and dragging the worksheet.

6. Copy multiple Sheets in Excel

In fact, all of the above solutions to copy a Sheet in Excel can be used to copy multiple Sheets in Excel. The important thing is to select multiple Sheets to copy at the same time. Here’s how you can copy multiple Sheets in Excel at once:

Step 1: To select adjacent Sheets, hold the left mouse button while pressing the Shift key and click the left mouse button to select the last Sheet. To select non-adjacent Sheets, hold the left mouse button while pressing the Ctrl key and click the left mouse button to select the Sheets you want to copy in Excel.

Step 2: After selecting multiple Sheets in Excel, perform one of the following operations to copy them:

• Click any of the selected Sheets, hold down Ctrl and drag these Sheets to the new position.

• Right-click any of the selected Sheet, from the displayed menu select the option Move or Copy Sheet… Then do the same as copying a Sheet in Excel using the right-click menu above.
• Or you can also access the Home tab, then click the Format > Move or Copy Sheet button and do the same as copying a Sheet in Excel to another workbook above.

7. Copy a Sheet in Excel with formulas

Generally, when you copy a Sheet in Excel that contains formulas, it’s just like any other Sheet. The formulas will automatically adjust the references in a way that works well in most cases.

If a Sheet has formulas copied in the same workbook (the same Excel file), the formulas will refer to the copied Sheet unless you are using external cell references pointing to a different Sheet or workbook (different Excel file).

For example: If you copy a Sheet in Excel that contains formulas to another workbook (different Excel file), the formula references will work as follows:

• References to the same Sheet, relative or absolute, will refer to the copied Sheet in the destination workbook.
• References to other Sheets in the source workbook will still refer to the Sheets in the source workbook. As in the image below, the name of the source Excel file [Book1] will appear before the name of the third formula in the copied Sheet:

To get the copied formulas to refer to a Sheet with the same name in the destination workbook, you just need to remove the source workbook name by using Excel’s Replace All feature. Here’s what you need to do:

Step 1: In the copied sheet, select all the formulas you want to change.

Step 2: Press the combination of keys Ctrl + H to instantly select the Replace tab of the Find and Replace dialog box displayed.

Step 3: In the empty box Find what, enter the name of the source workbook (exact name as appears in your formulas). In this case, we will enter [Book1].

Step 4: Leave the Replace box blank. Then click the Replace All. button.

Step 5: The result of the copied formulas on the copied sheet will change from:

If the formulas don’t work on the new worksheet, you can try the following approach: copy the worksheet with all the formulas you have created to a different workbook, select all the cells of the worksheet (Ctrl + A), right-click and select Copy or CTRL + C ) then paste the information into a separate worksheet. Then, the formulas will work normally on the newly copied worksheet.

Summary

Above, you just learned many very simple solutions to copy a Sheet in Excel with FPT Shop. Good luck!

• How to print Excel on an A4 paper, simple tricks but not everyone knows
• Summary of 7 quick ways to delete blank rows in Excel that not everyone knows