Overview of the IMPORTRANGE Function
- Function Objective
The IMPORTRANGE function allows you to retrieve specific ranges of values from an Excel worksheet and import them into a Google Sheet. It’s important to note that this function is only available in Google Sheets.
- Syntax
=IMPORTRANGE([spreadsheet_url], [range_string])
- Arguments Explanation
Argument | Required/Optional | Explanation |
---|---|---|
spreadsheet_url | Optional | This is the web link or URL of the source workbook from which you want to import data into a new worksheet. |
range_string | Optional | Specify the specific cell or range of cells that you want to import from the original data. |
Since the IMPORTRANGE function is not available in Excel, we need to first import the Excel workbook into Google Sheets. Without this step, we won’t be able to transfer data using the function. Let’s dive into the methods for using the IMPORTRANGE function.
Method 1 – Applying IMPORTRANGE Function in Google Sheets for Importing a Single Worksheet from Excel
There are two ways to do this:
1.1. Use of URLs or Spreadsheet Keys
- Import the Excel file into Google Sheets:
- Open a blank sheet in Google Sheets.
- Go to the File tab and select Import.
-
- Choose your required file from Google Drive.
- Select the type of import location from the drop-down menu and click Import data.
-
- The Excel workbook is now in Google Sheets.
- Copy the shortcode from the URL of the source dataset (press Ctrl + C on your keyboard).
- Enter the IMPORTRANGE function in Cell B4 of a new sheet and paste the URL within two quotation marks, like this:
- Along with the URL, insert the sheet name and cell range (B4:F11) according to the source dataset.
=IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F11")
- You’ll encounter a #REF! error, which prompts you to give access for connecting both sheets. Press Allow access.
- You’ll get the data imported all at once.
- After formatting, the data table will look like this:
1.2. With Named Ranges
- Select the cell range B4:F7 in the source dataset.
- Right-click on it and go to View more cell actions.
- From the Context Menu, select the option Define named range.
- Provide a name for that specific range and click Done.
- Enter this formula in Cell B4 of the new sheet.
=IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","NamedRange1")
- Press Enter, and you’ll get the output.
- Format the dataset to achieve the final result.
Method 2 – Using the IMPORTRANGE Function to Extract Data from Multiple Worksheets in Excel to Google Sheets
2.1. Using Arrays
When transferring data from multiple sheets simultaneously, we create an array formula that works as a group. Follow these steps:
- Insert the following formula in Cell B4 of the new sheet:
={IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F10");IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2019!B5:F10")}
- Press Enter.
You will now have the dataset from two worksheets combined into a single one.
2.2. Using the QUERY Function
We can also combine the QUERY function with IMPORTRANGE to retrieve data from multiple sheets. The QUERY function allows you to filter data based on specific conditions and reorder columns. Follow these steps:
- Enter the following formula in Cell B4:
=QUERY({IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2018!B4:F10");IMPORTRANGE("1YMPPrxRzTv_c1Bb9nsMqykM3nPbJq6WMhat7WVl7vyI","2019!B5:F10")},"select * where Col1 is not null")
- Press Enter.
- You will obtain the final output.
Errors while Using IMPORTRANGE Function
You may encounter various errors while using IMPORTRANGE. Here are two common ones and their reasons:
- Error #N/A: Occurs when the formula has incorrect arguments or when URLs/cell ranges are not enclosed in quotes.
- Error: #REF!: Appears when the URL requires access permission. Even if the Google Sheet is not in Editor mode, this error will occur.
Things to Remember
- Enclose each argument in quotes to avoid errors.
- Changes made in the original worksheet will automatically update in the new sheet where the IMPORTRANGE function is applied.
- Data synchronization occurs in one direction; attempting to modify data in the new sheet will result in an error.
- You can use entire column references instead of cell ranges in the formula.
Related Articles
- How to Copy and Paste from Excel to Google Sheets
- [Solved:] Copy Paste from Excel to Google Sheets Not Working
- How to Copy from Excel and Paste to Google Sheets with Formulas
- Pros and Cons of Google Sheets vs Excel – Which Is Better?
<< Go Back to Export Data from Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!