Link Data between Different Excel Online Files
Illustrate how to link data between Excel Files in OneDrive
Why bother with linking different Excel workbooks?
Nowdays the working environment requires us to process real-time data on a daily basis.
As a result, we often encounter a situation in which the IT system in the company will dynamically update the raw data every day and synchronize these updated data to an Excel workbook containing all sorts of relevant data.
To illustrate this situation, let’s consider the following setting.
We have an Excel file “SourceData.xlsx” containing the latest Gold Price per Ounce, which is updated every day automatically.
Subsequently, this Gold Price per Ounce will be used to perform relevant calculations in another Excel file “Portfolio.xlsx.”
Conceptually, we wanna create a cell in “Portfolio.xlsx” which is dynamically linked to the updated data in the Excel file “SourceData.xlsx.” This goal can be simplified as below:
“SourceData.xlsx” → → → “Portfolio.xlsx”
How to create links between Excel files online?
Creating links between Excel files online requires a special arrangement. If you have worked with linking different Excel files in the Excel desktop application,
then be AWARE that Excel online has some extra complexity in setting up the links.
Prerequisite Before Linking Excel Files Online
The most crucial step (as in March 23, 2024) before linking Excel Files Online is to ensure that those files are located under the same folder in OneDrive.
In the context of our preceding example, we MUST make sure that both “SourceData.xlsx” and “Portfolio.xlsx” are residing in the same folder just like the next screenshot.
Linking cells between Excel files online
Once those Excel files are sitting under the same folder in OneDrive, we are now ready to link cells between Excel files online.
Assume that we intend to link the cell C2 in Sheet1 of “SourceData.xlsx” to the cell G2 in the worksheet FinancialData of “Portfolio.xlsx”.
Step 1: Copy the source cell.
Move the active cell to cell C2 in Sheet1 of “SourceData.xlsx” and Copy this cell (use keyboard shortcut CTRL + C)
Step 2: Paste the Link to the Target Cell
Switch the active Tab in browser to the worksheet FinancialData of “Portfolio.xlsx”.
Move the active cell to G2 and click the “little arrow” under the Paste button under the “Home” menu in the Ribbon and click on “Link to source” in the dropdown menu as the following image.
Step 3: Configure the Links
After clicking the item “Link to source”, Excel online will immediately pop a warning message at the top of the worksheet with two buttons.
Just push the button of “Trust Workbook Links” as the following screen.
After that, the Workbook Links panel will appear at the right hand side.
Click on the Settings tab as shown below.
After switching to the Settings tab, Tick On two settings as the following screenshot.
Always trust workbook links
Always refresh links
This leads to the following screen.
Step 4: Close the Panel of Workbook Links
After closing the Workbook Links Panel, we can see the formula in the cell G2 has become a link to the cell C2 in Sheet1 of “SourceData.xlsx”
Whew, so many steps to go through, right?
Yeah, we feel the same.
Just remember the Excel files to be linked MUST reside under the same folder. Otherwise, the link may not work correctly.
Hope Microsoft will resolve this limitation in the future.