Back to Blog

IMPORTRANGE Excel – Does It Exist and How to Link Sheets in Excel

In Google Sheets, there is a wonderful function called IMPORTRANGE. It allows you to import and link a specific range of cells from one spreadsheet to another. Basically, it saves more than 50% of your time since you no longer have to copy and paste data from one sheet to another.

Excel is a time-honored spreadsheet app that provides more intense functional stuffing. So, I and many users expect to see IMPORTRANGE or, at least, a similar functionality in Excel. In this guide, I’m going to explain whether you can use IMPORTRANGE in Excel and introduce other options to link Excel worksheets. It will be insightful and fun, so read on!

Is there IMPORTRANGE in Excel?

There are many functions in Excel, such as VLOOKUP Excel or SUMIF Excel. However, there is no Excel IMPORTRANGE function

1-no-importrange-in excel

At the same time, Excel provides a similar to IMPORTRANGE functionality for linking a data range from a separate spreadsheet. This can be done either by copying and pasting a data range or by using a formula bar.

What is the Excel version of IMPORTRANGE?

In Excel Online, the IMPORTRANGE functionality is called Workbook Links. I’ll cover the details of Workbook Links later.

2-workbook-links

In Excel desktop, it has no explicit name but is implemented via the Paste Link option.

3-workbook-links-excel-desktop

The implementation of an Excel IMPORTRANGE equiv depends on the Excel app you use (365/Online or desktop app). Let’s discuss each way separately. 

IMPORTRANGE/Workbook Links alternative for Excel

In essence, both Excel Workbook Links and IMPORTRANGE synchronize data between two sheets. So, if the data in the source sheet changes, these changes are visible in the destination sheet. And this could be an issue if, for some reason, the data in the source sheet was corrupted or the file was broken. As a result, the valuable information disappears in both sheets. With the Excel integrations by Coupler.io, you won’t have this risk. 

Using the Coupler.io web app or add-in for Excel, you can also connect workbooks. However, unlike Workbook Links or IMPORTRANGE, the Excel integration by Coupler.io imports data from one sheet to another. So, if the source workbook is not accessible for some reason, you will still have your data set in the destination workbook. 

Connecting Excel workbooks with Coupler.io only takes three steps and does not require any formulas. All you need to do is sign up to Coupler.io and add a new importer:

  • In your OneDrive folder, select the Excel workbook and sheet(s) to get data from.
step1.etract data from excel
  • Preview and transform the data, if needed, before loading it to the destination Excel workbook.
step2.transform data from excel
  • In your OneDrive folder, select the Excel workbook and sheet to load data.
step3.load data to excel

This is a high-level flow but you can also enjoy a number of optional parameters and features:

  • Select multiple sheets to concatenate data from them into one master view.
  • Specify a range, not an entire sheet, to import.
  • Specify the first cell to load data to.
  • Change the import mode from replace to append. With the replace mode, all the new data will replace the previously imported records. With the append mode, the newly imported records will be added under the previously imported ones.

The icing on the cake is the automatic data refresh. Coupler.io allows you to automate the import of data on a custom frequency from once a month to every 15 minutes. To do this, toggle on the Automatic data refresh and configure the schedule for your data import.

automatic data refresh excel importrange

From the moment you click Save and Run, the data flow between the selected Excel files will be automated. You will be sure to have up-to-date information without spending a minute on manual data transfer. Actually, Coupler.io will save you up to 60% of your time – this is quite a significant improvement in your reporting and analytics. 

In addition to that, I have to mention that Coupler.io is not limited to Excel integrations. You can load data from business apps to Excel, or connect your Excel files to other destinations like Google Sheets, BigQuery, or Looker Studio. You should definitely try it! Moreover, you can do this for free within 14 days, and no credit card is required.

How to use IMPORTRANGE equivalent for Excel desktop

I promised to share the details of using Workbook Links in Excel. Let’s start with how this works for desktop Excel.

