Workbook Link support coming to Excel for the web
Published Aug 18 2020 02:07 PM 90.8K Views
Microsoft

When collecting data from multiple workbooks, it’s often desirable to link directly to the data. Why? It provides a record of where the data was sourced and, if the data changes, you can easily refresh it. This has made Workbook Link support a highly requested feature for our web users. Today, we are excited to announce that Workbook Link support is beginning to roll out to Excel for the web.

 

This was previously only available in Excel for Windows and Excel for Mac, and is an important step on our journey to meeting one of our key goals: "Customers can use our web app for all their work and should never feel they need to fall back to the rich client". You can read more about our web investment strategy in Brian's recent post.

 

Creating a new Workbook Link

To create a new Workbook Link, follow these simple steps:

 

  • Open two workbooks in Excel for the web. They should be stored in either OneDrive or SharePoint.
  • In the source workbook, copy the range.
  • In the destination workbook, “paste links” via the right click menu or via paste special on the home tab.

The gif below provides a brief demonstration.

 

Creating Workbook LinksCreating Workbook Links

You can also explicitly reference the workbook using the following reference pattern. ='https://domain/folder/[workbookName.xlsx]sheetName'!CellOrRangeAddress

 

Opening a workbook that contains Workbook Links

When you open a workbook that contains Workbook Links, you will see a yellow bar notifying you that the workbook is referencing external data.

 

Data Connection WarningData Connection Warning

If you ignore or dismiss the bar, we will not update the links and instead keep the values from before. If you click the “Enable Content” button, Excel for the web will retrieve the values from all the linked workbooks.

 

Managing Workbook Links

To help you manage Workbook Links, we’ve added a new Workbook Links task pane. The task pane can be accessed by pressing the Workbook Links button on the Data tab or via the “Manage Workbook Links” button on the yellow bar above.

 

Workbook Links button on the Data tabWorkbook Links button on the Data tab

You can see the task pane below with its menus expanded below.

Workbook Links TaskpaneWorkbook Links Taskpane

The task pane lists all your linked workbooks and provides you with information on the status of each of the linked workbooks. If the link could not be updated, the status will explain the cause. So that you can quickly spot issues, any workbook that cannot be updated will be bubbled to the top of the list. 


At a global level you can take the following actions:

Refresh all: This triggers an immediate refresh of all linked workbooks.

Refresh links automatically: When enabled, this causes Excel to periodically check for updated values while you are in the workbook.

Break all links: This removes all the Workbook Links by replacing those formulas with their current values.

 

At a workbook level you can take the following actions:

Refresh workbook: This triggers an immediate refresh of that linked workbook.

Open workbook: This opens the linked workbook in another tab.

Break links: This removes links to that workbook by replacing those formulas with their values.

Find next link: This selects the next occurrence of a link to that linked workbook. (Great for quickly finding phantom links)

 

Workbook Links vs External Links

Direct links to external workbooks have historically been referred to simply as “External Links”. As we continue to add more external data sources to Excel, the term “External Links” has become ambiguous. To improve clarity going forward, we will use the term “Workbook Links” instead.

 

Learn More

You can find additional information about Workbook Links on our help page.

 

43 Comments

Good addition to Excel for Web

Copper Contributor

Nice.

It is also important to be able to change source. Without that it's still limited. For now you will have to change all related cells manually.

 

@JoeMcDaid , so far didn't win the lottery to check functionality. Do I understand correctly these are static links, i.e. can't be generated / modified by formulas like INDIRECT()? Hope it could be done with Office scripts. And how it works if one of workbooks is opened by Excel Desktop app?

Copper Contributor

Is it similar to that of import range in Google spreadsheet.

Copper Contributor

Will this also be the solution for migrating Excel Workbooks with VLOOKUP to SharePoint?. As far as I understood, it allows to extract and update data stored in other SPO-Excel-Workobooks as long as the user has the permissions to open. Since you can generate a link it should be useable with formulars.

Copper Contributor

When this feature will be  officially released and available to all excel for web users?

Copper Contributor

Hi @JoeMcDaid - I desperately need this feature but can't find it anywhere.  Did they roll back the release?  Did something go wrong or do I simply not have access to it?  I'm using Excel for Web under the Basic subscription.

Copper Contributor

Hi @hobbitshire,

You can have a look on the M365 roadmap. -> https://www.microsoft.com/de-de/microsoft-365/roadmap?filters=&searchterms=workbook%2Clink

There is a entry for this feature which states it's currently in rollout :) Roadmap ID 71476

Since the entry was last updated in October maybe take the date with a grain of salt.

 

Kind regards

Niklas

Copper Contributor

@Niklas MurrThanks for the link.  I guess the key phrase in the above article is "beginning to roll out".

Copper Contributor

Microsoft, please enable this functionality to Sharepoint, Excel Web! We've been waiting years for it. In the meantime, we continue to send piles of money to Google Docs because they've supported this for years. Thank you.

Copper Contributor

@Niklas MurrQuick update - the workbook link feature is now live in Excel for Web and appears to be working well (at least it is for me here in the UK)

