IMPORTRANGE Function to Import Data from Excel to Google Sheets

When you are working on an Excel workbook, you may sometimes need to convert it to a Google Sheet and edit it afterward. But this does not work only with the general copy-paste method. For this, we need to use the IMPORTANGE function. In this article, we will learn how to use the IMPORTRANGE function to get data from Excel to Google Sheets with 2 Unique Methods.

IMPORTRANGE Function Overview

Function Objective

The IMPORTRANGE function is the function that helps to return certain ranges of values from an Excel worksheet to a Google Sheet. This function is only available in Google Sheets.

Syntax

=IMPORTRANGE([spreadsheet_url], [range_string])

Arguments Explanation

Argument	Required/Optional	Explanation
spreadsheet_url	Optional	It is the web link or URL of the source workbook from where we want to import data to a new worksheet
range_string	Optional	This is the specific cell that you want to import from the original data.

As the IMPORTRANGE function is not available in Excel, we have to import the workbook into Google Sheets first. Otherwise, we cannot transfer data using the function. The process of using the IMPORTRANGE function is used for both single and multiple worksheets. So, without any further delay, let’s hop into the methods.

1. Applying IMPORTRANGE Function in Google Sheets for Importing Single Worksheet from Excel

In this first method, we will insert the IMPORTRANGE function for a single worksheet. There are two ways to do this. They are described as follows.

1.1. Use of URLs or Spreadsheet Keys

We can transfer data using the source URL in Google Sheets imported from Excel. Let’s see how it works.

First, we need to import the Excel file into Google Sheets.
For this, open a blank sheet in the Google Sheets window.
Then, go to the File tab and select Import.

After that, select your required file from your Google Drive.
Following, choose the type of Import location from the drop-down menu and click on Import data.

That’s it, we have our Excel workbook in Google Sheets now.

Now, copy the shortcode in the URL of the source dataset by pressing Ctrl + C on your keyboard.

Then, type the IMPORTRANGE function and paste the URL under two quotation marks in Cell B4 of a new sheet like this.

Along with it, insert the sheet name and cell range B4:F11 according to the source dataset.
Lastly, the formula will look like this.

=IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F11")

Afterward, you will get the #REF! error which will ask you to give access for connecting both sheets.
Therefore, press on Allow access.

Finally, you will get the data all at once.

After some ornamentation, the data table looks like this.

Note: When you press Allow access in a worksheet, it is applicable for that entire file. No matter how many worksheets you add later, it will fetch data from the URL without asking for any permission further.

1.2. With Named Ranges

We can also copy and paste some selective cells using the IMPORTRANGE function. For this, follow the steps below.

First, select the cell range B4:F7 in the source dataset.

Then, right-click on it and go to View more cell actions.
Here, select the option Define named range from its Context Menu.

Following, provide a name for that specific range and click on Done.

Now, type this formula in Cell B4 of the new sheet.

=IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","NamedRange1")

Lastly, press Enter and you will get this output.

Furthermore, format the dataset and you will get this final result.

2. Utilizing IMPORTRANGE Function for Extracting Multiple Worksheets from Excel to Google Sheets

We can apply the IMPORTRANGE function for transferring data from multiple Google worksheets as well. Let’s see how it works.

2.1. Apply for Arrays

While transferring data from multiple sheets at the same time, they create an array formula and work as a group. To create this follow these simple steps.

First, insert this formula in Cell B4 of the new sheet.

={IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F10");IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2019!B5:F10")}

Note: You can add as many sheet references in this formula. But make sure to insert the correct sheet name and cell range in each argument.

Then, hit Enter.
That’s it, you will get the dataset of two worksheets in a single one like this.

2.2. With QUERY Function

We can also combine the QUERY function with the IMPORTRANGE function for getting data from multiple sheets. It helps to filter the base of the origin and hide certain columns by reordering them. To do the task, follow the process below.

First, type this formula in Cell B4.

=QUERY({IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F10");IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2019!B5:F10")},"select * where Col1 is not null")

Following, press Enter.
That’s it, you will get the final output.

In this formula, the IMPORTRANGE function imports data from other Google sheets as per given URLs and cell ranges. Along with it, the QUERY function filters each data based on the condition where column 1 is not null.

Errors while Using IMPORTRANGE Function

You may face various types of errors while using the IMPORTRANGE function. Here are two of them describing the reasons.

1. Error #N/A

It occurs when the formula has wrong arguments and the URLs or cell ranges are not inside quotes.

2. Error: #REF!

When your URL requires access permission this error occurs. Even, if the Google Sheet is not in Editor mode, it will show this error.

Things to Remember

Make sure to enclose each argument under quotes to avoid errors.
If you make any changes in the original worksheet, it will automatically update in the new sheet where we applied the IMPORTRANGE function.
Data synchronization only happens in one direction. You will get an error if you try to change any data in the new sheet.
You can insert entire column reference instead of cell range in the formula as well.

Conclusion

Henceforth, I hope that it was a helpful article for you on how to use IMPORTANGE from Excel to Google Sheets with 2 Unique Methods. Let us know if you know of any other methods for this.

<< Go Back to Export Excel to Google Sheets | Export Data from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!