How to Use IMPORTRANGE Function in Google Sheets (Complete Guide)

how to Use IMPORTRANGE Function in Google Sheets 21

In this article, we will learn how to use IMPORTRANGE Function in google sheets.

Now in google sheets, we have various ways to connect two individual google sheets files and share data within the files or copy some parts. Similarly, we have IMPORTRANGE, an easy function to connect two or more spreadsheets and makes it very quick and easy for us to transfer data among multiple workbooks. Now since it’s a built-in function so we need to follow the syntax, below we will see and understand the syntax.

See also  How to Alternate Row Color in Google Sheets [Guide 2023]

Use Case of IMPORTRANGE Function in Google Sheets

IMPORTRANGE Function is pretty helpful when we have big data and the data is placed in multiple files, if we select a cell in a google sheets file and write the equal sign (=). And then we go to another worksheet and select a range of data from there, the previous file will not collect the cell references of the range we selected. This is because of cross files compatibility, in other software products like excel we can do this, they allow cross files compatibility. But since google sheets completely work on web so they don’t allow such compatibility features without proper reference, so the IMPORTRANGE is the solution google sheets provide us. We will see how to use it and how it works to learn how to import data from other google sheets files and workbooks.

  • Data import is very quick and easy
  • You can import big data
  • Real-time sharing
  • Automatically updates data from the source data
See also  How to Make Table of Contents in Google Sheets (3 Methods)

How to Use IMPORTRANGE Function in Google Sheets

To learn IMPORTRANGE Function in google sheets we have some methods and some examples using the examples we will see how we can use this function to make our data and range transfer easy within google sheets files and workbooks. Before that, we have to understand some basics about the function.

=IMPORTRANGE(spreadsheet_url, range_string)

  • The IMPORTRANGE is the Function name
  • Spreadsheet_url is the URL (link) of the file from where you want to import the data range
  • Range_String is the range of the data (A2:C10), followed by the file name of the workbook if there are multiple files.

Note that there is a ! mark between the range and the sheet name.

See also  How to Add Date & Time in Google Sheets [2 Methods]

How to Use IMPORTRANGE Function in Google Sheets – To Import a Normal Range

In this section, we will learn how to use IMPORTRANGE Function in Google sheets to import a normal range from another workbook. In this section, we have multiple google sheets files and we will learn how to import data from each file to another file.

So, I have two files 1 is empty and the other has some data, I will fetch the data in the empty file

 

Step 1

In the google sheets file, click on the cell where you want to import the range

how to Use IMPORTRANGE Function in Google Sheets 1

Step 2

= sign

how to Use IMPORTRANGE Function in Google Sheets 2

Step 3

IMPORTRANGE Function

how to Use IMPORTRANGE Function in Google Sheets 3

Step 4

Pass the URL or spreadsheet id inside double quotes

how to Use IMPORTRANGE Function in Google Sheets 4

how to Use IMPORTRANGE Function in Google Sheets 5

how to Use IMPORTRANGE Function in Google Sheets 6

how to Use IMPORTRANGE Function in Google Sheets 7

how to Use IMPORTRANGE Function in Google Sheets 8

Step 5

Pass the file name

how to Use IMPORTRANGE Function in Google Sheets 9

how to Use IMPORTRANGE Function in Google Sheets 10

Step 6

Exclamation mark “!” after the file name

how to Use IMPORTRANGE Function in Google Sheets 11

Step 7

Pass the data range (i.e., A2:C10)

how to Use IMPORTRANGE Function in Google Sheets 12

Step 8

Hit Enter, you may see an error

how to Use IMPORTRANGE Function in Google Sheets 13

Step 9

Understanding the error

how to Use IMPORTRANGE Function in Google Sheets 14

Step 10

Resolving the error by allowing access

how to Use IMPORTRANGE Function in Google Sheets 15

Step 11

Success, the range is imported accurately

how to Use IMPORTRANGE Function in Google Sheets 16

Step 12

Make some changes in the source data

how to Use IMPORTRANGE Function in Google Sheets 17

Step 13

You can see the change is automatically updated.

how to Use IMPORTRANGE Function in Google Sheets 18

 

This is how simply you can work on IMPORTRANGE Function and import your ranges and data from one google sheets file to another.

See also  How to Use SWITCH Function in Google Sheets [Complete Guide]

How to Use IMPORTRANGE Function in Google Sheets – Using Named Range

Named range is nothing but a concept to name our data ranges. This is exclusively available in google sheets and it’s pretty useful we all get confused and can’t remember the range names so this is the ultimate solution. We will mark the ranges we want to import in a sheet, then we will convert those ranges to named ranges, we will assign good names to all ranges and it will make it easier for us to call that range from the name instead of actual cell range.

