How to Make a Copy of an Excel Sheet: 4 Best Methods

Do you need to create a few copies of an Excel worksheet within the same workbook or across workbooks and wondering how? Look no further! Here’s how to make a copy of an Excel sheet in five easy ways. Suit yourself!

Microsoft Excel makes it easy to manage thousands of numerical data, tables, graphs, etc., on its worksheets. It also offers intuitive ways to manage these worksheets in several ways like copying, moving, etc.

Read on to find out how to make an exact copy of an Excel sheet in several ways below.

Usually, you use Excel for data entry, financial analysis, accounting, data management, project management, task management, budget tracking, expense tracking, and so on.

It means the Excel worksheets in a workbook (actual Excel file) contain important records, formulas, data visualizations, and tables that you’ve been working on for days.

Suppose, you must share a copy online with team members where there’s a high chance that changes will be made to the file. Hence, you’d want to create copies of the Excel worksheet instead of making the original file online.

In another scenario, you’ve created a data entry or accounting format in one Excel sheet and you want multiple copies in the same workbook and on other workbooks too. Again, you must make copies of the worksheet to make entries organized and consistent. Hence, you must learn how to make a copy of an Excel sheet.

There could be many more reasons apart from these two. No matter the reasons, the following methods to make a copy of an Excel sheet are universal. Let’s dig in!

It’s the most primitive way to make duplicate copies of Excel worksheets. Here’s how:

• Select all the data in an Excel worksheet by pressing Ctrl + A.
• Now, copy the selection by pressing Ctrl + C.
• Then, create a new worksheet on the same workbook by clicking the plus (+) sign or the New sheet button on the worksheet tab located at the bottom of the workbook.

• Select cell A1 and press Ctrl + V to paste the data from the original worksheet.
• You got your copy of the original worksheet.

This method is also good for creating copies of Excel worksheets from one workbook to another. Simply open the Excel app and create a new Blank workbook. Then follow the copy-paste part of the above steps to create a duplicate worksheet on another workbook.

Additionally, this method lets you utilize several Paste options of the Excel app. For example, when you manually copy data from one worksheet and paste it to another worksheet or workbook, you get the Paste option drop-down list.

Click the arrow beside this drop-down list to find the following special copy-pasting options:

• Paste: Normal Paste, Paste Formulas, Paste Formulas & Numbers Formatting, Keep Source Formatting, etc.
• Paste Values: Values only, Values & Source Formatting, and Values & Number Formatting.

For instance, your original worksheet contains a lot of formulas for a performance database. But, you don’t want the recipient to know what formulas you’re using.

You simply need to share the values. Then, you can use the manual copy-paste method and select Values only pasting to remove formulas from the duplicate worksheet.

Worksheet dragging is another intuitive and effortless way to copy worksheets to the same workbook or to another workbook. First, try it within the same workbook by following these quick instructions:

• On the keyboard, press and hold the Ctrl button.
• Use the mouse cursor to select the worksheet you want to copy by clicking on it.
• Now, move the mouse cursor left or right and let go of the cursor click.
• Excel will create a duplicate copy of the worksheet within the same workbook.
• You can rename the duplicate by double-clicking the name of the worksheet.

This method is also good for multiple worksheet copying. Press the Shift key and then select the first worksheet using a left-click. Now, go to the last worksheet and click again.

You’ll see that Excel has selected all the worksheets in the workbook. Now, drag as explained earlier to copy multiple Excel worksheets.

You might also want to know how to make a copy of an Excel sheet using the dragging method from one workbook to another. The following steps are the ones you’re after:

• Open the target workbook.
• Now, go back to the source workbook and select View from the Excel ribbon menu.

• There, look for the View Side by Side command. Click on it.
• Now, follow the drag-and-drop method described above.
• But, this time, you need to drop the worksheet on the second workbook’s worksheet tab.
• This will make a copy of the original worksheet from the source to the target workbook.

To export multiple worksheets from one workbook to another without any changes made, choose the worksheets either by clicking Ctrl or Shift and perform the drag-and-drop action of worksheet copying.

The Excel user interface (UI) also has several visual commands or buttons that you can use to make a copy of an Excel sheet. For example, you can use the right-click action on the worksheet tabs by following these quick steps:

• Right-click the worksheet you want to duplicate to the same workbook.
• From the context menu that opens, select Move or Copy.
• The dialog box named Move or Copy will show up.
• Don’t make any changes to the To book option.
• Choose any option below the Before the sheet option. This is to position the worksheet. If you don’t choose any position for the duplicate worksheet, it’ll occupy the first position.

• Now, checkmark the checkbox beside Create a copy command and click OK.
• A duplicate copy of the source worksheet will show up.

Alternative to the above steps is getting to the Move or Copy dialog box from the Home tab on the Excel ribbon > Format in Cells command group > click the drop-down list > select Move or Copy Sheet.

You might be asking, why did I ask you not to make changes in the To book option when performing the above steps? That’s a good question!

The To book option lets you choose the workbook where you want Excel to create a duplicate of the source worksheet. Now, the challenge of this visual command is the target workbook must be open.

Follow these steps to make a copy of an Excel sheet from one workbook to another using the Format or right-click method:

• Ensure both the source and target workbooks are open.
• Select a worksheet to be copied on the source workbook.
• Right-click and choose Move or Copy.

• Alternatively, click the Format drop-down on the Home tab and then choose Move or Copy Sheet.
• Now, click the drop-down arrow on the To book option.
• You should see two existing workbooks and a new book option.
• If you select the new book option, Excel will create a new workbook and place the duplicate worksheet there.
• Alternatively, if you select the target workbook, you’ll get a replica of the source worksheet on the target workbook.

For instance, you want a copy of the data entered in Sheet 1 automatically in Sheet 2 of the same workbook, or another sheet in another workbook. For this, you must use a simple formula. Let me show you how below:

• Consider, on the source worksheet, there are data between cells A1 and E2 horizontally, and A1 to A10 and E1 to E10 vertically.
• Now, create Sheet 2 and go to that sheet.
• Enter the following formula after selecting cell A1:

• Now, on Sheet 2 cell A1, you should see the data from cell A1 of Sheet 1.
• Then, click the fill handle and drag it horizontally until cell E1.

• The fill handle is the tiny square shape that appears on the lower right corner of each cell.
• Again, perform the same task but this time from cell A1 to cell A10, meaning vertical filling.
• You should see that as you move the fill handle horizontally or vertically, Excel copies data as is from the target worksheet.
• Now, whenever you paste new data on the source worksheet, the target worksheet will update automatically.

You can use this method to export data from one worksheet to another worksheet of a different workbook. Just, ensure both the source and target workbooks are open and you’re using this formula in cell A1. You must adjust the cell references if data is not within the cell A1:E10 range.

In the above formula, Book1 is the source workbook and Sheet1 is the source worksheet. Feel free to modify the data according to your workbook name and sheet name.

Do you create duplicate worksheets a lot when working on Microsoft Excel? Do you want to bypass all these mouse and keyboard actions and just want worksheets copied? Then, learn how to apply Macro and copy worksheets instantly below:

• Go to the Developer tab on the Excel ribbon and click on Record Macro.

So, these are pretty much all the methods to make a copy of an Excel sheet within the parent workbook or in a different workbook. If you find any method missing, do let our readers know by commenting below.