Last Updated on January 18, 2024 by Jake Sheridan
Ever wondered how to duplicate a Google Sheet efficiently in just a few clicks?
This tutorial shows you three methods to copy your spreadsheets, without messing up your data.
Check it out:
3 Ways to Copy a Google Sheet
Here are three different ways to copy Google Sheets:
1. How to copy a Google Sheet into a new or existing Google spreadsheet
Here’s how you can copy data in a Google Sheets into a new or existing spreadsheet.
Open the spreadsheet containing the sheet you want to copy.
Locate the tab of the sheet you wish to copy. You can find the list of tabs at the bottom of the Google Sheets interface.
Right-click on the tab and select the Copy to > New spreadsheet option.
Click on Open spreadsheet to access the new document.
If you instead want to copy your spreadsheet to an existing sheet, right-click on the sheet tab and select Copy to > Existing spreadsheet.
In the File picker dialog box, you can browse your Google Drive files for the destination sheet for your data. Once you’ve selected a spreadsheet you can send your data to, click on Insert.
Google Sheets will let you know once the sheet has been copied successfully. Click Open spreadsheet to view the spreadsheet where your data has been copied to.
The new spreadsheet will have a default name like “Copy of..” followed by the original spreadsheet name.
2. How to duplicate a Google Sheet on your computer
Open the Google Sheets document you want to duplicate.
If your document contains multiple sheets, right-click on the sheet name you want to duplicate.
In the context menu, click the Duplicate option.
Google Sheets should now create an exact duplicate copy of the target sheet.
The sheet name of this copy will be prefixed with “Copy of…” to indicate that the sheet has been copied from an existing sheet.
To rename this sheet, right-click on the sheet name and select Rename. Type the new name of your sheet tab.
Instead of duplicating a sheet within a Google Sheets spreadsheet, you may want to duplicate the actual Google Sheets file in your Drive account.
To accomplish this, first open and log into your Google Drive account.
Next, navigate to the folder where you can find the Google Sheets file you want to duplicate.
Right-click on the Google Sheets document and select Make a copy.
You should now have a duplicate of your selected Google Sheets document.
The copy will have a file name starting with “Copy of…” followed by the name of the original document.
3. How do I copy data from one sheet to another in Google Sheets?
We can copy data from one sheet to another using a variety of methods. The most common include the Copy and Paste command and using a formula.
Here’s how to start copying data from one sheet to another in Google Sheets.
Open the Google Sheets document that contains data you want to copy.
Select the range you want to copy and use the keyboard shortcut Ctrl+C to copy the data to your clipboard.
Navigate to the sheet where you want to copy your data to. Select an empty cell and type Ctrl+V to paste the data that was copied to your clipboard earlier.
One disadvantage of the manual copy-paste method is that the copied data is not linked to the original data. If you want to create a dynamically linked range, we can use the ARRAYFORMULA function.
In the sheet above, we copied over data from Sheet1!A1:E100 using the formula =ARRAYFORMULA(Sheet1!A1:E100).
Since this data is linked to the original dataset, any changes made to the original will reflect in the copy.
Why can’t I make a copy of a Google sheet?
There are a few possible reasons why you might not be able to copy a Google Sheet:
Reason #1 – Lack of Permissions
If you are not the sheet owner, you may lack the appropriate permissions to copy the sheet.
It’s possible that the sheet owner has restricted copying the sheets.
To solve this, you may have to request permission from the owner to allow copying or to ask them to make a copy for you to use.
If you are the sheet owner and want to make sure that others can copy your worksheet, follow these steps:
Open the document you want others to copy.
Click on the Share icon on the top-right corner of the interface.
In the Share dialog box, click on the gear icon on the top-right corner.
Ensure that the option that allows viewers and commenters to copy the document is checked.
Checking this box should fix most issues with copying Google Sheets documents.
Reason #2 – Browser Issues
For whatever reason, your browser may be preventing you from copying a Google Sheets file.
Try clearing your browser cache, opening the sheet in incognito mode, or opening the sheet in a different browser altogether.
Reason #3 – Storage Space Limit
Copying files will actually consume more space in your Google Drive account.
If copying a file will push your consumed space over the designated limit, Google Drive may prevent you from copying the file.
You can check the storage left in your account by accessing Google Drive on your browser and looking at the sidebar.
You can choose to either delete files in your Drive account or pay for more storage.
Reason #4 – Google Workspace Outage
On rare occasions, Google Sheets might experience service interruptions or outages.
You can find out if there are known current issues with Google Sheets by checking out the Google Workspace Status Dashboard.
If there is a service outage, then users may have to wait for the service to be back online.
Is there a copy formula in Google Sheets?
Google Sheets does not directly have a copy formula but there are several ways to reference data from a specific cell or cell range.
Here’s how you can copy values in Google Sheets using formulas:
The simplest way to copy a value from another cell is by typing the cell reference into the formula.
For example, to copy the value in cell A1, select the cell you want to copy to and type “=A1”.
The formula will return the value of the cell being referenced.
If you want to use a formula to copy an entire range, you’ll need to use the ARRAYFORMULA function.
For example, the formula =ARRAYFORMULA(A3:A5) will return the range A3:A5 when evaluated.
We can also use the keyboard shortcut Ctrl+Shift+Enter to convert an existing cell reference into an arrayformula.
Google Sheets also considers any formula within curly braces as an array formula.
If you want to copy data from another Google Sheets document, the IMPORTRANGE function will allow you to do so using a formula.
The IMPORTRANGE function follows the syntax =IMPORTRANGE(“spreadsheet_URL”, “range_string”)
In this example, spreadsheet_URL is the URL of the source spreadsheet and range_string is the range of cells you want to import.
In the example above, we used the IMPORTRANGE function to copy the values in the cell range A1:E25 in another sheet labeled shirt_orders.
The QUERY function can also be used to copy ranges.
The best way to do this is by setting the query to “SELECT *” with no additional clauses to ensure that all data will be returned.
In the example above, we used the formula =QUERY(A1:C4,”SELECT *”) to create a copy of the range A1:C4.
Hopefully this guide has given you an overview of the different ways to copy Google Sheets.
What’s Next?Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.