Many times, we need to have a lot of data to import from various sources and we are confused with the ranges, the column and row names, of course, we cannot memorize all the data ranges, so there is a solution to this problem. Whenever you need to have a lot of import data from multiple spreadsheets file you need to learn this technique to save your time and energy

We have some datasheets from which will collect data ranges and will import them into the final file.

Step 1

You need to have multiple workbooks or google sheets files to follow me with this example

how to Use IMPORTRANGE Function in Google Sheets 19

Step 2

Select the range of the data you want to export into the final sheet

how to Use IMPORTRANGE Function in Google Sheets 20

Step 3

Go to Data > Named Ranges

how to Use IMPORTRANGE Function in Google Sheets 21

Step 4

Give a name to the selected date range

how to Use IMPORTRANGE Function in Google Sheets 22

Step 5

Click on done, and your range has got the name

how to Use IMPORTRANGE Function in Google Sheets 23

Step 6

Now using IMPORTRANGE Function similarly to import this range, but this time we will use the range name, not the cell references.

how to Use IMPORTRANGE Function in Google Sheets 24

how to Use IMPORTRANGE Function in Google Sheets 25

Step 7

The overall syntax will be like below

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1lMv_Pt8Dny94cpTBatRoCplp29jGJ52D4XCwNoNTjJg/edit#gid=0″,”Sheet1!Students”)

Step 8

Press Enter, you can see we have got the same result

how to Use IMPORTRANGE Function in Google Sheets 26

Tip:

Know that when you use IMPORTRANGE Function for the very first time on your sheet, after completing the function parsing the values, and when calculating the answer, it will return a reference error. Don’t worry about it, you have not done anything wrong it is the natural behavior of google sheets. This is an importing function and this error is only a warning (read the error message), it’s a permission, google sheet’s asking you to allow to access the data from other sources (workbooks or google sheets files), when you allow it, google sheets has got the knowledge that you (as a google account holder) authorize this spreadsheet and now google sheets will link that sheet with your current sheet. This is the entire process.

See also  Extract Numbers from Strings in Google Sheets [REGEX Functions]

Notes

  • Use Named ranges when you have multiple ranges to import from one or multiple sheets or workbooks.
  • Named ranges are easy to deal with
  • The function returns an error on the first-time users, it’s a warning simply give the permission to allow it to access the data from other sheets, files, or workbooks one time then it will never ask for it again for the same data sheets.
  • IMPORTRANGE is an importing function, it helps us import the data from other google sheets files directly with live updates
  • Remember that importrange does not copy formatting or style, it only copies values or formula-driven values.
  • Importrange can work on the URL, and also on the key, a spreadsheet key can be used instead of the entire link and it works perfectly.

See also  How to Share Google Sheets [Complete Guide]

FAQs

Why I am facing a reference error when importing a range?

Yes, for the first time the IMPORTANCE function will give you a reference error, read the error and it will ask for permission to import the range from the sheet you have specified by providing the URL and range. This is for the security and safety purposes of data, once you have to permit by clicking on allow button and then it will give you the range imported from that sheet or workbooks, next time you will use the IMPORTRAGE function then it will not ask for any permission and no error will be thrown. This is one-time authentication and after allowing it’s allowed for a lifetime.

Can I use Named Ranges for other purposes?

Named ranges is a new concept introduced by google sheets for naming several ranges in a big data set for better identification and minimizing the reference error and also the problem of memorizing the ranges every time using in a formula. For other purposes, surely yes. Named ranges are not associated with IMPORTRANGE or any other function exclusively. Named Ranges are independent and can be used in any condition, you can use them without using any other formula or function, you can use them directly just to rename the important ranges or the ranges that you think will be used in the formula or function frequently. The main purpose of the named range is to rename the range of cells, logically it can be applied even on a singular cell.

See also  How to Automatically Sort Dataset in Google Sheets

Conclusion

So here, I hope you have gone through the entire tutorial and have learned How to Use IMPORTRANGE Function in Google Sheets. I have covered the basic function and we talked about the syntax as well, then we moved on to the method and we learned step by step how to use IMPORTRANGE Function in google sheets. We talked about the error that is a first-time error for the permission, we have also seen the auto update of data on any change made on the exporter files. We have seen the change is automatically updated in the file, then we moved on and learned a technique called Named ranges exclusively available in google sheets, we have seen how Named ranges work and how we can use them, and how they help us while importing the data. We did an example using Named ranges inside the IMPORTRANGE Function.

So, that’s all for today’s tutorial I hope you have now learned how to use IMPORTRANGE Function in google sheets, see u soon with another tutorial till then take care. Share this article with your friends and don’t forget to subscribe Office Demy blog. Thank you have a nice day.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general