@hobbitshire , you are lucky, still don't have it even with UK locale

Copper Contributor

@Sergei BaklanI knew it was too good to be true.  I just went to do some work in the workbooks and lo and behold this feature has now been disabled.  I mean, it's not like anyone's work depends on it or anything ..... oh, but wait ..... it really does!  Anyway, all I have now are the memories of what could have been ..... ..... :facepalm: :lol:

Copper Contributor

is this feature available only for some microsoft365 subscriptions? i have 2 tenant..the first one has a office365E3 subscription and the feature is available...the second one has Microsoft Business Standard subscription and the feature isn't available. Doing something wrong?

@flysoft_re , I guess for Excel Online the lottery is more selects tenants, not subscriptions.

Copper Contributor

When will this workbook link feature be available? I really need it.

Copper Contributor

This is a great article @JoeMcDaid., I have been waiting for this functionality for a while now. Is there any news when this will be rolled out to all tenants?  

@NeilShorrter , good question. GA is scheduled on Oct 2020 Microsoft 365 Roadmap | Microsoft 365

Copper Contributor

I don't see it on my Excel for web using Safari or Firefox on MacOS. 

Copper Contributor

Todavia no nos aparece a nosotros en un tenant de Argentina, alguien sabe algo? no hay información.

Copper Contributor

Hi all, does anyone knows if the Workbook Link Support in Excel for the Web is live? I can't see any option available on mine to use, and really in need of that. Any help or clarofication would be really appreciated. Thanks

@CZadra , there are no such option for Excel Online, you have functionality or you don't have it. That's only to wait.

Copper Contributor

@CZadra  , hice un ticket a ms por este tema, me dijeron que para recibir este cambio lo más rapido posible ponga en el tenant desde setup->org settings->profile (Tab) - targeted release for all users (middle option) , esta opción hará q te lleguen los cambios rápidamente, teóricamente en 5 días debería llegar.. eso me dijeron, por ahora han pasado 24 hs y nada, pero se supone q eso lo acelera al máximo, no sirve ponerlo por usuarios , tiene q ser para todos si o si, eso me explico el ingeniero.

 

Otro comentario, esta opción no tiene nada q ver con la versión de Office q uses , excel web es independiente de la versión de escritorio.

 

 

 

Copper Contributor

Not quite what we're looking for.

So I get the link detail only when I copy from a (range of) cells from an on line sheet and perform a special (link) paste into another on line sheet.

The result is a function in the target cell that has a URL which points precisely to the source range of cells.

 

So far so good.

 

But: I cannot paste the same URL into a link I am setting up in the laptop Apps: Word, Excel, etc.

 

The Use Case we're needing to have supported is this:

I have a sharepoint located (on line) sheet listing data. The data consists of records, each record having some kind of significance.

 

Now I am writing an e-mail or Word docuument. Or I am creating a PPT. And I want to say: "... for this topic see such-and-such in the Record Log on Sharepoint."

And now I want the words 'such-and-such' to be the text of a web link with the link being the URL pointing to the (range of) cells in the sharepoint sheet.

 

The functionality developed so far, does not support this Use Case and for me, it is largely useless.

Copper Contributor

BUT how is this different to "Edit Links" already available?

And does it enable the linking of references to spreadsheets saved in OneDrive?  That's what this al lstarted with. Links between spreadsheets on desktop Office 365 work fine. But put those same spreadsheets on OneDrive (in the same folder structure) and the links fail.

Has this been fixed?

Copper Contributor

Not surprisingly, "External Data Connections have been disabled" by default. Is there any way to programmatically enable content so that I can update the links using ExcelScript? 

Copper Contributor

Works good for me:

 

Set up a book with some data

Copied the data across to the new book, but pasted as linked value

Enabled workbook connections

Changed some values in the source book

Refreshed Manually and noted changes in target workbook

Viewed the datasource and enabled it automatically updating

Changed more data in the source book

After some time, noted the changes in the target book.

Then:

Copied the link into a new cell, edited the link to bring in a range of data rather than a single value, edited the formula further into a SUM() and correct values all returned.

 

Maybe a few annoyances linked the initial disabling of the links and the setting to manual updates in the first instance.  but these are only changes which need to be made once to a workbook and likely done for security reasons.

 

To me it looks like this will cater for most people needs (albeit about two years later than I would have hoped!).

 

Then set it back to automatically update

 

Brass Contributor

Hi @Elgin Green et al, The macro recorder did not capture the refresh links event. However, that does not mean there is no code method for this. Also:

If you write a script that uses .setFormulaLocal() function it performs a cell refresh. Here is my code:

function main(workbook: ExcelScript.Workbook) {
  let Sheet1 = workbook.getWorksheet('Sheet1');
  // Set range C2 on selectedSheet
  Sheet1.getRange("C2").setFormulaLocal("='https://analysisie.sharepoint.com/sites/PowerFlowTesting/Shared Documents/General/Sudhi Murthy/[Events.xlsx]Keys'!C2")
  let cellC2Object = Sheet1.getRange('C2')
   cellC2Object.calculate
}

