Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconData Sciencebreadcumb forward arrow iconHow to Use IMPORTRANGE Functions in Google Sheets?

How to Use IMPORTRANGE Functions in Google Sheets?

Last updated:
23rd Aug, 2022
Views
Read Time
6 Mins
share image icon
In this article
Chevron in toc
View All
How to Use IMPORTRANGE Functions in Google Sheets?

Google Sheets is one of the most commonly used online spreadsheet programs owing to its easy accessibility and user-friendliness. In Google Sheets, you might often need to transfer and import data from one spreadsheet to another. This is where the IMPORTRANGE function comes in. 

The IMPORTRANGE function is a vital and commonly used function in Google Sheets that help integrate data between spreadsheets without using third-party add-ons. For experienced users, this formula is convenient. However, one must take extra care to eliminate all mistakes. If executed in a rush, it can be quite confusing or faulty.

Copy-pasting the cells in question should do the trick, but in doing so any live connection between the two sheets will be severed. Also, changing the original data will render the second spreadsheet outdated. The IMPORTRANGE function can be used to overcome these challenges altogether. This function lets you import data quickly from one spreadsheet into another, keeping spreadsheets well-synced.

Application of IMPORTRANGE in Google Sheets

The IMPORTRANGE function primarily focuses on importing values from one spreadsheet cell into another. It is mandatory to have access to the other spreadsheet and its URL.

Since Google Sheets has a cloud-based infrastructure, IMPORTRANGE is not complex to use. This is because all files have separate URLs with a unique identity and these individual files can be referenced using the “key” or their URL modifier. After this, specificity is added within the target file by using the reference of a page and a range.

For one-time and occasional requirements to import data from one sheet to another, this is a very useful function, but not useful for performing in multiple spreadsheets for multiple data imports.

Learn data science courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Explore our Popular Data Science Courses

Important Features Of The IMPORTRANGE Function in Google Sheets

The IMPORTRANGE function might seem intimidating to beginners, but it is not. All you need is the spreadsheet URL and the syntax below:-

=IMPORTRANGE([spreadsheet_url], [range_string])

As per the above syntax, the IMPORTRANGE function has two parts.

  • Spreadsheet_url: It is the identifier unique to only one spreadsheet in Google Sheets uses. It should be the URL related to the Google Sheets spreadsheet from which data needs to be imported and kept within double quotes. You can also keep the URL in a cell for using the cell reference. 
  • range_string: For using the IMPORTRANGE function with the range_string the location of the spreadsheet where the concerned cells are located needs to be clarified. A spreadsheet can have multiple tabs, each having its individual sheet. The syntax for using IMPORTRANGE in the range_string function is:-

“[sheet_name!]range”

For example, if data from the cells C7:B8 from a sheet named Hello is to be imported, the format should be “Hello!B7:C8”. This function initiates the data import and data display on the sheet. The data transfer occurs within seconds when a small range of data is transferred but might a little longer to import a large dataset.

If the sheet name is not specified, the formula will import data from the first sheet by default. You can put this text in a cell and use it for the exact cell reference in the second argument.

How To Use The IMPORTRANGE Function in Google Sheets

