Have you ever needed to replicate data from one Google Sheet to another? Look no further than the IMPORTRANGE function. This powerful feature allows you to transfer data from one Google Sheet to another, automatically updating the imported sheet to reflect changes in the original sheet. Say goodbye to the hassle of keeping multiple sheets open!
The IMPORTRANGE Formula
The syntax for the IMPORTRANGE function is as follows:
=IMPORTRANGE(source_url, range)Let’s break down what each entry represents:
source_url: The URL of the original sheet containing the data you want to import.range: The range of cells in the original sheet that you want to import into your new sheet.
How to Use IMPORTRANGE
To use this function, you’ll need two open sheets: one sheet with your original data and a second destination sheet where you want to import the data. Follow along with our step-by-step guide to learn how to identify your inputs and utilize IMPORTRANGE in Google Sheets.
1. In your new sheet, click on the top left cell of the area where you want the imported data to appear.
The cell where you enter the IMPORTRANGE function will become the top left cell of your imported range. So, start your function with =IMPORTRANGE(.
In my practice sheet, I’ll begin my function in cell A1.
2. Copy the URL of the sheet containing the data you want to import.
The first entry, the sheet URL, refers to the URL of the original sheet containing your data. To copy the URL, go to your original sheet, select the entire URL, right-click, and choose “Copy”.
Then, return to your new sheet. Paste the URL into your function, surrounded by quotes ("").
In my practice sheet, my function now reads =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1LdPxJKzgw-cwnGcg2XYjdkbQtNN37oqrqDzoGw6E28k/edit#gid=1837330146",.
3. Identify the range of cells in your original sheet.
The range of cells is the area in the original sheet containing the data you want to import into your new sheet.
The format for the range of cells is SheetName![top_left_cell]:[bottom_right_cell]. Here are a few things to keep in mind when determining your range of cells:
- If your sheet name contains spaces or numbers, enclose the name in single quotes (
'). - If you don’t include a sheet name in the range of cells, this function will automatically import data from the first sheet (tab) of your sheet (workbook).
- Your range can be as small as a single cell. If you only want to import a single cell, simply shorten the cell range before the colon to
SheetName!Cell.
Write the range of cells in your function, surrounded by quotes ("").
In my practice sheet, I’m importing data from the sheet named “Highest-grossing-films-US_1977-2022”. The top left cell of my data is A1, and the bottom right cell is G47. So, my range of cells is 'Highest-grossing-films-US_1977-2022'!A1:G47.
Now, my function reads =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1LdPxJKzgw-cwnGcg2XYjdkbQtNN37oqrqDzoGw6E28k/edit#gid=1837330146","'Highest-grossing-films-US_1977-2022'!A1:G47".
4. Finish your formula with a closing parenthesis ) and press Enter.
The first time you import this data into your new sheet, you will receive a #REF! error message. This is expected at this stage and has to do with sharing permissions.
5. Select “Allow access” to complete the import of your data.
Even though your IMPORTRANGE function will still appear if you select the cell where you entered the function, your new sheet will now display a copy of the data from your original sheet.
How to Reference Another Sheet within the Same Spreadsheet
If you want to import data to a new location within the same spreadsheet, whether it’s on the same tab as your original data or on a new tab, you can use a simplified formula instead of the IMPORTRANGE function. Simply enter an equals sign and the range for the original data into the top left cell where you want your replicated data to appear.
For example:
='SheetName'![top_left_cell]:'SheetName'![bottom_right_cell]If you only want to replicate a single cell, you only need to include that cell in your formula:
='SheetName'!CellIf your reference cell exists in the same sheet as your original data, you don’t need to include the sheet name.
Limitations and Alternatives to IMPORTRANGE
IMPORTRANGE replicates data from one spreadsheet to another and will automatically update the imported data when you add, delete, or otherwise manipulate the original data set. However, when using IMPORTRANGE, you’ll encounter two limitations: (1) you can’t directly manipulate your imported data, and (2) the formatting of your original sheet won’t transfer automatically.
Let’s take a closer look at how to work around these limitations.
Working with Imported Data
Although you can perform certain tasks with your imported data, such as filtering, calculations, and functions, you can’t directly edit your imported data, such as changing text and sorting.
If you want to work directly with your imported data in your new sheet, one solution is to copy your imported data to a new tab. Here’s how:
- Begin by highlighting the imported range and copying the data.
- Create a new tab in your sheet.
- Highlight the first cell in your new tab.
- Go to Edit in the top menu. Then, go to Paste Special > Paste values only.
- Your imported data will be pasted as text that you can work with directly.
However, keep in mind that these copied values won’t automatically update to match the original sheet. You’ll need to repeat this process every time your original data set is updated.
Formatting Imported Data
IMPORTRANGE doesn’t import formatting, only the content of cells. However, you can manually copy and paste the formatting from your original sheet to your new sheet. Here’s how:
- In your original sheet, highlight the cells in your source range and copy them.
- In your new sheet, highlight the range area for the imported data.
- Go to Edit in the top menu. Then, go to Paste Special > Paste format only.
- The formatting from your original sheet will be pasted onto your imported data.
Unfortunately, you can’t copy column widths from one sheet to another, but you can manually resize the cells in your new sheet without affecting the IMPORTRANGE function.
Common Errors and Solutions
If you don’t use the IMPORTRANGE function correctly, you’ll encounter a parse formula error. Here are some common error messages associated with IMPORTRANGE and suggestions for resolving them:
-
#ERROR!: This error usually indicates a syntax issue. Make sure you’ve entered each entry correctly and used proper punctuation, including quotes around the sheet URL and cell range, and correctly formatted your cell range. -
#REF!: This error typically appears in two cases: (1) when you import your data for the first time and need sharing permissions, and (2) if you try to manipulate cells within your imported range. In the first case, select “Allow access” to import your data. In the second case, undo your attempt to manipulate the cells within the imported range. Then, refer to the above solution for working with your imported data.
Continue Learning
Want to further enhance your data handling skills using Google Sheets? Enroll in the Professional Certificate in Data Analysis by Google. You’ll gain insights into spreadsheets and other key analysis tools.
Source: Crawlan.com
