Show All

Hide All

This article describes how to move or copy worksheets (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) and worksheet data to other locations.

• Move or copy worksheets to another location in a workbook

• Move or copy worksheets to another workbook

• Move or copy data to another worksheet or workbook

• Drag data between open workbook windows in Excel

• Drag data to another workbook that is open in a separate instance of Excel

Move or copy worksheets to another location in a workbook

It's easy to move or copy a whole worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) (or sheet) to another location in a workbook. However, be aware that calculations or charts that are based on worksheet data might become inaccurate if you move the worksheet. Similarly, if a moved or copied worksheet is inserted between sheets that are referred to by a 3-D formula reference (3-D reference: A reference to a range that spans two or more worksheets in a workbook.), data on that worksheet might be unexpectedly included in the calculation.

1. Select the worksheets that you want to move or copy.

   How to select worksheets

   To select	Do this
   A single sheet	Click the sheet tab. If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab.
   Two or more adjacent sheets	Click the tab for the first sheet. Then hold down SHIFT while you click the tab for the last sheet that you want to select.
   Two or more nonadjacent sheets	Click the tab for the first sheet. Then hold down CTRL while you click the tabs of the other sheets that you want to select.
   All sheets in a workbook	Right-click a sheet tab, and then click Select All Sheets on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.).

   Tip  When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.

   Keyboard shortcut  To move to the next or previous sheet tab, you can also press CTRL+PAGE UP or CTRL+PAGE DOWN.

2. On the Home tab, in the Cells group, click Format, and then under Organize Sheets, click Move or Copy Sheet.

   

    Tip   You can also right-click a selected sheet tab, and then click Move or Copy.

3. In the Move or Copy dialog box, in the Before sheet list, do one of the following:
   • Click the sheet before which you want to insert the moved or copied sheets.
   • Click move to end to insert the moved or copied sheets after the last sheet in the workbook and before the Insert Worksheet tab.

     

4. To copy the sheets instead of moving them, in the Move or Copy dialog box, select the Create a copy check box.

    Note   When you create a copy of the worksheet, the worksheet is duplicated in the workbook, and the sheet name indicates that it is a copy — for example, the first copy that you make of Sheet1 is named Sheet1 (2).

Tips

• To move sheets in the current workbook, you can drag the selected sheets along the row of sheet tabs. To copy the sheets, hold down CTRL, and then drag the sheets; release the mouse button before you release the CTRL key.
• To rename the moved or copied worksheet, right-click its sheet tab, click Rename, and then type the new name in the sheet tab.
• To change the color of the sheet tab, right-click the sheet tab, click Tab Color, and then click the color that you want to use.

 Top of Page

Move or copy worksheets to another workbook

1. To move or copy worksheets to another workbook, make sure that the target workbook is open in the same instance of Microsoft Office Excel.

    Note   You cannot move or copy worksheets between workbooks that are open in separate instances of Excel. If a workbook is opened in a separate instance of Excel — for example, this can happen when you open that workbook from a Windows SharePoint Services site — make sure that you open that workbook in the same instance of Excel instead by browsing to it in the Open dialog box (Microsoft Office Button, Open).

2. In the workbook that contains the sheets that you want to move or copy, select the sheets.

   How to select worksheets

   To select	Do this
   A single sheet	Click the sheet tab. If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab.
   Two or more adjacent sheets	Click the tab for the first sheet. Then hold down SHIFT while you click the tab for the last sheet that you want to select.
   Two or more nonadjacent sheets	Click the tab for the first sheet. Then hold down CTRL while you click the tabs of the other sheets that you want to select.
   All sheets in a workbook	Right-click a sheet tab, and then click Select All Sheets on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.).

   Tip  When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.

   Keyboard shortcut  To move to the next or previous sheet tab, you can also press CTRL+PAGE UP or CTRL+PAGE DOWN.

3. On the Home tab, in the Cells group, click Format, and then under Organize Sheets, click Move or Copy Sheet.

   

    Tip   You can also right-click a selected sheet tab, and then click Move or Copy.

4. In the Move or Copy dialog box, in the To book list, do one of the following:
   • Click the workbook to which you want to move or copy the selected sheets.
   • Click new book to move or copy the selected sheets to a new workbook.
5. In the Before sheet list, do one of the following:
   • Click the sheet before which you want to insert the moved or copied sheets.
   • Click move to end to insert the moved or copied sheets after the last sheet in the workbook and before the Insert Worksheet tab.

     

6. To copy the sheets instead of moving them, in the Move or Copy dialog box, select the Create a copy check box.

    Note   When you create a copy of the worksheet, the worksheet is duplicated in the destination workbook. When you move a worksheet, the worksheet is removed from the original workbook and appears in the destination workbook only.

Tips

• To rename the moved or copied worksheet in the destination workbook, right-click its sheet tab, click Rename, and then type the new name in the sheet tab.
• Worksheets that you move or copy to another workbook will use the theme fonts, colors, and effects that are applied to the destination workbook.
• To change the color of the sheet tab, right-click the sheet tab, click Tab Color, and then click the color that you want to use.

 Top of Page

Move or copy data to another worksheet or workbook

Although moving or copying the actual worksheet is a fast and effective way to transfer data to another location, you can also move or copy all or part of the data in a worksheet to another worksheet. This method can be used to transfer data to a worksheet in a workbook that is open in a separate instance of Excel.

1. In a worksheet, select the data that you want to move or copy.

    Note   If the selection includes hidden rows or columns, Excel also copies the data in those hidden rows and columns. You may have to temporarily unhide rows or columns that you do not want to include, and then select each range of data that you do want to move or copy in separate operations. For information, see Hide or display rows and columns.

2. On the Home tab, in the Clipboard group, do one of the following:

   

   • To move the selected data, click Cut .

     Keyboard shortcut  You can also press CTRL+X.

   • To copy the selected data, click Copy .

     Keyboard shortcut  You can also press CTRL+C.

3. Do one of the following:
   • Click the worksheet where you want to paste the data.
   • Switch to a workbook that is opened in another instance of Excel, and then click the worksheet where you want to paste the data.
4. Select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.).

    Note   Data in the paste area will be overwritten. Also, if the paste area contains hidden rows or columns, you might have to unhide the paste area to see all the copied cells.

5. On the Home tab, in the Clipboard group, click Paste .

   Keyboard shortcut  You can also press CTRL+V.

    Tip   To keep the column width that was originally specified for the data, click the arrow below Paste , click Paste Special, and then under Paste, click Column widths.

  Notes  

• By default, Excel displays the Paste Options button on the worksheet to provide you with special options when you paste cells, such as Keep Source Formatting and Match Destination Formatting. If you do not want Excel to display this button every time that you paste cells, you can turn this option off.
  1. Click the Microsoft Office Button , and then click Excel Options.
  2. In the Advanced category, under Cut, Copy, and Paste, clear the Show Paste Options buttons check box.
• When you copy cells, cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) are automatically adjusted. However, when you move cells, cell references are not adjusted, and the contents of those cells and of any cells that point to them might be displayed as reference errors. In this case, you will have to adjust the references manually.

 Top of Page

Drag data between open workbook windows in Excel

If you open more than one workbook in the same instance of Excel, you can drag worksheet data between the workbook windows.

1. In Excel, open the workbooks between which you want to transfer worksheet data.
2. On the View tab, in the Window group, click Arrange All.

   

3. In the Arrange Windows dialog box, under Arrange, click the options that you want, and then click OK.
4. In one of the windows, select the data that you want to move or copy to another window.
5. Do one of the following:
   • To move the selected data, point to the border of the selection. When the pointer becomes a move pointer , drag the selected data to another window.
   • To copy the selected data, hold down CTRL while you point to the border of the selection. When the pointer becomes a copy pointer , drag the selected data to another window.

 Top of Page

Drag data to another workbook that is open in a separate instance of Excel

If a workbook is open in another instance of Excel, you can drag worksheet data to it if it is visible on the taskbar.

1. Start an instance of Excel, and then open the workbook into which you want to drag worksheet data, or create a new workbook.
2. In another instance of Excel, open the workbook that contains the worksheet data that you want to transfer by dragging.
3. In a worksheet, select the data that you want to drag to a workbook that is visible on the taskbar.
4. Do one of the following:
   • To move the selected data, point to the border of the selection. When the pointer becomes a move pointer , drag the selected data to the workbook on the taskbar.
   • To copy the selected data, hold down CTRL while you point to the border of the selection. When the pointer becomes a copy pointer , drag the selected data to the workbook on the taskbar.

 Top of Page

• Moving a sheet from one Excel file to another?
• How do I copy a worksheet for each day of the year?
• How do you copy and rename linked worksheets?
• Move a sheet out of my workbook and place in another folder?
• Copying to multiple pages and keeping columns sized correctly???
• How do I copy or move a worksheet to another workbook?