IMPORTRANGE is fairly easy to use if you are an experienced professional. As a beginner, it can be a bit confusing. So, here is a step by step guide on how to use IMPORTRANGE function in Google Sheets:-

  1. Click and copy the URL in the address bar at the top of the browser. Alternatively, copying the spreadsheet key from the URL also does the trick.
  2. Open a new spreadsheet and type “=IMPORTRANGE(” – without quotations.
  3. Paste the URL and type a closing quote (“).
  4. Add a comma, type a quote (“) and add the range of the required cells. It should look something like this: 

“Sheet2!B7:C8” 

Here, the spreadsheet named “Sheet1” is being specified, and the cells B7 through C8 are the desired cells from which data needs to be imported.

  1. Add a closing quote and click Enter.
  2. The complete function should look like the following:

=IMPORTRANGE([spreadsheet_url], “Sheet2!B7:C8”)

If the function fails, there’s a #REF! error in the cell. To solve this, click on this cell, and a message will pop up with the instruction that the sheets need to be connected. Hit “Allow access” for the data to appear. This function must be executed only once for each spreadsheet from where data is imported.

How to use the IMPORTRANGE function with a named range

A named range can also be used instead of specifying the range manually if preferred. To carry out this process, follow the below steps:-

  1. Select the range in the original spreadsheet, and then right-click.
  2. Choose “Define named range” in the dropdown menu.
  3. Name the selection in the Named ranges pane and then click “Done.”
  4. Now while adding your range_string to the IMPORTRANGE function, just enter this name, that already has the name of the sheet. This process is easier than building the argument manually.

Tips for using IMPORTRANGE Function

  1. Create named ranges: Importing the data from multiple sheets can be confusing and result in errors. Creating named ranges in the source sheet is a good practice to avoid this. In addition, it is an easier way to specify a range.
  2. Pull entire columns instead of a specific range: When you want to add data to the source sheet, pulling a specific range might seem the ideal option, but it is wiser to pull the entire column. For example, instead of ‘Hello’!B7:C8, use ‘Hello’!B:C. This will pull the data from entire B and C columns. This will automatically update the destination sheet once more data is added to the source sheet. When changes are made in the original spreadsheet, the update usually appears after a few seconds.
  3. Always use quotes: In Google Sheets, both the URL and range are considered strings. Therefore, always use quotes to enclose them.
  4. Use the spreadsheet key: Make a habit of using the spreadsheet key rather than the entire URL. Most browsers will automatically select it if you double-click that portion of the URL in the browser’s address bar.
  5. Data synchronisation is one directional: If you try to change a value in the new spreadsheet, you’ll find that an error message will appear. To get the imported data back in the new spreadsheet, delete the data you tried to add.

Read our popular Data Science Articles

Conclusions

Needless to say, IMPORTRANGE is quite a handy function, especially for students as well as professionals working on Google Sheets. It can greatly help learners better grasp data-related knowledge that is instrumental for data science and related roles. 

Aspirants looking to enter the technical domain should check out Executive Post Graduate Programme in Data Science

 

Profile

Rohit Sharma

Blog Author
Rohit Sharma is the Program Director for the UpGrad-IIIT Bangalore, PG Diploma Data Analytics Program.

Frequently Asked Questions (FAQs)

1What is the query in IMPORTRANGE?

The QUERY function can manipulate data while importing it from a different sheet. One can select, sort, filter, and perform various manipulations.

2Is IMPORTRANGE dynamic?

No, IMPORTRANGE is not dynamic because the ranges do not change once the range is dragged down. The dynamic formula gets modified instead when it is dragged down.

Explore Free Courses

Suggested Blogs

Python Developer Salary in India in 2024 [For Freshers & Experienced]
908669
Wondering what is the range of Python developer salary in India? Before going deep into that, do you know why Python is so popular now? Python has be
Read More

by Sriram

21 May 2024

Binary Tree in Data Structure: Properties, Types, Representation & Benefits
89024
Data structures serve as the backbone of efficient data organization and management within computer systems. They play a pivotal role in computer algo
Read More

by Rohit Sharma

21 May 2024

Data Analyst Salary in India in 2024 [For Freshers & Experienced]
22144
Summary: In this Article, you will learn about Data Analyst Salary in India in 2024. Data Science Job roles Average Salary per Annum Data Scient
Read More

by Shaheen Dubash

20 May 2024

Python Free Online Course with Certification [2024]
134639
Summary: In this Article, you will learn about python free online course with certification. Programming with Python: Introduction for Beginners Le
Read More

by Rohit Sharma

20 May 2024

13 Interesting Data Structure Projects Ideas and Topics For Beginners [2023]
248183
 In the world of computer science, understanding data structures is essential, especially for beginners. These structures serve as the foundation for
Read More

by Rohit Sharma

20 May 2024

Top 30 Python Pattern Programs You Must Know About
40865
Summary Pattern in Python or “Python patterns” is an essential part of Python programming, especially when you are just starting out with using algor
Read More

by Rohit Sharma

19 May 2024

15 Exciting Data Science Project Ideas &  Topics for Beginners [2024]
956191
Summary: In this Article, you will learn about 15 exciting data science project ideas & topics for beginners. 1. Beginner Level | Data Science P
Read More

by Rohit Sharma

16 May 2024

Binary Tree vs Binary Search Tree: Difference Between Binary Tree and Binary Search Tree
63078
Introduction Sorting is the process of arranging the data in a systematic order so that it can be analysed more effectively. The process of identifyi
Read More

by Rohit Sharma

16 May 2024

Top 12 Fascinating Python Applications in Real-World [2024]
157599
It is a well-established fact that Python is one of the most popular programming languages in both the coding and Data Science communities. But have y
Read More

by Rohit Sharma

16 May 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon