Google Sheets Importrange Function – Basic to Advanced Use Tips

0
359
importrange in google sheets

Google Sheets importrange function is one of the easiest function in Google Sheets and also very useful on the other hand.

I found Query, Sumproduct, and Regex are some of the complex functions in Google Sheets for general use.

In this tutorial I will take you through the basic to advanced use of Importrange function in Google Spreadsheets.

What is the Use of Google Sheets Import Range Function?

When you want to pull data from one spreadsheet to another, use importrange function. That means a specified range of data from file “a” to file “b”.

In some cases you may want to keep your master file intact and share a portion of the data, just use this function. The advanced use part of this Google Sheets tutorial will explain it in details.

There may generally a question arise. Importrange is for import a range of data from one file to another. Then how to importrange data within same file? The question is irrelevant as if you want to pull data within same file but different tabs, you can simply do it without importrange. I will explain it also below under separate title. Now time to move to the function.

Basic Use Use of Google Sheets Importrange Function 

Syntax: IMPORTRANGE(spreadsheet_url, range_string)

Example:

There are two files. File 1 “masterfile”, File 2 “resultsheet”

Our data is in “masterfile”. We want the same data in “resultsheet”

First open both the files on your browser.

Go to the second file “resultsheet”

On any cell, probably in the first cell A1, apply the formula like;

Google Sheets Importrange Example

First you will probably get error!

Google sheets importrange function error

Just click on “Allow access”.

Now see the above first picture. The URL is the URL of “masterfile” from where you want to import data to “resultsheet”. This you can directly copy form the address bar of your browser when your file is in open mode. The URL should be put between double quotes.

The second part of the formula is the range to import. It should enter with sheet name. The sheet name is the name of tab in your “masterfile”. I have infinitive data under column A, B, C, D, E and F, so I put the range A:F. You can restrict the range like A1:F20. The sheet name and range also should put between double quotes. That’s it.

Use Google Sheets Importrange Function to Hide Other Formulas

Here is one example to the advanced use of Google Sheets Importrange function. Hiding formula in Excel is very easy. It you can achieve by the protect sheet method. The same protect sheet is available on Google Sheets too. But not sufficient to hide formulas.

So you can use importrange function in Google Sheets to protect formulas. How?

To cut short this tutorial length, I’ve prepared another specific tutorial for this. Follow this link How to Hide Formulas in Google Sheets Similar to Excel?.

How to Apply Importrange Function within Same Spreadsheet in Google Sheets?

Here there is no URL to use as the data to be pulled between different sheets in the same file.

In this case simply use the array formula as below.

={‘Class Data’!A:F}

Here I have imported the same range used in the above importrange function. But within curly brackets! No URL so no function!

That’s it.

LEAVE A REPLY

Please enter your comment!
Please enter your name here