IMPORTRANGE in Google Sheets
Quick Navigation
There is often a need to mix data from different spreadsheets.
With the IMPORTRANGE function, you can import data (range of cells) across multiple spreadsheets and you can avoid spending a lot of time manually copying big amounts of data from one spreadsheet to another.
What is the IMPORTRANGE Function?
The IMPORTRANGE function has the following syntax:
=IMPORTRANGE(spreadsheet_url, range_string)
The first argument, spreadsheet_url, is the URL of the spreadsheet from which you want to import. You can copy it from the search bar of the browser.
It doesn’t have to your own Google Sheets document. It can belong to someone else, but you need to have permission to access it.
The second argument, range_string, is the data that you want to import from another spreadsheet. You have to write it in the following format: “Name_of_the_sheet!cells”. So it’s the name of the tab found on the bottom (Sheet1, Sheet2, and so on by default) followed by a ‘!’ character and the range of cells that you want to import.
When to use IMPORTRANGE for marketing stuff?
- Combining multiple Google Sheets.
- Pulling reporting data from different sheets
How To Use The IMPORTRANGE Function in Google Sheets
The IMPORTRANGE function allows you to import a range of cells from a specified spreadsheet – this data will then automatically update across all linked sheets.
Formula: =IMPORTRANGE(“spreadsheet_url” , “range_string“)
For example:
There are two parts to this formula:
- URL of the spreadsheet to be imported ( in this case:
https://docs.google.com/spreadsheets/d/1ENcEHL8gKMitPNuEDWHcIn7i3u7mGJVPm-9UhLebbaQ/edit#gid=0) - String specifying the range to be imported. (in this case:
Sheet1!A2:A9)
You can pull in anything that is stored in another Google Sheet, like an Ahrefs keyword research export, backlink data, a Screaming Frog crawl or Search Console export.
If it’s in a sheet in one place, you can use IMPORTRANGE to pull it into wherever you want (providing its’s a Google Sheet obviously)
IMPORTRANGE Formula Example
For example, Sheet2!A2:C5 imports the range A2:C5 from the Sheet2. If you don’t specify the sheet name, the formula would assume that you want to import the data from the first tab found on this URL.
It works with one cell, but you can also import a whole range of cells.
For example, this function works to pull the content of cells A2:A9 from the Sheet1 of another sheet:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ENcEHL8gKMitPNuEDWHcIn7i3u7mGJVPm-9UhLebbaQ/edit#gid=0", "Sheet1!A2:A9")
The first time you use the IMPORTRANGE function in a sheet, you will get an error indicating you to give permission to access the imported spreadsheet. You have to explicitly grant permission to pull data from the other spreadsheet.
Once access is granted, any editor on the destination spreadsheet can use the IMPORTRANGE function to import data from any part of the source spreadsheet.
After you gave access, the IMPORTRANGE function loads the requested data. The imported range will have the data, but it won’t have the same formatting as the source sheet.
This function can be useful when where the source data is continuously updated.
Instead of manually transferring the data from your other spreadsheet to update it every time, you can use the IMPORTRANGE function to do it automatically.
In this case, it’s good to use infinite column references (B2:B) to ensure that the imported range always contains all the rows that the original sheet has.
It’s also useful if you want to keep certain fields hidden while using the rest of the data. In such cases, you can use the function to import only the data that you want to show in the new sheet. (But be aware, that the users of the new sheet would be able to pull all the content of the source sheet manually since they would have access to it.)
For example, you can import the prices of items you purchased without exposing the names of the products. This solution you to work with this specific data without modifying the original source data.
Summary
Although you won’t need to use IMPORTRANGE on every SEO spreadsheet you use, it’s still something useful to know.