Open both a source and a destination Excel spreadsheet. Select a range of cells in the source spreadsheet and copy them: you can use either Ctrl+C or right-click => Copy.

4-source-spreadsheet-copy-range

Go to your destination spreadsheet, select either one cell to import the entire range or a range of cells to only populate the selected cells. Then right-click and select Paste Link.

5-cells-selected

You can expand the imported range by simply dragging the cross as follows:

7-drag-imported-range

Now if you change some values of the imported range in the source file, the values in the destination spreadsheet will be updated as well. 

8-change-update-source-destination

This method works well if your source and destination spreadsheets are open. When you need to import a range from an Excel workbook that is not open, use the formula bar method.

Excel IMPORTRANGE formula using the formula bar in desktop app

First of all, an important note: If your source workbook only has one worksheet with the same name as the workbook, the imported range reference will look as follows in the formula bar:

={workbook-name}.xlsx!{first-cell}

For example:

9-imported-range-formula-bar

However, if the names of the source workbook and worksheets differ, or the workbook has multiple worksheets, the imported range reference will look as follows in the formula bar:

=[{workbook-name}.xlsx]{worksheet-name}!{first-cell}

For example:

10-imported-range-formula-bar

When you close the source workbook, the imported range reference transforms in the following way:

11-imported-range-reference-closed-source

So, it’s attached with a path to the file on your device. In our case, the path is to the OneDrive storage folder. However, you can also encounter more regular paths like this one:

12-path-to-folder-device

This is the boilerplate you can use to import data from any Excel file on your device:

='{path-to-file}/[{workbook-name}.xlsx]{worksheet-name}'!{first-cell}

Note: you can use either slash or backslash depending on which type is used in the path to your file.

For example, we need to import data from Sheet1 of a source file named Source2.xlsx stored in the OneDrive folder. In the example above, our reference to this folder looked as follows:

https://d.docs.live.net/ec25d9990d879c55/Docs/Excel IMPORTRANGE/

But it’s okay if you take a regular path, which you can find in the file explorer like this:

C:\Users\Захар\OneDrive\Docs\Excel IMPORTRANGE
13-regular-path-to-onedrive

Insert this path string to the Excel IMPORTRANGE equivalent formula and add other variables: {workbook-name}, {worksheet-name}, and {first-cell}. Here is what we’ve got:

='C:\Users\Захар\OneDrive\Docs\Excel IMPORTRANGE\[Source2]Sheet1'!A1

Select a cell, insert this formula to the formula bar and press Ctrl+Shift+Enter.

14-insert-formula-cell

Now you can expand the range by dragging the cell vertically or horizontally.

15-expand-range

If you want to import an exact range at once, specify the range instead of the first cell in your formula. Then select the range in the destination sheet, insert the formula to the formula bar, and press Ctrl+Shift+Enter. Here is an example:

='C:\Users\Захар\OneDrive\Docs\Excel IMPORTRANGE\[Source2]Sheet1'!A1:C10
16-import-exact-range

Note: if you select the number of cells greater than the number of cell in the range to import, you’ll get #N/A for the cell beyond the range. 

Now you have an understanding of how IMPORTRANGE functionality on Microsoft Excel desktop works. However, Excel Online, as well as Excel 365, has some differences in use. Let’s explore how Workbook Links works.

Open the files you already know, Source.xlsx and Destination.xlsx, in Excel Online. Select and copy a range in the source file, go to the destination file, select a cell, right-click, and choose Link as a paste option.

17-paste-link-excel-online

The cells will be populated with the values from the source spreadsheet.

18-imported-range-excel-online

The main difference here is that the imported data won’t be refreshed automatically until you enable this. To do this, go to the Data menu => Workbook Links.

19-workbook-links

On the pop-up panel on the right, click the three dots and check the Refresh automatically checkbox for the linked workbook.

20-refresh-automatically

Now the imported range will be refreshed automatically every 5 minutes to display any change in the source range. 

We tried to import a range from a file stored in another folder, and here is the result:

21-workbook-links-error

We referenced a thread on the MS Office forum regarding the same issue, but we’ve still had no luck in resolving it. The only method that worked was to put the source and destination workbooks in the same folder. 

With this in mind, it’s good to have a failsafe alternative to link Excel to Excel. And there is one – Coupler.io.

IMPORTRANGE from Excel to Google Sheets and vice versa

The real IMPORTRANGE function only connects Google Sheets documents. IMPORTRANGE in Excel, Workbook Links, only connects Excel files. With Coupler.io, you can mix those functionalities and synchronize data between an Excel workbook and a Google spreadsheet. 

For example, to import a range from an Excel spreadsheet to Google Sheets, you need to choose Excel as a source app and select the file to get data from. As a destination app, choose Google Sheets, and select the file to load data to.

Excel IMPORTRANGE FAQs

The two most popular spreadsheet apps, Google Sheets and Excel, have a large influence on each other’s progress. IMPORTRANGE is a case in point because it’s a Google Sheets function that is not available in Excel. Nevertheless, many users keep using the term Excel IMPORTRANGE to name Workbook Links…or they just dream about a day when the Microsoft team releases this function. 🙂 Anyway, we collected a few questions associated with this and hope you’ll find it useful.

Excel IMPORTRANGE function for a whole column

Everything works as usual. To import an entire column from one workbook to another, just select the column (click the column index letter), copy it, and paste as link into the destination workbook.

30-importrange-column

How to use Excel IMPORTRANGE to get data with formatting?

IMPORTRANGE in Google Sheets, Workbook Links in Excel, as well as Coupler.io, allow you to only import raw data without formatting. 

Can I use Excel IMPORTRANGE with CONCATENATE?

In Google Sheets, you can nest IMPORTRANGE with CONCATENATE, QUERY, and many other functions. For example, check out our IMPORTRANGE+QUERY tutorial

However, in Excel, there is no IMPORTRANGE function, so it’s impossible to do. 

Excel IMPORTRANGE to import cells with text only

Unfortunately, Workbook Links does not let you set conditions for the ranges you import. However, you can use the Excel Power Query to do the job. For example, our data set that we need to import contains empty rows:

31-empty-rows

We can set a condition to ignore them for our import. 

  • Go to the Data menu => New Query => From File => From Workbook
32-new-query
  • Select your source workbook. In the Navigation window, select a sheet and click “Transform Data“. 
33-transform-data
  • This will bring you to the Power Query Editor, where you can add or remove columns/rows, change data types, split columns, group values by columns, and perform other sorts of data manipulation. What we need is to remove blank rows. There is a dedicated button for this on the ribbon menu: Remove Rows => Remove Blank Rows.
34-remove-blank-rows
  • All blank rows have been removed. Now you can Close & Load your data range. Click the respective button.
35-close-and-load
  • Welcome your data range into your sheet. Now the source sheet is connected to your destination sheet, and the queried data range will be refreshed automatically every 5 minutes, or you can do this manually. 
36-queried-data-range

Who’s going to win this race? Only Coupler.io is designed for both Excel and Google Sheets users and provides them with a scheduled data refresh, support for multiple sources and destinations, and many other cool features.

IMPORTRANGE is only available for Google Sheets users. It’s a good function, although it is known for its errors, which we’ve covered in Why IMPORTRANGE Is Not Working: Errors and Fixes.

Workbook Links, looks a bit plain and straightforward compared to its competitors. The keyword “excel importrange” is highly requested in Google search results, which means that Excel users need this function instead of WL. Ugly truth.

So, make your choice for importing data and good luck with it!

Streamline data analytics & reporting

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

  • Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
  • Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
  • Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
  • Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
  • Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

Start 14-day free trial
  • Zakhar Yung

    A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io