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 them 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 IMPORTRANGE function to get data from Excel to Google Sheets with 2 Unique Methods.


Download Practice Workbook

Get this sample file to try it by yourself.


Introduction to the IMPORTRANGE Function

  • 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.

2 Unique Methods to Use IMPORTRANGE Function to Import Data from Excel to Google Sheets

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 sing and multiple worksheets. So, without any further delay, let’s hop into the methods.

1. Apply 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 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.

importrange from excel to google sheets

  • 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.

Apply IMPORTRANGE Function in Google Sheets for Importing Single Worksheet from Excel

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

Apply IMPORTRANGE Function in Google Sheets for Importing Single Worksheet from Excel

  • 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.

Apply IMPORTRANGE Function in Google Sheets for Importing Single Worksheet from Excel

  • 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.

Read More: How to Import Data from Excel to Google Sheets (2 Easy Ways)


Similar Readings


2. Use 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")}

Use IMPORTRANGE Function for Extracting Multiple Worksheets from Excel to Google Sheets

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")

Use IMPORTRANGE Function for Extracting Multiple Worksheets from Excel to Google Sheets

  • 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.

Read More: How to Sync Excel to Google Sheets (with Simple Steps)


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. Follow ExcelDemy for more Excel-related articles.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo