Steps to copy an Excel sheet into a new workbook

Copying or moving data in Excel workbooks is a common task for users. While copying data within the same workbook is straightforward, copying an entire worksheet from one workbook to another may seem more complicated. However, Excel provides a built-in feature that simplifies this process. In this tutorial, I will explain two methods: copying a worksheet within the same workbook and copying a worksheet to a different workbook using drag-and-drop.

Steps to copy an Excel sheet into a new workbook:

Before we proceed, it’s important to understand the specific terms used in Excel:

• Sheet: Refers to the entire sheet element, such as Sheet 1, Sheet 2, Sheet 3, and so on. It includes all types of sheets, including worksheets and chart sheets.
• Worksheet: Refers to a specific type of sheet where you enter and manipulate values. It is often used interchangeably with the term “sheet.”
• Spreadsheet: Refers to the values, expressions, formatting, and other elements within a worksheet.

Now, let’s look at the steps to copy a worksheet within the same workbook:

1. Right-click on the sheet tab of the worksheet you want to copy.
2. From the resulting submenu, select “Move or Copy” (Figure A). This action will open the “Move or Copy” dialog box, where you can specify the destination for the copied worksheet.

Steps to copy an Excel sheet into a new workbook

Figure A

1. In the “Move or Copy” dialog box, you will see a list of all the sheets in the workbook. Choose the location where you want to place the copied worksheet. You can select an existing workbook or create a new workbook by selecting the corresponding option from the “To book” dropdown menu.
2. Check the “Create a copy” checkbox at the bottom-left corner of the dialog box to indicate that you want to create a copy of the worksheet rather than moving it.
3. Click the “OK” button to finalize the copy operation. The selected worksheet will be copied to the specified location within the same workbook.

Copying a worksheet to a different workbook using drag-and-drop:

Alternatively, you can also copy a worksheet to a different workbook using a simple drag-and-drop method. Here are the steps:

1. Open both the source workbook (the one containing the worksheet you want to copy) and the target workbook (the one where you want to copy the worksheet).
2. Arrange the windows of the two workbooks side by side on your screen so that you can view both of them simultaneously.
3. In the source workbook, click and hold the sheet tab of the worksheet you want to copy.
4. Drag the sheet tab to the target workbook and release the mouse button. The worksheet will be copied to the target workbook.

By following these steps, you can easily copy a worksheet within the same workbook or to a different workbook using Excel‘s built-in features.

To access a list of commands, right-click on a sheet tab.

Steps to copy an Excel sheet into a new workbook

Figure B:

Make sure to select the “Create A Copy” option.

Steps to copy an Excel sheet into a new workbook

Figure C:

Click OK.

Figure C shows that you now have two identical sheets. At this point, you may want to change the default sheet name and start entering new data. If you want to move a sheet instead of creating a copy, you can use the same right-click method and deselect the “Create A Copy” option in step #3.

Now, let’s learn how to copy the same worksheet to a completely different Excel workbook.

Copying a worksheet to a new workbook:

Let’s assume you want to copy the same worksheet to a different Excel workbook file. There are two ways to do this: 1) Using the interface or 2) Dragging the worksheet from one file to another. Let’s go through an example using the interface first.

Copy an Excel worksheet to a new workbook using Excel’s user interface

To copy a worksheet from one Excel workbook to another using the user interface, follow these steps:

1. Open the source workbook (containing the worksheet you want to copy or move) and the target workbook (where you want to copy or move the worksheet). Both workbook files must be open. You can use the demonstration file as the source workbook. Open a second workbook to act as the target, even if it’s a new blank workbook.
2. In the source workbook, navigate to the sheet you want to copy or move.
3. Right-click the sheet tab in the source workbook and select “Move or Copy” from the resulting shortcut menu, as described in the previous section.
4. In the “Move or Copy” dialog, choose the target sheet from the “To Book” dropdown. The list will display only open workbooks in Excel.
5. If you want to create a copy of the sheet instead of moving it, check the “Create a Copy” option at the bottom of the dialog (Figure D).

Steps to copy an Excel sheet into a new workbook

Figure D:

Select the target workbook and check the “Create a Copy” option.

1. Click OK, and Excel will move or copy the sheet to the target workbook.

If you navigate to the target workbook file, which is still open, you will find a copy of the source worksheet.

Copy an Excel worksheet by dragging it from one file to a new workbook:

Now, let’s try a different technique to achieve the same result. I wouldn’t call it a shortcut, but having options is always great. With both the source and target workbooks open, follow these steps:

1. Click the View tab and select “View Side by Side” in the Window group. Excel will split the screen horizontally between the two workbooks.
2. Click the sheet tab in the source workbook and drag it to the target workbook while holding down the Ctrl key. As you drag, Excel will display the “can’t drop” cursor. Once you reach the sheet tab area of the target workbook, the cursor will return to the normal drag-and-drop cursor. At this point, you can release the sheet tab. If you don’t hold down the Ctrl key, Excel will move the worksheet to the target workbook instead of creating a copy.

As shown in Figure E, both workbooks will have a copy of the source worksheet.

Steps to copy an Excel sheet into a new workbook

Figure E:

Display the source and target workbook files side by side.

To remove formatting when copying an Excel worksheet, follow these steps:

1. Click on the sheet selector, which is the small gray square at the intersection of the row and column header cells. This will select the entire worksheet.
2. Go to the Home tab.
3. In the Editing group, click on the Clear dropdown option.
4. From the dropdown menu, select Clear Formats (Figure F).

Steps to copy an Excel sheet into a new workbook

Figure F:

A bonus tip for creating templates in Microsoft Excel:

If you find yourself frequently copying Excel sheets from one workbook to another, especially for repetitive business processes, there is a more efficient way to set up your new workbooks.

While Excel’s default settings may work for some users, if you constantly have to reset things every time you start a new workbook file, consider the following approach. Open a blank workbook, customize the settings according to your needs, and save the file as a template. Then, in the future, use this template as the basis for new workbook files instead of relying on Excel’s built-in templates. You can even consider modifying the default workbook itself to save time and ensure consistent settings.