Cara menggunakan workbook links excel online

Working with Excel, a day will come when you’ll need to reference data from one workbook in another one. It’s a common use case and pretty easy to pull off. Join us as we explain how to link two Excel files and discuss many possible scenarios.

Table of Contents

How to link Excel files – what are the available options?

Just as there are many different versions of Excel, the ways to link data also differ. It’s a lot easier if you share your Excel files in OneDrive rather than locally but we’ll discuss both scenarios.

Choosing how to link data depends also on the sheer volume of what you wish to link. If we’re talking here about particular cells or a column from your workbook, the default methods will do just fine. If you’re after linking entire Excel files, using tools such as Coupler.io with its Excel integrations may prove to be more efficient. But we’ll get to that!

How to link local files in Excel?

You can link two or more Excel files stored on your hard drive. When the data changes in a Source file, the change will be quickly reflected in the Destination file.

The drawback of this approach is that it will only work on your local machine. Even if you share both files with another user, the link will cease to exist and they’ll be forced to re-add it. What’s more, the data will be only updated if both files are open at the same time.

So if you have a choice, it’s better to add both files to your OneDrive. If they’re already in there, you may as well jump to the How to link between Cloud-based Excel files section.

To link 2 Excel files stored locally, you have two options:

• Type in a formula referencing the exact location in a Source file
• Copy the desired cells and paste them as a link

How to link between files in desktop Excel?

To reference a single cell in another local file, you’ll use the following formula:

=[SourceWorkbook.xlsx]Sheet1!$A$1

Replace SourceWorkbook.xlsx with the name of the file stored on your machine. Then, point to an exact sheet and a cell. A reference to a range of cells could like this:

=[MyFile.xlsx]Sheet1!A1:C8

Press ENTER to save the formula and pull the data. If you’re on an older version of Excel, you may need to press CTRL+SHIFT+ENTER instead.

When you close the Source file, the formulas will change to include the entire path of the file – for example:

='C:\[MyFile.xlsx]Sheet1'!A1:C8

As an alternative, you can: 

• Open a Source file, select the desired cells, and copy them.

• Head back to the Destination file, right-click on a desired cell or cells and choose to paste as a link.

• Here is the result:

Note that if the Source file is closed, and you reference it with a formula, no data will be pulled until you open a file. 

How to link between cloud-based files in Excel?

When you wish to link Excel Online files or use those stored in OneDrive, things become easier. You can freely share files among coworkers and any interlinking won’t be affected. Links in files also refresh in near real time, giving you peace of mind that you’re working with the latest data.

The feature that enables it is called Workbook Links. We’ll explain how it works in the following chapters.

Workbook Links are suitable for individual cells or ranges of them. You may also link individual columns but the more data is involved, the slower your calculations will be. If you’re going to be linking entire worksheets or workbooks, it’s far better to focus on importing, rather than linking them. This is best done with dedicated tools. 

There is more on that in the How to link a wide range of cells to Excel or another service chapter.

How to link two Excel files?

Let’s start with a most basic use case – you’re running some operations in your Excel workbook. In one of the fields, you wish to use the value(s) from another workbook and have it update automatically.

The flow is simple:

• In workbook 1 (source), highlight the data you want to link and copy it.
• In workbook 2 (destination), right-click on the first row and select the Link icon.

The latest data will be imported. A yellow bar will, however, appear now as well as every time you open a destination workbook.

To enable the data sync, select Enable Content. 

For some more advanced settings, you can choose the Manage Workbook Links button to look up the list of connected workbooks and a status for each – most likely it will be Connection Blocked. 

To sync, press the Enable Content button from the yellow bar.

If any errors occur, you’ll see them in the menu to the right. For each of the connected files, you can press the Refresh button to manually pull the latest data. You can also use the button above to refresh data for all your links.

Of course, the whole idea of creating links between workbooks is not to keep refreshing the data manually. Once the data has been refreshed, an option to set automatic updates will be enabled.

If you tick Refresh automatically, the data will start to refresh periodically. 

How to link a wide range of cells to Excel or another service?

The more data you link, the more computing your Excel needs to perform to pull the data and refresh it. It’s not an issue if you have a few or a few dozens of links spread across files. 

However, if you wish to regularly pull thousands of cells into your workbooks, it will significantly slow down your workbooks. It may delay the data refresh and may leave you wondering whether the data has already been refreshed or not.

To avoid that, for larger operations it’s better to use tools dedicated to importing data such as Coupler.io. With Coupler.io, you can pull the desired ranges of cells directly into another Excel workbook or worksheet. You can then refresh the data automatically at a chosen schedule.

If you wish to, you can also import the Excel data to other services, such as Google Sheets or Google BigQuery, or bring it to Excel from Airtable, Pipedrive, Hubspot, and many others.

To get started with Coupler.io, create an account, log in, and click the Add an importer button.

From the list of source applications, choose Excel.

Next, click the Connect button. Log in with your Microsoft account and allow for Coupler.io to connect.

Once connected, you’ll need to choose the workbook from your OneDrive that we’ll be importing from. Also select the worksheet in this file.

Although it’s optional, most often you’ll want to specify the range of cells to import. If you don’t, all data from a given sheet will be fetched.

You may use the standard Excel formatting and pull, for example, cells C1:D8. You may also pull an entire column by typing, for example, C1:C.

Jumping to the Destination settings, choose where to import the data to. We’ll go with Excel as we just want to move the data from one workbook to another but there are other options available too.

If you’re importing from Excel to Excel, there’s no need to connect your account again, unless you’re importing to someone else’s account. Select it, and specify the exact destination.

Finally, you can create a schedule for when the data should be imported. Choose what works best for you and run the importer.

Give it a little while to load, and then open the destination worksheet to see the results.

How to link Excel files and sync in real-time?

One of the advantages of using Excel files stored on OneDrive is their ability to sync data between one another. Microsoft advertises it as real-time sync but after some tests, we would call it a near real time. 

If you’re used to the refresh rate of Google Sheets, for example, you may be disappointed. However, in most situations, a slight delay won’t cause any trouble.

To link files in Excel, follow the steps we outlined in the How to link Excel files chapter.

When data is changed in the destination file, most likely you won’t see an update visible right away in the source file. If you do nothing about it and just move on to the next task, you should see a refreshed number in a cell in a few minutes’ time. It will then continue refreshing at regular intervals.

If you want to speed things up, you can run an instant refresh by clicking Data -> Workbook Links in the menu and then the icon to refresh the data from a particular workbook.

This will often work, but only if the data in the source workbook has already been saved. Once again, it doesn’t happen instantly but only at regular (quite frequent) intervals. 

If you’re anxious to have the data refreshed presently, you may consider refreshing the source file after making a change. This will prompt an automatic save. After that, manually force a data refresh in the destination file and it will fetch the latest saved data.

As a reminder, if you use Coupler.io to import data from one Excel workbook to another, you decide on the refresh schedule. For some, morning sync from Monday to Friday will do just fine. Others will prefer more frequent syncs – with Coupler.io you can even do it every 15 minutes. 

FAQ: How to link Excel files

Let’s now discuss some specific use cases for how to link files in Excel. The tips below apply for files stored in OneDrive. If you only use Excel locally, jump back to the How to link local files in Excel section.

How to link cells in different Excel files?

For linking individual cells across files, the procedure is very much the same as we discussed earlier:

• Highlight the cell you want to reuse elsewhere and copy it.
• Right-click on the desired destination and select the Link icon from the Paste Options section.

If you’re importing to the same workbook as before, you won’t need to Enable Content again. If you reloaded it in the meantime, or are just linking the data to a new workbook, choose Manage Workbook Links and then Enable Content from the same bar.

Note that if you link multiple sets of cells or data ranges from the same workbook serving as a Source, they will all appear as a single position on your list of Workbook Links. 

In the example below, you can see the data we imported from our sample Shopify store using Coupler.io. 

We have a range of prices to the left linked from the respective workbook. Below there’s also a name of one of our products linked from another place in the same workbook. To the right, we can refresh data for all linked fields or, for example, enable automatic data refreshes.

How to link two Excel files without opening the source file?

You can link files in Excel without actually opening the source file. Of course, you’ll need to know the exact location of the cell or range of cells you wish to link. What’s more, to link Excel Online files or anything else stored on your OneDrive, you’ll need to fetch your unique ID.

To do so, set up a link in the traditional way we described above. Then, click on any linked cell in the Destination file and check its formula. It will look something like this:

='https://d.docs.live.net/18644c626caae38c/[myworkbook1.xlsx]Sheet1'!$C$2

The ID will follow right after the live.net link:

To insert a link from any given workbook, copy the formula with your ID and swap the file name and the cell range with the right values. Press ENTER and the latest data will be fetched.

How to link Excel columns between files?

Choosing a range of cells limits you to only the values currently present in the Source workbook. If anything new appears, it won’t be linked and, as such, won’t appear in the Destination workbook.

The solution is often to link an entire column and reference it in another file. To do so, click on any column in the Source file and copy it.

Then, select the first row of a column you want to add a link to and choose the Link icon. All the rows from the chosen column will be imported.

Rather than click, you can also enter the formulas directly. To link an entire column, it’s best to link to the first cell and then stretch the formula to the other rows.

When you insert the formula for the first row, be sure to remove the second $ (dollar) sign pointing to the specific cell. So, instead of the reference:

(...)Sheet1'!$C$2

Make it:

(...)Sheet1'!$C2

How to link fields between multiple files in Excel?

Advanced calculations may require referencing data from multiple spreadsheets at the same time. In the same way, the calculated data can then be referenced in other workbooks, creating a complex network of interconnected links.

As you recall from the earlier chapters, the formulas for linking cells between Excel Online files look somewhat like this:

='https://d.docs.live.net/18373e637ca3e48c/[Shopify Products - prices.xlsx]Sheet1'!$C$8

For the purpose of running any calculations or just adjusting the formulas as we go, the link is far too complex. It’s much better to link the desired fields into the destination workbook and then reference them from another worksheet in the same workbook.

To do so, create a separate worksheet in your destination file where you’ll link all the external data. Name it accordingly – for example, “Sources”. Link the desired data by copying it from the source file and then pasting it as links (just as we did before).

Then, jump to the worksheet in the destination file. To reference the data from another sheet, use the following pattern:

Sheet_name!$A$1

For example, for calculating the average value of a range of cells present now in another worksheet, we would use:

=AVERAGE(Sources!B2:B12)

You can also type in the “=” sign (plus optionally a function name) and then jump to another worksheet and highlight the arguments. Press ENTER and the formula will be resolved.

In the standard version of Excel, the approach is pretty much the same, just with some small changes in the syntax of the links, as we mentioned earlier.

How to link Excel files – summing up

Linking Excel files can save you plenty of time and help you automate many dull processes. 

It can also make things harder if you begin to link from one file to another, then to another, and another. The more interconnected workbooks become, the more complex it will be to troubleshoot the entire flow. 

Find the right balance and use the right methods for linking your data.

When moving individual cells or ranges of them, Workbook Links work perfectly and are very easy to set up.

For moving large sets of data between workbooks or linking entire files, it’s a lot better to use tools such as Coupler.io. You’ll be able to set up your own schedule for imports, and all data transfers will happen outside of Excel. As a result, no Excel resources will be used, and you’ll be able to work much more smoothly while the information is synced in the background.

Bagaimana cara link di excel?

Bagaimana cara membuat link spreadsheet?

Apa itu Hyperlink di Excel?

Bagaimana cara mengakses Google Spreadsheet?