How To Copy A Sheet In Google Sheets? Tested Methods!

Are you finding it difficult to transfer data from one sheet to a different sheet as it is? But it is easily possible with GS. But yes, it is also true that not many people know how to copy a sheet in Google Sheets the easiest way.

You need to just right-click on the sheet name on the bottom panel and then click on the “Duplicate” option to create a duplicate copy on the same spreadsheet. And you can also use the “Copy To” option if you want to create a copy on a different spreadsheet. You can also copy data using the “Paste Special” option.

However, you need to follow the right steps if you need to copy fast and easily. So, without any further ado, let’s know about all the working methods.

Copy/Duplicate A Sheet In Google Sheets

There are two things you can do in terms of copying an entire tab or spreadsheet on Google Sheets. First, you can copy a tab in the same spreadsheet. Or you can copy a tab in a completely different spreadsheet stored on your Google Drive.

Let’s now check out both ways. And you can use any of the methods depending on your needs.

How To Copy/Duplicate A Sheet In The Same Spreadsheet In Google Sheets?

There are again two options available for this operation in Google Sheets. You can either use the “Duplicate” function or can use the “Copy to” function. Let’s start with the easiest one.

Method 1: Using The “Duplicate” Option

Google Sheets offers a direct function with which you can create a duplicate copy of the tab of the same spreadsheet. The steps are also very basic.

• Step 1: Launch Google Sheets and open your spreadsheet.
• Step 2: Navigate to the tab which you want to copy to the existing spreadsheet.
• Step 3: Right-click on the tab name (on the bottom panel) to get the contextual menu.
• Step 4: Click on the “Duplicate” option.

• Step 5: You can now see a new tab has been created (with copied data) with the name “Copy of <original tab name>” by default.
• Step 6: Now, navigate to the newly created tab and right-click on the tab name from the bottom panel.

• Step 7: Click on the “Rename” option if you want to change the name of the new tab.
• Step 8: Enter your desired name and hit the “Enter” button to finish the operation.

The newly created tab with the “Duplicate” option will copy all the formatting and formulas (if any). So you don’t need to reinsert the formula or reformat it.

Bonus: Do you know that you can now search for any particular data in Google Sheets? Check out my latest search guide of Google Sheets to know in detail. 

Method 2: Using The “Copy To” Option

This is a different method which will yield almost the same result. And if you can’t make a duplicate copy using the previous option, you can use this one. Now, let’s check out the steps!

• Step 1: Open the spreadsheet that you want to make a duplicate on Google Sheets.
• Step 2: Navigate to the sheet (tab) which you want to copy.
• Step 3: Right-click on it (on the bottom panel) to get the contextual menu.
• Step 4: Hover your mouse over the “Copy To” option, and a side menu will appear.

• Step 5: Now, click on the “Existing Spreadsheet” option.
• Step 6: On the new popup window, select the “My Drive” tab (if it is stored on your Google Drive).

• Step 7: Type the name of the spreadsheet in the search bar and hit the enter button.
• Step 8: Select the spreadsheet (the same one) and click on the “Insert” button on the bottom-right corner.
• Step 9: You can now see a new tab with the “Copy of <original tab name>” default name created.
• Step 10: Finally, rename the tab by right-clicking on it.

You can perform the same operation even if a spreadsheet is not stored in your Google Drive but is shared with you. All you need is to go to the “Shared with me” tab on the popup window to find the file. The rest of the process will be the same.

Bonus: Do you find it hard to clear all the content from any cell in a spreadsheet or from the complete spreadsheet? Don’t miss my comprehensive guide on clearing cell content on Google Sheets to know all the methods. 

How To Copy A Sheet In A Different Spreadsheet On Google Sheets?

If you want to make a copy of a particular sheet (tab) in a different spreadsheet, it is also very much possible in Google Sheets. However, the other file needs to be stored in your Google Drive, or you must have editorial access (if it is a shared file).

Steps are very easy to perform. Let’s check it out now!

• Step 1: Open Google Sheets and open the spreadsheet from where you want to copy.
• Step 2: Navigate to the particular sheet on the spreadsheet in the bottom panel.
• Step 3: Right-click on the sheet name at the bottom to get the contextual menu.
• Step 4: Hover the mouse pointer over the “Copy To” option to get the side-loaded menu.

• Step 5: If you want to copy it to a new spreadsheet, click on the “New Spreadsheet” option.
• Step 6: If you want to copy it to an existing sheet, click on the “Existing Spreadsheet” option.
• Step 7: Once you get a popup window, click either on the “My Drive” option or the “Shared With Me” option, depending on where the output file is located.

• Step 8: Now, click on the search bar and type the name of the file where you want to copy the sheet.
• Step 9: Click on the file name and then click on the “Insert” button.
• Step 10: You can now see a new tab (sheet) is created on the output file with all the copied data.

Once you have copied a tab to an existing spreadsheet, it is better to change the name of the sheet by right-clicking on it. You can then get the renaming option by clicking on the “Rename” option.

Bonus: Do you know that CONCATENATE is among the most important functions in Google Sheets that can be a savior in multiple things? Check out my comprehensive guide on the CONCATENATE function in Google Sheets to get all the details.

Copy A Sheet In Google Sheets Using The “Paste Special” Option

I’ve previously said that using any of the “Copy to” or “Duplicate” functions will copy a sheet as it is, with all its formatting, formulas, and even borders. However, you may need only the data or the format to be copied.

It is also possible in Google Sheets. You need to use the “Paste Special” option. And the steps are pretty easy, though!

• Step 1: Launch Google Sheets and open the spreadsheet from where you want to copy.
• Step 2: Click on the first cell of your data range.
• Step 3: Click the “Ctrl + A” buttons together to select the complete data range.
• Step 4: Now, use the “Ctrl + C” shortcut to copy the data range.
• Step 5: Close the spreadsheet and open the other spreadsheet where you want to copy.
• Step 6: Click on the first cell where you want the data to get started.
• Step 8: Navigate to the main menu and click on the “Edit” option.

• Step 9: Hover your mouse over the “Paste Special” option to get the side-loaded menu.
• Step 10: Click on any of the options depending on your requirements.
• Step 11: Finally, check on the spreadsheet to see if it has been properly copied or not!

You’ll get eight options in the “Paste Special” section. And depending on your particular need, you can choose any of them. The options are as follows.

• Values only (Can be accessed through the “Ctrl + Shift + V” shortcut)
• Format only (Can be accessed with the “Ctrl + Alt + V” shortcut)
• Formula only
• Conditional formatting only
• Data validation only
• Transposed
• Column width only
• All except borders

If you want just the data of the source sheet without any formula or formatting, use the “Values only” option in the “Paste Special” menu. And to get just the format of the source sheet, use the “Format only” option.

Bonus: Do you know that it is now even possible to insert emojis and special characters in GS? Check out my latest guide on inserting emojis and symbols in Google Sheets to know more!

Copy A Sheet Using The Sheets Manager Add-on

The methods I’ve mentioned above are basic and come with the default settings. But, if you are looking for advanced controls while copying or duplicating a sheet on Google Sheets, you can use an add-on.

The Sheets Manager add-on developed by Ablebits is my personal favorite, as it gives a lot of customization options. Let’s check out the steps now.

• Step 1: Launch Google Sheets and open a spreadsheet.
• Step 2: Navigate to the main header menu and click on the “Extensions” option.
• Step 3: Hover your mouse over the “Add-Ons” option and then click on the “Get Add-ons” option.
• Step 4: Once the “Google Workspace Marketplace” popup window appears, click on the search bar.

• Step 5: Type “Sheets Manager” in the search field and hit enter.
• Step 6: Click on the very first result and click on the “Install” button.
• Step 7: Once you get a new popup confirmation window, click on the “Continue” button.
• Step 8: Click on the “Allow” button to grant all the necessary permissions.
• Step 9: Now, go back to your spreadsheet and click on the “Extensions” option.
• Step 10: Hover your mouse over the “Sheets Manager” option to get the side-loaded menu.
• Step 11: Click on the “Start” button, and a side widget will appear on the spreadsheet.
• Step 12: Hold down the “Ctrl” button and select all the pages that you want to copy on the side widget.
• Step 13: Click on the “Copy” icon located in the header menu of the side widget.
• Step 14: Finish copying by selecting any of the three available options.

As of 2023, the Sheets Manager add-on gives three options for copying, which are “Current Spreadsheet,” “New Spreadsheet,” and “Existing Spreadsheet.” If you want to create a copy on the same file, choose the “Current Spreadsheet” option.

You can also copy it and create a new file by using the “New Spreadsheet” option on the add-on.

