HOW TO USE IMPORTRANGE FUNCTION IN GOOGLE SHEETS?

INTRODUCTION

IMPORTRANGE FUNCTION is one of the very important and useful functions in GOOGLE SHEETS .

As IMPORTRANGE  FUNCTION is concerned with the text, so it is present under the TEXT FUNCTION CATEGORY of the functions.

IMPORTRANGE FUNCTION FACILITATES US TO IMPORT OR BRING THE DATA FROM A RANGE IN ANY OTHER SPREADSHEET EASILY. IN OTHER WORDS, WE CAN CONNECT THE CELLS IN ANY DIFFERENT SHEET TO THE CURRENT SHEET.

In many situations, we have data in different worksheets. It is not always possible to keep all the data in a single sheet. Now, if we want to get the data from the different sheets at a single place, we can do so in many different ways. One way can be to copy and paste all the data in any main worksheet or the other smart way is to import the range itself in the main sheet.

In this article , we’ll learn about importing a data range from any other sheet into our main sheet.

In this article we would learn about the purpose, syntax, formula of the IMPORTRANGE FUNCTION and get a better understanding with the help of the examples.


PURPOSE OF IMPORTRANGE FUNCTION IN GOOGLE SHEETS

IMPORTRANGE FUNCTION simply imports a range from any other spreadsheet to the current sheet. The name of the function is self explanatory. Import Range i.e. importing a range.

This function is very useful in many situations.

For example,

We have a master sheet and various other sheets. The data is put in the different sheets and we can import the range [ The cells which are meant for any calculations] from these sheets into the master sheet and get the things going.

Any other situation where we need to connect the cells, we can simply import the range.

NOTE: THE CHANGES IN THE ORIGINAL CELLS WILL REFLECT IN THE SHEET WHERE WE HAVE IMPORTED THE RANGE.



PREREQUISITES TO LEARN IMPORTRANGE FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in GOOGLE SHEETS .
  • Understanding of cells and ranges.
  •  Of course, a google account for  GOOGLE SHEETS .



SYNTAX: IMPORTRANGE FUNCTION

The Syntax for the IMPORTRANGE function is
=IMPORTRANGE ( “URL ADDRESS OF THE SHEET” ( or Cell address containing the sheet address ) , “SHEET NAME!RANGE “( or if no sheet name is given, the range will be taken from the first sheet)

URL ADDRESS OF THE SHEET is the complete address of the sheet from which we want to import the range. A complete address will be something like https://docs.google.com/spreadsheets/d/1Fmpkj1aSWMr3Tov6J0K2tR_1tH4Mk7ehCjf3xfwjWwU/edit#gid=2070757595

 
SHEETNAME!RANGE is the name of the sheet of the workbook being fetched using the URL given in the first parameter and the range which we want to import. For example if we have a sheet named ABC and we want to import A1:A20, the parameter will be something like “ABC!A1:A20”.
 
NOTE: BOTH PARAMETERS OF IMPORTRANGE FUNCTION SHOULD BE IN THE ” “. IF THE SHEET ADDRESS i.e. FIRST PARAMETER IS GIVEN AS THE REFERENCE TO THE CELL WHICH CONTAINS THE SHEET ADDRESS , THERE IS NO NEED OF “”.
 




EXAMPLES: IMPORTRANGE FUNCTION IN GOOGLE SHEETS



EXAMPLE TYPE 1: IMPORTING EMPLOYEE DATA OF STORE A AND CREATING A FINAL LIST

Let us create two different sheets for two different stores in a city.

The sheets contain different data for the employees working in the store.
 
DETAILS OF STORE A IN A SHEET NAMED DETAILS

Similarly , we have the details of the employees in STORE B file with a sheet named DETAILS.

  DETAILS OF STORE B

We want to bring the data from both these sheets into one final Master Sheet where we want the final analysis.

The master sheet is something like this.

 

 
MASTERSHEET TABLE

IMPORTING RANGE FROM STORE A

The first step is to import the data range from store A.

We’ll import it using the explicit address of the STORE A sheet.

FOLLOW THE STEPS TO IMPORT THE DATA FROM STORE A

Open the mastersheet where we want to gather the data from both the stores.

Select the cell B4 which is the cell to get the first cell of data i.e. Left upper corner.

Put the function as =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1M366tAYBJN0TG8sN8rtgsXsJWopdtteaOlUV7DmZbW4/edit#gid=0″,”DETAILS!A4:E8”) where the first expression is the sheet of STORE A whereas the second expression is the range given in the sheet named DETAILS and range is A4:E8.

The process is shown in the picture below.

PUT IMPORTRANGE FORMULA TO FETCH THE DATA

After putting the formula, click ENTER.

If we haven’t allowed the access for the sheet, it’ll show REF error.

Hover the mouse over the error and it’ll ask you for the Access.

Click ALLOW ACCESS.

The data will be fetched.

The process is shown below in the picture.

SORTING OUT THE ACCESS PROBLEM

After fetching the first set of data, our master sheet table will look something like the one shown in the right bottom of the picture above.

Now, it is time to fetch the second table.

We won’t fetch the S.No. this time as it’ll again start from the 1 to 5 and will mess with our data.

We’ll manually put the serial number from 6 to 10.

This time, we’ll use the second type of reference to the sheet.


EXAMPLE 2: USING IMPORTRANGE TO IMPORT DATA FROM STORE B

We have put the sheet addresses in two different cells this time.

Follow the steps to fetch the data from STORE B sheet.

Again open the mastersheet and select C9

Enter the expression as =IMPORTRANGE(J4,”DETAILS!B4:E8″) where J4 contains the URL OF THE SHEET for STORE B as shown in the picture below and second expression is the name of the sheet and range which we want to import.

Click enter.

IMPORTING STORE B DATA

It’ll again ask for the permission if it is the first time linking.

After allowing the access , the data will be imported from the store B.

DATA IMPORTED FROM STORE B

Put the serial number from 6 to 10 and apply the borders in the table.

THE FINAL MASTER TABLE



EFFECT OF CHANGE IN THE STORE DATA

Now let us check if our linking is dynamic or not.

For this, we’ll make a change in any of the STORE RECORDS.

One of the employees of STORE A left the job and we hired a new employee named DOMINIC.

The data was updated.

The following picture shows the process.

UPDATING THE STORE DATA AND CHECKING THE EFFECT

In the above picture, we made a change in the STORE A and we could see the effect in the MASTER SHEET TABLE, which made our data accurate and there was no need to update the data.