IMPORTRANGE formula is used to import data from one Google Sheets document to another. This is particularly useful when you want to use data from one sheet in another sheet or when multiple people are collaborating on a project and need to share data between sheets. The formula takes in the URL of the sheet you want to import from and the range of cells you want to import.
- How to use
- Examples of using
IMPORTRANGEformula not working?
- Similar formulas to
IMPORTRANGE formula with the syntax shown below, it has 2 required parameters:
- spreadsheet_url (required):
The URL of the sheet you want to import from. This can be the URL of any Google Sheets document, provided that the document is shared with the user who is importing the data.
- range_string (required):
The range of cells you want to import. This should be in A1 notation, for example 'Sheet1!A1:B10'.
ExamplesHere are a few example use cases that explain how to use the
IMPORTRANGE formula in Google Sheets.
Importing data from one sheet to another
Suppose you have two sheets in the same Google Sheets document, Sheet1 and Sheet2. You want to import the data from A1:B10 in Sheet1 to A1:B10 in Sheet2. To do this, you would use the formula
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:B10") in cell A1 of Sheet2.
Sharing data between collaborators
Suppose you are collaborating on a project with someone else and you both have your own Google Sheets documents. You want to share some data with your collaborator, but you don't want to give them access to your entire sheet. To do this, you can create a new sheet in your document, import the data you want to share from your sheet into the new sheet using
IMPORTRANGE, and then share only the new sheet with your collaborator.
Importing data from a different Google Sheets document
Suppose you have two different Google Sheets documents, Sheet1 and Sheet2. You want to import the data from A1:B10 in Sheet1 into A1:B10 in Sheet2. To do this, you would use the formula
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:B10") in cell A1 of Sheet2, where spreadsheet_url is the URL of Sheet1.
IMPORTRANGE not working? Here are some common mistakes people make when using the
IMPORTRANGE Google Sheets Formula:
Incorrect URL format
If the URL of the spreadsheet you want to import from is not formatted correctly, the
IMPORTRANGE formula will return an error. Make sure the URL is enclosed in quotation marks and that it includes the correct protocol (http or https).
Incorrect range format
If the range of cells you want to import is not specified correctly, the
IMPORTRANGE formula will return an error. Make sure the range string is enclosed in quotation marks and that it uses the correct A1 notation format.
Missing access permissions
If you do not have access to the spreadsheet you are trying to import from, the
IMPORTRANGE formula will return an error. Make sure that the spreadsheet is shared with you and that you have the necessary access permissions.
The following functions are similar to
IMPORTRANGE or are often used with it in a formula:
QUERYfunction in Google Sheets is used to query data from a specified dataset. The function allows users to filter and sort data based on specific criteria. The function is commonly used in data analysis and reporting, as well as in creating dynamic dashboards.
VLOOKUPfunction is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.
HLOOKUPfunction is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.
INDEXformula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.
MATCHformula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
You can learn more about the
IMPORTRANGE Google Sheets function on Google Support.