How To Copy A Sheet Using Apps Script?

If you are not very fond of add-ons (as using many add-ons can make Google Sheets work slow, especially in the offline mode), you can also write your own code using Apps Script to get it done.

I’m sharing an Apps Script code that you can use. It will create a new menu ribbon on Google Sheets from where you can copy or duplicate any sheet (tab). See the steps now!

• Step 1: Launch Google Sheets and open the spreadsheet.
• Step 2: Navigate to the header menu and click on the “Extensions” option.
• Step 3: Now, click on the “Apps Script” option to launch the code editor.
• Step 4: In the code editor box on your right-side box, remove the first three default lines.
• Step 5: Paste the following Apps Scripts code in the code editor box:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Copy Sheets')
      .addItem(‘New Spreadsheet’, ‘copyToNewSpreadsheet’)
      .addItem(‘Current Spreadsheet’, ‘copyToCurrentSpreadsheet’)
      .addItem(‘Existing Spreadsheet’, ‘copyToExistingSpreadsheet’)
      .addToUi();
}
function copyToNewSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var newSpreadsheet = SpreadsheetApp.create('Copied Sheets');
    for (var i = 0; i < sheets.length; i++) {
    sheets[i].copyTo(newSpreadsheet);
  }
}
function copyToCurrentSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].copyTo(spreadsheet);
  }
}
function copyToExistingSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var ui = SpreadsheetApp.getUi();
   var result = ui.prompt('Enter the URL of the existing spreadsheet:');
  if (result.getSelectedButton() == ui.Button.OK) {
    var existingSpreadsheet = SpreadsheetApp.openByUrl(result.getResponseText());
    for (var i = 0; i < sheets.length; i++) {
      sheets[i].copyTo(existingSpreadsheet);
    }
  }
}

• Step 6: After inserting the code, simply click on the “Save Project” (floppy icon) button.
• Step 7: Click on the “Deploy” button and allow all the required permissions.
• Step 8: Relaunch your spreadsheet on Google Sheets and click on the “Extensions” option again.
• Step 9: Click to run the “onOpen” function that you have generated with Apps Script.
• Step 10: You’ll see a new menu ribbon called “Copy Sheets” in the header menu.
• Step 11: Click on any of the three available options, and it will automatically copy the sheet.

 When you click on the new menu ribbon, you’ll get three options: “Current Spreadsheet,” “New Spreadsheet,” and “Existing Spreadsheet” options. You can select any of the options, depending on your needs.

And you need to run the code just once. And after that, the new menu ribbon will automatically appear.

Bonus: Do you know it is possible to refer to another spreadsheet on Google Sheets? To know more in detail, don’t miss my latest guide on referencing another sheet, tab, or cell on Google Sheets.

Final Note

It is better to use the “Duplicate” option, which comes as default for copying a sheet to the spreadsheet. You need to just change the name after copying using the “Rename” option. And you can use the “Copy To” option to create an exact copy on a different spreadsheet.

You can also download the file offline and create multiple copies of the spreadsheet in the manual method. But yes, the “Paste Special” option is the only solution if you want to make a copy of just the data, formula, or format. However, while doing it, select the right option.

FAQs

Q. Can you duplicate multiple sheet tabs at the same time?

Yes, you can duplicate multiple sheets or tabs located on the same spreadsheet. To do that, first hold down the “Ctrl” button and select all the tabs that you want to copy by clicking on those. Now, right-click on it and click on the “Duplicate” option to copy multiple tabs at once.

Q. What is the shortcut for duplicate sheets in Google Sheets?

There is no shortcut available on Google Sheets with which you can copy or duplicate a sheet or tab. You can only do it by right-clicking on the tab name from the bottom menu and then clicking on the “Duplicate” option.

Q. How do I copy a Google sheet to another Google account?

To copy a Google Sheet to another Google account, first, open the Google Sheet you want to copy. Next, click on “File”> “Make a copy” and choose the destination Google account from the dropdown menu under “Shared with me.” Finally, click “OK” to create a copy of the sheet in the selected account.

Q. How to merge sheets in Google Sheets?

To merge sheets in Google Sheets, open your spreadsheet and click on the tab of the sheet where you want to merge the data. Then, click on “Data” in the menu, select “Consolidate,” choose the function you want to apply, select the range of sheets to merge, and click “Add” and “Done.” The selected sheets will be merged into one using the chosen consolidation function.