Skip to content

Google Sheets IMPORTRANGE Walkthrough

Learn how to use the IMPORTRANGE function in Google Sheets. The formula is explained and multiple step-by-step example use cases are introduced.

IMPORTRANGE cover image

Working with Google Sheets often requires data imports from other sheets. IMPORTRANGE is a helpful function enabling you to work across multiple sheets and share data between them. If you would like to try the examples for yourself, open the Google Sheets links, select "File" from the toolbar and select "Make a copy".

  1. IMPORTRANGE Formula
  2. Example #1
  3. Example #2

IMPORTRANGE Formula

The IMPORTRANGE function has two required inputs, the spreadsheet_url and range_string.

IMPORTRANGE Formula
  • The spreadsheet_url specifies the URL of the spreadsheet you intend to import data from. It must be enclosed within quotation marks or otherwise be a reference to another cell containing the spreadsheet URL.
  • The range_string parameter selects the data to be imported. Range_string is composed of two elements, the sheet name and the range within that sheet to be imported. It is formatted as the sheet name, followed by an exclamation mark, followed by the range, for example, "Sheet2!A1:B10".
Google Sheets IMPORTRANGE example using spreadsheet id
Using spreadsheet ID
importrange example with full URL
Using full URL
  • Either the full URL or just the spreadsheet ID can be used for the spreadsheet_url parameter.
  1. The first time you try to import from another sheet you will see a #REF! error. You will need to press "Allow access".
  2. If you do not own the spreadsheet you are trying to import data from you will receive the error, "You don't have permissions to access that spreadsheet". If this happens you can go to the URL of the spreadsheet and request access. If the owner grants you access as an editor you will be able to import data using the IMPORTRANGE function.
ref error importrange google sheets

IMPORTRANGE will automatically check for updates every hour while the sheet is open to ensure the data is up-to-date.

Example #1

In the first example, IMPORTRANGE will be used to import a table from a source spreadsheet to a main spreadsheet. You can access the live source spreadsheet here and the sheet where the IMPORTRANGE function will be used is here.

On our source data sheet there are three key pieces of information we need to take a note of in order to correctly format our IMPORTRANGE function:

  1. The URL of the spreadsheet, which is located in the address bar and highlighted below.
  2. The range of the data, in this case it is A1:C10.
  3. The name of the tab upon which the data is located, in this case it is "Sheet1".
Example data ready for import for importrange function example in google sheets

Once we have these parameters we can return to our main spreadsheet to begin writing our IMPORTRANGE function. In this example we will use our spreadsheet ID, which we can use for the spreadsheet_url and our range string Sheet1!A1:C10.

Putting both parameters into the IMPORTRANGE formula we arrive at the function below:

Next, grant permission for the sheet to pull data from the source.

After a short loading period, the date from the source sheet will appear in the main sheet, as shown below. Adjusting the amount of data imported can be achieved by adjusting the range_string cell parameters.

Result of IMPORTRANGE function usage

N.B. In the above example we have just one tab on the sheet we are importing from, so by default it is the first (and only) sheet. In this example it is possible to omit the tab name in the range_string parameter. This is because Google Sheets defaults to the first tab when the tab name is left out of the query.

IMPORTRANGE without tab name example
Another way to achieve the same result with the tab name omitted.

However, when working with data across multiple tabs, it is important to include the sheet name.

Example #2

In many situations, data in the source sheet will need to be filtered and reordered when being imported. In our second example we will look at filtering the imported data using IMPORTRANGE multiple times.

The raw data this time has multiple additional columns.

Raw data for importrange example

The boss wants to take a closer look at advertising costs, office expenses, and client entertainment each month. He is not familiar with Google Sheets and has asked us to present it on another document.

The first way to achieve this filtered view is via using the IMPORTRANGE function three times which is the method we will demonstrate first. In this example, we will be using the same spreadsheet_url multiple times so instead of using the full URL we will use a cell reference. Our spreadsheet will be in cell E2.

spreadsheet cell reference in E2

The first time the sheets are connected we will again need to allow the main sheet to access the source sheet.

Allow access importrange

The data for the month is in column A, advertising costs in column C, office expenses in column E and client entertainment in column F.

To filter for just the month column, the formula will be:

Showing filtered importrange formula
Showing filtered importrange example

Filtering for advertising costs will be the same, except we need to adjust the column being imported to column C.

Filtering for column C importrange

We can combine the import for columns E and F as they are alongside each other in the source sheet.

Filtering for columns E and F importrange

The result is a filtered table with all of the data we need in the format we needed it in!

result of filtered importrange

Wrapping Up

The intention of this guide was to demonstrate multiple ways in which the IMPORTRANGE function and its parameters can be used. However you need to import your data, I hope this guide has given you a foundational understanding that you can apply to your specific problem. If you have any questions, don't hesitate to drop a comment down below.

Comments

Latest

As an Amazon Associate we earn from qualifying purchases.