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.

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.

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.