Duplicating a spreadsheet in Excel is not a walk in the park. This article brings you a step-by-step guide on how to duplicate a sheet in Excel using some tested and trusted methods.
How to Duplicate a Sheet in Excel
Duplicating a sheet in Excel entails making a copy of the same spreadsheet in Microsoft Excel. There are different ways you can employ to duplicate a sheet in Excel.
How to Duplicate a Sheet in Excel Manually
You can duplicate the sheet manually by using the copy and paste method:
Step #1: Select data you want to duplicate in the worksheet.
Step #2: Press CTRL+C to copy all the data on the sheet.
Step #3: Click the plus sign to add a new blank worksheet to your workbook.
Step #4: Press CTRL+V to paste the data into the new sheet.
How to Duplicate a Sheet in Excel by Dragging and Dropping
You can quickly duplicate a sheet in Excel by clicking and dragging your cursor.
Step #1: First select the sheet to be duplicated.
Step #2: Hold down the ‘Ctrl Key‘.
Step #3: Proceed to select the sheet you want to duplicate sheet. Let it be highlighted blue.
Step #4: Then tick the ‘Create a copy’ box.
Step #5: Click ‘OK’.
How to Duplicate a Sheet in Excel by Right-clicking
You can also use the right click method to duplicate your desired sheet in Excel.
Step #1: Open the sheet to be duplicated.
Step #2: Right-click on this sheet.
Step #3: Scroll up the menu and select the ‘Move or Copy’ option.
Step #4: A box will pop up for you to select the workbook you want to paste your duplicate sheet into select and highlight, then click ‘OK’.
Step #5: Select and highlight the sheet you want to appear before your duplicate sheet.
Step #6: Tick the ‘Create a copy’ box and click ‘OK’.
How to Duplicate Sheet in Excel on Mac
For Mac users, the same method for dragging and dropping your duplicate Excel sheet still applies but, you have to select a different button.
Step #1: First select the sheet to be duplicated.
Step #2: Hold down the ‘Command‘ button on your Mac keyboard.
Step #3: Select and highlight the sheet you want to appear before your duplicate sheet.
Step #4: Then tick the ‘Create a copy’ box.
Step #5: Click ‘OK’.
How to Duplicate a Sheet in Excel Shortcut
Keyboard shortcut makes life easy for the average Excel user. To use a shortcut key to duplicate a sheet in Excel:
Step #1: Press ‘CTRL+C‘ to copy the sheet.
Step #2: Then click the plus sign to add a new blank worksheet.
Step #3: Click the first cell in the new worksheet and press ‘CTRL+V‘ to paste the data.
Common Errors You Might Encounter and Troubleshooting
Sometimes, you might encounter one or two errors when trying to duplicate sheets in Excel. Some of these errors include:
- Insufficient Memory Error: You might experience an insufficient memory error while trying to duplicate sheets in Excel. Especially if you are trying to use the copy and paste option, the insufficient memory error messages will appear when trying to copy into a large area on your worksheet.
Try to increase the amount of memory that Excel has to work with and see if this error stops. Simply go to File > Click on Options > Select Advanced > Click on Memory Usage and increase the number in the ‘Memory Usage’ field. - Name Conflict Error: If you see this error, it means you have a similar name range in the workbook you are pasting the sheet you are duplicating to. it is simply a clash of names with the error message ‘Name already exists on destination sheet‘. You have the option to change the name. In the New name section, enter a new name for the range and click ‘OK‘. The name will be changed instantly, and you can proceed.
- Read-Only Mode Error: This error occurs if the file is read-only. You have to take it out of read-only mode by right-clicking on the file and selecting Properties. Then uncheck the Read-only attribute and click ‘OK‘.
These are the common errors one can experience while trying to duplicate their sheets in Excel. Follow the trouble-shooting tips highlighted here to fix these issues when they come up.
FAQs
How to copy Excel sheet to another sheet with same format
To copy from one sheet to another sheet while still maintaining format, you have to use the copy and paste option. Here’s how to do thus:
Step #1: Select data you want to duplicate in the worksheet.
Step #2: Press ‘CTRL+C‘ to copy all the data on the sheet.
Step #3: Click the plus (+)sign to add a new blank worksheet to your workbook.
Step #4: Press ‘CTRL+V‘ to paste the data into the new sheet.
What shortcut can you use to rename a worksheet?
Press these keys on your keyboard, ‘Alt+H > O > R‘. Type in the new name and press ‘Enter‘.
How to copy Excel sheet with formulas
This is very easy to achieve:
Step #1: First click the cell with the formula you want to copy.
Step #2: Select it and press ‘Ctrl + C‘ to copy the formula.
Step #3: Go to the cell or a range of cells where you want to paste the formula.
Step #4: Press ‘Ctrl + V‘ to paste the formula.
How to copy an Excel workbook with multiple tabs
To do this, use the right-click option to move the tabs to the preferred tabs.
Step #1: Select the Sheet.
Step #2: Right Click and Choose ‘Move or Copy‘.
Step #3: Check ‘Create a Copy‘.
Step #4: Select the ‘Location‘.
Step #5: Click ‘OK‘.