How to copy and move sheets in Excel

The tutorial shares a few helpful secrets about copying and moving sheets in Excel.

There may be many situations when you need to create a new spreadsheet based on an existing one or move a tab from one Excel file to another. For example, you may want to back up an important worksheet or create several copies of the same sheet for testing purposes. Luckily, there exist a few easy and quick ways to duplicate sheets in Excel.

How to copy a sheet in Excel

Excel has three inbuilt routes to duplicate worksheets. Depending on your preferred working technique, you can use the ribbon, mouse or keyboard.

Method 1. Copy Excel sheet by dragging

Usually, you drag-and-drop to move something from one place to another. But this method also works for copying tabs and is, in truth, the fastest way to copy a sheet in Excel.

Simply, click on the sheet tab that you want to copy, hold the Ctrl key and drag the tab where you want it:
Copy Excel sheet by dragging

Method 2. Duplicate a sheet by right-clicking

Here's another way to duplicate a sheet in Excel that is just as easy:

  1. Right click on the tab and select Move or Copy from the context menu. This will open the Move or Copy dialog box.
  2. Under Before sheet, choose where you want to place the copy.
  3. Put a tick in the Create a copy box.
  4. Click OK.

For instance, that's how you can make a copy of Sheet1 and place it before Sheet3:
Duplicate a sheet by right-clicking it.

Method 3. Copy a tab in Excel using the ribbon

The ribbon contains all the features available in Excel, you just need to know where to look :)

To copy a sheet, go to the Home tab > Cells group, click Format, and then click Move or Copy Sheet:
Copy a tab in Excel using the ribbon.

The Move or Copy dialog box appears, and you follow the same steps as described above.

How to copy Excel sheet to another workbook

The common way to copy a sheet to another workbook is this:

  1. Right click on the tab that you want to copy, and then click Move or Copy…
  2. In the Move or Copy dialog box, do the following:
    • Under To book, choose the target file. To place a copy into a new workbook, select (new book).
    • Under Before sheet, specify where to put the copy.
    • Select the Create a copy box.
    • Click OK.

Copy Excel sheet to another workbook.

Note. Excel displays only the open workbooks in the To book drop-down list, so be sure to open the destination file before copying.

Apart from this traditional route, there is one more way to do the same thing. Guess what, simply by dragging a sheet from one Excel file to another!

How to copy a sheet to another workbook by dragging

If Excel allows duplicating a sheet within the same workbook by dragging it, why not try to use this method for copying a sheet to a different workbook? We just need to view both files at the same time. Here's how:

  1. Open the source and target workbooks.
  2. On the View tab, in the Windows group, click View Side by Side. This will arrange the two workbooks horizontally.
  3. In the source workbook, click the sheet tab you'd like to copy, hold down the Ctrl key and drag the sheet to the target workbook.

Copy a sheet to another workbook by dragging.

Awesome, isn't it? The next time you need to copy information from one Excel file to another, forget about copy/pasting and drag the sheet.

How to copy multiple sheets in Excel

All the techniques that work for duplicating a single sheet can be used to copy multiple sheets. The key thing is to have several worksheets selected. Here's how you can do this:

  • To select adjacent sheets, click on the first sheet tab, press Shift, and click on the last tab.
  • To select non-adjacent sheets, click on the first sheet tab, press Ctrl, and click the other tabs one by one.

With multiple worksheets selected, do one of the following to copy them:

  • Click on any of the selected tabs, press Ctrl and drag the tabs to the desired position.
  • Right-click any of the selected sheets and click Copy or Move. Then, carry out these steps.
  • On the Home tab, click Format > Move or Copy Sheet. More details can be found here.

To copy multiple sheets to another workbook, select them as described above, and then follow one of these ways.

How to copy Excel sheet with formulas

Generally, you copy a sheet containing formulas like any other sheet. The formula references adjust automatically in a way that works just fine in most cases.

If a sheet with formulas is copied within the same workbook, the formulas will refer to copied sheet unless you use external cell references that point to another sheet or workbook. For example:
Copying Excel sheet with formulas

If you copy a sheet with formulas to another workbook, the formula references behave in this way:

  • References within the same worksheet, either relative or absolute, will point to the copied sheet in the destination workbook.
  • References to other sheets in the original workbook will still point to the worksheets in the original workbook. In the screenshot below, please notice that the original file name [Book1] appears before the sheet name the third formula in the copied sheet:

Formulas copied to another Excel file