2. Alternatively, there is a "URL interface" methodology within Office Scripts which might be a way to create these links and then access a .refresh() method. 

 

3. Yet another, alternative, solution is to use Power Automate to obtain your source data (put it into an array) and, then, either use an Office Script to populate a range, in your workbook, or use the Flow's Excel Connector :"Update a row".  

Copper Contributor

How can we use formula using multiple workbook (like "Importrange" function in Google Sheet) here?

Copper Contributor
  • It appears table references are not supported (yet?) in the Workbook links:
    • I have XLOOKUPS referencing a table in one workbook, and returns the expected values in the Desktop version, but I only get "#Ref!" in Excel Online.
    • Yes, the Workbook Links are set up correctly, because my XLOOKUPs work if I don't use tables...
  • Even when not using tables, the updates to values returned by XLOOKUP can be very slow and seemingly erratic. Refreshing the link, and doing a manual "Calculate Workbook" operation do not force the update in Excel Online. It does it whenever it feels like it, sometimes minutes later...

Is there any maximum limit to the number of workbooks that can be linked? ImportRange used by some customers have between 30-40 spreadsheets linked. Thank you!

Copper Contributor

Is it possible to make this link dynamic? I have tried with Concat function (to gather cell value and replace in the link) with no success. any ideas?

Copper Contributor

for me it works with new workbooks, not older ones.

been trying to get this to work for the last few weeks on a couple of old workbooks, hit the enable external links pop up on both and still nothing.

just says:

UNABLE TO REFRESH We couldn't get updated values from a linked workbook.

Made 2 brand new ones to test the feature and was able to get it to work fine

 

not sure if i need to do something to the old ones to get links, or do i just have to remake them from scratch?

Copper Contributor

This function is really valuable, however I the selection paste special workbook link is not available unless I have something copied in the target workbook. And then that is what is copied in to the target cell - ie a copy within one workbook.

Is there anyone who has gotten this to work???

Brass Contributor

Hi @Anders_Kjo , the way to use this is to either manually type the formula or copy the cells (range) from the source workbook and "past link".

As far as I am aware this feature has now been rolled out across the world, so it should be working for you. If not raise a support request with Microsoft. 

Brass Contributor

What would be really useful, is if this feature could work with non-Excel formatted files!! For example csv. Csv files do open in Excel Online and we can see the data in individual cells, etc., yet when it comes to copy-pasting links it does not work.  

Copper Contributor

Checking if anyone figured out how to automatically enable the "external data connection has been disabled"?

Brass Contributor

@Rodito_Buan 

You can try to use this example VBA to refresh your "external data connection" :

Sub UpdatingExternalCons()
' UpdatingExternalCons Macro
ActiveWorkbook.Connections("Query - SampleData").Refresh
End Sub

You should, then, insert that VBA into the Power Automate triggered solution as I have demoed here:  Run VBA on SharePoint Online for Excel files - YouTube

 

Copper Contributor

I use workbook links in my other ms teams / excel on the web spreadsheets but in this organization Im getting the following error.

 

JCOLLINS0331_0-1656516444119.png

I have tried everything i can think of. help?

Brass Contributor

Hi @JCOLLINS0331, you mention "organization". So, this means you got the Excel linking functionality to work in your own personal organization and but can't get them to work in your current organization?  If so, you could consider asking your colleagues, in that organization, if they are having the same issue. This would point to , perhaps an Office 365 Admin setting that needs to be turned on. In Teams, restrictions can be applied to which SharePoint sites/Groups and content you get access to. So, opening an Excel workbook in a Teams tab does not necessarily give you access (via the linking) to other Excel workbooks. Though, that said, the error message, above, is perhaps an HTTP/Server caused error. That is, it is perhaps an intermittent error. Refreshing/quitting Teams can resolve that type of error.

Additional remediation, possible, is to recreate the links in the workbook or recreate a new worksheet in that workbook with the links or create a new workbook with the links in it.

You could, also, try reporting the issue in a support request to Microsoft. Give them that Error id. Use your own personal Microsoft work account to report the issue if you don't have access to the support reporting link in your organizational tenant.
Hope that helps.    

Copper Contributor

Is it also possible to link two files saved in Sharepoint automatically with a macro (VAB)? I tried to sort out the appropriate path. But I could not succeed. When I use an example path I generated manually by copying the cells as explained above, it does not work.

Thanks for help!

Copper Contributor

I have a workbook that I am working in that has numerous sheets within in.   One of those sheets is a summary sheet of all the jobs (individual work sheets).   I want to link the job number on the summary page to the job worksheet so that I don't have to scroll through all the sheets to find that specific job sheet.   How do I do this?

Brass Contributor

RE: 

" I have a workbook that I am working in that has numerous sheets within in.   One of those sheets is a summary sheet of all the jobs (individual work sheets).   I want to link the job number on the summary page to the job worksheet so that I don't have to scroll through all the sheets to find that specific job sheet.   How do I do this?:

 

My workaround is to name the individual work sheet with "job number" so that they are visible without additional linking or XL4 macros to list sheets. Hope this helps.

 

Version history
Last update:
‎Aug 18 2020 02:07 PM
Updated by: