How to link Excel workbooks in SharePoint Online
Having consulted many organizations about SharePoint Online features for quite some time, I hear this question/request all the time, especially from finance folks. The question is whether or not you can link Excel documents in SharePoint Online and what happens to linked Excel documents after they are migrated from file shares to SharePoint Online. Well, you came to the right place. Grab some popcorn and your favorite alcoholic drink (trust me, you’ll need it) 😊 and let me tell you how to link Excel workbooks in SharePoint Online and nuances you must be aware of.
Migrating Linked Excel files from file shares to SharePoint Online
First, let’s address the elephant in the room. If you are moving linked Excel documents from on-premises file shares/network drives to SharePoint Online, in most cases, the existing links will become broken. I have done some testing by moving linked Excel files to SharePoint; in some cases, links continued to work; in some, they were broken. Whether the links continued to work depended on the relative location of the linked Excel files.
Scenario 1: Linked Excel files are located in the same folder/subfolder
When linked Excel files are located in the same folder and are moved as-is to SharePoint Online, the links continue to work after the folder is migrated to SharePoint.
Scenario 2: Excel file being linked to is located in a subfolder
When the Excel file being linked to is located in a subfolder, the links continue to work when the entire folder hierarchy is migrated to SharePoint Online.
Scenario 3: Excel file being linked to is located in a folder above the file being linked from
When the files from the previous scenario were swapped – the links stopped working upon migration. In other words, when the destination Excel file was located not in the subfolder underneath, but in a folder above the other file, the links would break upon migration of the folder hierarchy to SharePoint Online!
Warning and Error Messages in SharePoint Online when working with linked Excel documents
Below, I list the most common warning and error messages you will receive when working with linked Excel files in SharePoint Online. They differ slightly between the Excel Browser and the Excel Desktop versions. I document both.
Excel Browser
Warning Message when you try to open a linked Excel Document. Just click Enable Content to proceed.
Error Message when linked Excel documents break upon migration from file shares.
You will need to fix the linked cells manually.
Excel Desktop
Warning Message when you try to open a linked Excel Document. Just click Enable Content to proceed.
Warning Message when trying to open linked Excel documents in Excel desktop. Just click Yes to proceed.
Error Message when linked Excel documents break upon migration from file shares.
You will need to fix the linked cells manually.
Linking Excel files in SharePoint Online
If you are starting fresh, trying to link Excel documents that already reside in SharePoint Online, or just trying to fix the broken links after the migration, here is what you need to do to link Excel files.
- Open both Excel documents from the SharePoint document library (Browser or Desktop, it does not matter)
- Either link both via formula function (“=”) or simply copy contents of one cell.
- Use Paste Link functional in another Excel document. It is very important to use Paste Link and not regular Paste. Otherwise, it will not link.
- You will then see the other Excel file/cell linked in the formula field (it will display the whole URL/file path to the file in SharePoint Online in there)
What happens if you move linked files in SharePoint Online
You might wonder what will happen if you move the linked Excel documents in SharePoint Online. I am glad you asked. In most cases, when you move either the file being linked to or the file being linked from, the links will break! (hey, I told you to grab some alcohol at the beginning of this post, didn’t I?) This is especially true if you move to a completely different library or site. In this case, you would need to manually change the source and point to the new location of the file.
- After it fails to resolve the links, click Manage Workbook links (in Excel Browser experience), then three dots next to the file it failed to locate, then Change source.
- Next, locate the file you are trying to relink
- If the file does not appear under the Recent or Suggested sections, click Browse.
- You can then browse to the exact location of the file you are trying to relink to. In the example below, I moved the file to a completely different site (HR Team), so that is where I found it.
- At this point, the file link will be re-established!
OneDrive Sync and linked Excel files
You might also wonder what happens if you sync the linked Excel files locally to your Windows Explorer using the OneDrive Sync client. Well, good news in this case. The links will continue to work; even if you create new ones from your PC, they will continue to work in SharePoint Online. That is because the links in the formula field use the absolute SharePoint URL path and not the Windows Explorer C: Drive/folder hierarchy!!! Amen!
Share Article