For the copied formulas to refer to the sheet with the same name in the destination workbook, you can simply remove the original workbook name by using the Replace All feature of Excel. Here's what you need to do:

  1. On the copied sheet, select all the formulas you want to change.
  2. Press Ctrl + H to open the Replace tab of the Find and Replace dialog.
  3. In the Find what box type the name of the original workbook exactly as it appears in your formulas. In our case, we'd type [Book1].
  4. Leave the Replace with box blank.
  5. Click Replace All.

Remove the original workbook name from the copied formulas.

As the result, the formula in the copied sheet will change from

=[Book1]Sheet2!B1*[Book1]Sheet2!C1

To

=Sheet2!B1*Sheet2!C1

Copy information from one Excel sheet to another using formula

In case you don't want to copy the entire sheet, but only some part of it, select the range of interest and press Ctrl + C to copy it. Then switch to another sheet, select the upper-left cell of the destination range and press Ctrl + V to paste the copied range.

For the copied data to update automatically as soon as the original data changes, you can copy information from one Excel sheet to another by using formulas.

For example, to copy data from cell A1 in Sheet1 to cell B1 in Sheet2, enter this formula in B1:

=Sheet1!A1

To copy information from another Excel file, include the workbook name in square brackets:

=[Book1]Sheet1!A1

If needed, drag the formula down or to the right to pull data from other cells.

Tip. To get a reference to another workbook right, type the equality sign (=), then switch to another workbook, click the desired cell, and press Enter. Excel will insert the reference for you automatically.

To copy data from several sheets into one, please see How to merge multiple sheets in Excel.

How to duplicate an Excel sheet using VBA

If you often need to copy information from one Excel sheet to another, macros can save you a lot of time. Here are just a few examples of tasks that can be performed automatically:

For more examples, please check out How to duplicate a sheet in Excel with VBA.

How to move sheets in Excel

Moving sheets in Excel is even easier than copying and can be done with the already familiar techniques.

Move a sheet by dragging it

To move a sheet(s) in Excel, you simply select one or more tabs and drag them to a new location.

For example, here's how you can move Sheet1 and Sheet3 to the end of the workbook:
Moving a sheet in Excel

To move a sheet to another workbook, place the workbooks side-by-side (View tab > View Side by Side) and then drag the sheet from one file to another.

Move a sheet via Move or Copy dialog

Open the Move or Copy dialog box either by right-clicking the sheet tab and selecting Move or Copy from the context menu or by clicking Home tab > Format > Move or Copy Sheet. Then, do one of the following:

  • To move a sheet within the same workbook, choose before which sheet to place it and click OK.
  • To move a sheet from one Excel file to another, select the workbook name from the To Book list, choose the "Before sheet" and click OK.

Move a sheet to another Excel file.

Note. If the destination file already contains a worksheet with the same name, Excel will add a number in brackets to the end of the moved sheet's name. For example, Sheet 1 will become Sheet 1 (2).

Workbook Manager - quick way to move and copy sheets in Excel

If you often copy or move sheets from one Excel file to another, our Workbook Manager can make your life a lot easier.

Run the tool by clicking the Workbook Manager button on the Ablebits Data tab in the Manage group:
Workbook Manager

Now, you have a tree-view of all open workbooks and can easily drag sheets between different files:

  • To move a sheet or several selected sheets, drag them in the Workbook Manager window.
  • To copy one or more sheets, drag them while holding the Ctrl key.

As you do this, the sheets in your workbooks are rearranged accordingly without you having to switch back and forth from one file to another.
Moving sheets between different Excel files

You are welcome to download a trial version of our Ultimate Suite to try this and 70+ more awesome tools for Excel.

Move or copy sheet in Excel not working

Usually Microsoft Excel copies and moves sheets without a hitch. If a certain worksheet refuses to be copied or moved, this may be because of the following reasons.

1. Excel table

You won't be able to move or copy a group of sheets if one of those sheets contains an Excel table (vs. a range). Each such worksheet should be dealt with individually.

2. Protected workbook

Moving and copying sheets are not allowed in protected workbooks. To check if the workbook is protected, go to the Review tab > Protect group and have a look at the Protect Workbook button. If the button is highlighted, it means the workbook is protected. Click that button to unlock the workbook, and then move sheets.

3. Excel names

When copying or moving a sheet from one Excel file to another, you can get an error message that a certain name already exists, meaning the source and target workbooks contain a table or range with the same name. If it is just a single error, click Yes to use the existing version of the name, or No to rename it. In case of multiple errors, it makes sense to review all the names before moving or copying sheets. For this, press Ctrl + F3 to open the list of names in the active workbook - you will be able to edit or delete the names from there. For more information, please see How to define and use names in Excel.

