Copy Worksheet in Excel

MS Excel, short for Microsoft Excel, is powerful spreadsheet software using which we get access to sheets/worksheets and record vast amounts of data within several cells. When working on Excel regularly, we often face situations when we need to work on the same data for different purposes.

For example, we may need to back up our essential data from the worksheet or create multiple copies of the same worksheet for testing purposes or other reasons. Instead of re-entering the entire data in a new worksheet, we can copy the data or worksheet to another.

This article discusses several ways to understand how to copy worksheets in Excel. The article also discusses specific cases where users may need to copy worksheets frequently. The methods discussed here are applicable for almost all Excel versions, such as Excel 2010, 2013, 2016, etc.

How to copy a worksheet in Excel?

Microsoft Excel provides various options to perform most built-in tasks or execute existing commands or tools. Likewise, there are several ways to copy a worksheet or duplicate worksheets in Excel.

The following are the most common and effective ways/ methods used to copy one worksheet to another in Excel:

• Copying a worksheet using the dragging
• Copying a worksheet using the right-click
• Copying a worksheet using the Ribbon
• Copying a worksheet using the Keyboard Shortcuts

Let us discuss each method in detail:

Copying a worksheet using the dragging

The drag-drop feature typically works for copying or moving something from one location to another. The same method can also be used to copy a worksheet. Drag-drop is one of the fastest methods to copy a worksheet in the same Excel workbook. We must perform the following steps to copy a worksheet by using a dragging method:

• First, we need to select the sheet from which we must copy the content into another sheet. To select the desired worksheet in an Excel workbook, we need to click the specific worksheet from the Sheet tab. For example, we select the Sheet1 to copy, as displayed below:
  
• Next, we must hold the Ctrl key on the keyboard and drag the selected sheet (Sheet1) across the sheet tab. We can drop the sheet between two consecutive sheets to place a copy of the source sheet.
  
• After we drag the selected sheet, Excel creates a copy of the selected sheet (Sheet1) in the corresponding place with the name of Source Sheet (2), i.e., Sheet1 (2). In our example, it looks like this:
  

Copying a worksheet using the right-click

Using the right-click menu (also called contextual menu) is another easy and quick method to copy a worksheet or make duplicate worksheets. This method also uses the mouse. We must perform the following steps to copy a worksheet using the right-click method:

• First, we need to select a worksheet to copy. After that, we must press a right-click button using the mouse on the specific worksheet from the sheet tab. Excel will display many options. Here, we need to select the ‘Move or Copy’ option from the list, as shown below:
  
• In the next (Move or Copy) window, we must choose the sheet name from the list under the ‘Before sheet’ section. The source sheet will be copied before the selected sheet in this list. However, we can also choose the ‘Move to End’ option to copy a sheet at the end of the sheet tab.
• After selecting the desired option from the list, we must ensure that the box before the text ‘Create a copy’ is ticked or marked. If we don’t tick the box ‘Create a copy’, the source sheet will be moved but not copied. Lastly, we must click the OK button.
  
  This will create a copy of the selected sheet or source sheet at the selected location within the workbook. In our example, we copied the sheet at the end, as shown below:
  

Copying a worksheet using the Ribbon

Excel’s Ribbon is the main area where users can access all the built-in features, tools, or commands. To copy a worksheet in Excel using the Ribbon, we must follow the below steps:

• First, we must select a sheet to copy using the sheet tab.
• Next, we must navigate to the Home tab on the Ribbon and choose the Format button from the section named Cells, as shown below:
  
• After selecting the Format button, Excel displays a drop-down list containing various options. Here, we must click the ‘Move or Copy Sheet’ option.
  
• After completing the previous step, we will see a Move or Copy dialogue box similar to the previous method.
  
  Like the previous method, we must choose the sheet before which we want to copy a selected sheet, tick the ‘Create a copy’ box and click the OK button.

Copying a worksheet using the Keyboard Shortcuts

The keyboard shortcuts are the quickest and simplest way to perform most Excel tasks. Excel also offers various shortcuts that we can use in a sequence to copy the worksheet easily to another location. To copy a worksheet using the keyboard shortcuts, we can follow the below steps:

• First, we need to open or select the worksheet to copy.
• Next, we must use the shortcut ‘Ctrl + A’ to select the entire sheet or the sheet’s contents.
• After selecting the sheet, we must use the shortcut ‘Ctrl + C’ to copy the entire sheet.
• After copying the sheet, we must go to another sheet (destination sheet) from the sheet tab. If there is no sheet in the active workbook, we can create a new sheet using the shortcut ‘Shift + F11’ or ‘Alt + Shift + F1’. We must select the first cell of the destination sheet and use the shortcut ‘Ctrl + V’ to paste the content copied from the source sheet.

Specific Cases of Copying a Worksheet in Excel

The methods discussed above are useful for copying a worksheet in basic cases, especially when copying a worksheet in the same workbooks. However, there may be various specific use cases when we might need to copy a worksheet to another existing or new workbook, copy multiple sheets, copy worksheet contents with formulas, and others. In such specific cases, we need to follow some additional steps.

The following are some most common scenarios when we may need to copy a worksheet in Excel:

Copying a worksheet to another workbook

When copying a worksheet to another workbook, we can use any of the four methods discussed above. Let us understand each:

By Using Dragging

• First, we need to open both the Excel workbooks, one we want to copy and the other to which we want to copy.
• After opening both the Excel workbooks, we need to navigate the View tab and click on View Side by Side option from the section Windows. This will arrange both the workbooks horizontally on the screen side by side.
  
• Next, we must select the worksheet from the first workbook (source workbook). We need to hold the Ctrl key and drag the selected worksheet onto the other workbook.
  
• Lastly, we must drop the selected worksheet at the desired place between the worksheets in the Sheet tab on the second workbook.
  

By Using Right-Click

• We need to right-click on the source worksheet and choose ‘Move or Copy’.
  
• Next, we must choose the target or destination workbook from the drop-down under ‘To book’. However, the target workbook must be active in the background.
  
  If we want to copy the worksheet into a new workbook, we can choose the ‘New Book’ option from the drop-down list.
  
• After selecting the workbook, we must select where to copy the worksheet. For example, before any existing worksheet or at the end in the destination workbook. Lastly, we must tick the ‘Create a copy’ box and click the OK button.
  

By Using Ribbon

• First, we need to open the source workbook and the destination workbook.
• Next, we must select the worksheet to copy in the source workbook (Book1). We must navigate the Home tab and choose the Format button. After that, we must click the ‘Move or Copy Sheet’ option.
  
• Excel will launch the Move or Copy dialogue box, shown in the previous method. We need to select the destination workbook (Book2, Book3, or New Workbook), choose a location on the sheet tab to copy the worksheet, tick the ‘Create a copy’ box, and click OK.
  

By Using Keyboard Shortcuts

• First, we need to open both the source and destination workbooks.
• Next, we must open a worksheet to copy in the source workbook, select the entire sheet using ‘Ctrl + A’, and copy it using ‘Ctrl + C’.
• After copying the worksheet, we need to go to another workbook. Open the destination sheet in the destination workbook and paste a worksheet using ‘Ctrl + V’.

Copying multiple worksheets or tabs at once

Instead of copying a single worksheet, we can also copy multiple Excel worksheets at once. However, we must select all desired (source) worksheets to copy before using any of the methods discussed above, like dragging, right-clicking, using ribbon or keyboard shortcuts.

To select multiple worksheets, we can use any of the following ways:

• When we need to select adjacent worksheets (or contiguous sheets), we must hold the Shift key and click on the first and last worksheet from the sheet tab. This will select/ highlight all the worksheets between the first and last selected sheets.
• When we need to select non-adjacent worksheets (or non-contiguous sheets), we must hold the Ctrl key and click on each worksheet one by one from the sheet tab. This will select/highlight only those sheets we clicked while holding the Ctrl key.

Copying a worksheet containing formulas

Typically, we can copy a worksheet with the formulas using any methods discussed above. Also, the formula references adjust automatically in most cases. For example, suppose we copy a Sheet1 with formulas to another sheet within the same workbook. In that case, the formulas get changed to refer to the source sheet unless we have not used the external cell references pointing to another worksheet or Excel workbook.

The following image displays the auto adjustment of formulas from Sheet1 when copied to a new sheet:

By default, when copying a worksheet to another workbook, the formula references get changed in the following ways:

References used for the worksheet in another workbook, either absolute or relative, will refer to the same worksheets as the original or source workbook. The cell references or sheet references do not get changed. However, the references in another (or new) worksheets will also point to the original workbook.

For example, our example displays that the references in another worksheet have automatically changed slightly compared to the original workbook. Although cell references or sheet names are the same, a workbook name is added before the worksheet name in the copied worksheet.

To fix this issue or change the formulas in another workbook to match the references of the destination workbook, we must remove the source workbook name from the formulas. This can be done using Excel’s ‘Find and Replace’ tool. We must perform the following steps after copying the worksheet with formulas in another workbook:

• First, we must select the copied worksheet in another (or new or destination) workbook and navigate to Formula > Show Formulas from the Ribbon to make all the formulas visible.
  
• Next, we must select all the cells with formulas where we want to make changes (remove original workbook name).
• After selecting the formula cells, we need to use the shortcut ‘Ctrl + H’ to open the ‘Find and Replace’ dialogue box.
• We must type the original workbook name next to the ‘Find what’ box in the dialogue box. In our example, we type ‘[Book1]’ as it is the name of our original workbook. Moreover, we must leave the ‘Replace with’ box empty, as displayed in the following image:
  
• Lastly, we must click the Replace all button. This will remove the name of the original workbook (i.e., Book1) from formulas and make them suitable for the destination workbook. As in our example, the formula in the destination workbook has changed from =[Book1]Sheet2!B1*[Book1]Sheet2!C1 to =Sheet2!B1*Sheet2!C1.
  

Important Points to Remember

• When using the dragging feature to copy a worksheet in Excel, we can copy only in the same workbook, not in other workbooks.
• When using Excel’s Move or Copy feature to copy a worksheet to another workbook, Excel restricts us from selecting the destination workbook from the drop-down list. The drop-down list displays only active workbooks. So, before using the Move and Copy feature, we need to open both the source worksheet and the destination workbook (or worksheet).