That's how you can move or copy a sheet in Excel. I thank you for reading and hope to see you on our blog next week.

Table of contents

28 comments

  1. When I copy and paste data in Excel from one workbook to another, then I close the current workbook; none of the drop-down lists work.

  2. how to move data in more excel sheets or workbook ???

  3. hello friends how do you do>?

  4. While inserting one sheet from one to another file, it displays that "the excel sheet could not be inserted to the destination file since the destination file contains fewer rows........" How to overcome this?

    1. Hello!
      I suspect that the destination workbook is in xls format Excel 2007 and earlier, while the source workbook is in xlsx or xlsm format. Files of these formats have a different number of rows: xls - 65 536, xlsx - 1 048 576.

  5. How do I fix the margins?

  6. Hi,

    Is there a way to take data from one spreadsheet and copy it to another, where lines have been removed. as a simple copy and paste of the columns/row mess up the order.

    As I want to complete in one.

    thanks

  7. Thank you for the Ctrl+H trick! 8532 changes made in an instant!

  8. To Copy an Excel sheet from one workbook to another (Windows 10 and MS Office 2013 and later and probably earlier):

    It's ridiculously easy.
    1) Open source and destination workbooks side by side on the screen.
    2) Drag and drop a worksheet fron the source to the destination file, positioning it anywhere it suits you.
    3) Make sure that you have no duplicate worksheet names in source and destination workbooks.

    Done!

  9. Hello - I am looking to move/copy a sheet from an Excel file to a Google Sheets workbook. Any idea on how to do this? I don't see any way to move/copy the entire sheet into the Google Sheets workbook, and if I just copy and paste my work into the Google Sheets page, it loses a bunch of formatting and formulas. Would love to know if anyone has an answer to this. Thanks!

  10. Hello. I've been using these methods for two decades. Recently, I was upgraded to Office 2019. Now, when I copy a worksheet containing a chart, the copy loses the chart. Please help. Thank you.

  11. I could do it. Many thanks, teams.

  12. Excellent article as I was trying to copy entire sheets to other workbooks and retain the column widths. I did not find a method to do this via the web client, but does work fine in the desktop client. If you do have a method that includes the web client, please add that. Thanks!

  13. I hope you help me to solve my problem

    If their possible ways to transfer datas (without using VBA) from one sheet(mother datas) to different sheet to seperate datas belong to sheet automatically that respond to code you made.

    Regards

  14. How to make copy of ecxel work sheet below same work sheet or multi copy of worksheet w

  15. i'm grateful to learn how to move a tab from one workbook to another one.

  16. Hi. I need to copy visible sheets from one workbook to a new workbook via vba. I want to keep the source formatting but do not want to copy the formula or links to the original workbook.

    Thank you

  17. I email a sheet from my workbook that contains a graph but not the data table (it's on a different sheet) to my supervision. The x-axis of the graph is formatted with the month and year. When my supervisor opens the copy of the graph the x-axis labels become random numbers instead of month/year date format. The formatting for the x-axis gets lost and becomes formatted as general data. This is embarrassing for me when I get a response asking what months are on the x-axis. Is there a way to lock the formatting so it doens't change when the email is sent. I check the copied sheet with the graph as the attachment to the email and the format is still correct. The formatting is lost when I send the email. Any suggestions?

  18. I have an excel file with different catagories. I which to copy individual blocks to another file. I can't seem to figure it out.

  19. I receive a report that's exported from SAP. The report has 5 tabs.
    I copy a tab from a previous report that has my VLOOKUP formulas. Everyway that I copy my tab all my formulas keep the original worksheet name, and I don't want that, can I turn it off?
    =VLOOKUP(A1530,'[Master Active Contracts Report Rev2.xlsm]Consolidated'!B:E,4,FALSE)

  20. Every year for years one of my office associates has used excel to calculate commission earnings. She copies the current worksheet each week to enter values and renames the sheet. For some reason, she cannot do this. She cannot drag the sheet, she cannot right click and select move or copy as they are "grayed out", she is literally unable to click on most of the menu that pops up when she right clicks on the tab. The same is true if she goes to 'format' on the home ribbon. She does protect the sheet and has tried it protected and unprotected. If she looks back through prior years' workbooks she has no problems with this. And for 2019 she has done this every week since January 1st. Why those options are no longer able to be selected when she right clicks the tab has us baffled. Thank you.

    Luke

    1. Got it. She had unprotected the sheet not the workbook.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)