Workbook links must be maintained and updated to function properly. You will need to refresh them to get the latest values and locate them in your workbook if they break.
WindowsWeb
Refresh and Update workbook links
Here's how to ensure that you are retrieving the latest values from your source workbooks.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
SelectRefresh all at the top of the pane.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select the workbook from the list and selectRefresh.
Note:Desktop workbooks don't have the Refresh Automatically command because this operation normally occurs with automatic recalculation when opening the workbook. For more information, seeChange formula recalculation, iteration, or precision in Excel.
You can decide whether the links in this workbook are refreshed when opening the file according to the user's setting, not refreshed when opening the file, or refreshed automatically without prompting the user.
This option affects all users of the workbook. If you choose not to refresh links, and not to prompt, users of the workbook will not know that the data is out of date.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select and expandRefresh settingsat the top right corner of the pane and select one of the following:
-
Ask to refreshAsks the user to refresh or not when the workbook opens.
-
Always refreshAutomatically refreshes all links when the workbook opens.
-
Don't refreshDoesn't refresh on open and doesn't ask the user.
-
When you open a destination workbook, and the source workbook is not open, you may be alerted by the Trust Bar to update the links.
You can control whether the Trust Bar alerts you, and whether to update all links when the alert does not appear. You can also choose to update only certain linksif the workbook contains more than one.
-
Close all source workbooks. If one source workbook is open, and others are closed, the updates will not be uniform.
-
Open the destination workbook.
-
In the Unsafe links warningdialog, select Update. This updates all the data links in the workbook.
If you only want to update specific links, select Don't Update, and see Change the source workbookbelow.
If you get a Security Warning bar when you first open a workbook, it means the workbook is not yet trusted. To trust the workbook:
-
Select Enable Content.This makes the workbook trusted for this session, and updates the links.
-
If you get a dialog asking to make it a trusted document, select Yes to avoid the Security Warning bar in the future.If you select No, you'll get the Security Warning bar the next time you open the workbook.
Note:Depending on your settings, you may also see the Unsafe links warning dialog.
Maintain workbook links
Sometimes, you need to change the source workbook, a workbook linkbreaks, or you may no longer need the workbook link.
You may want to examine and inspect the source workbook first, before making significant changes.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select More Commands (...) next to therequired workbookand then selectOpen workbook.
To change the source workbook for all references within the destination workbook:
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select More Commands (...) next to the required workbookand then selectChange source.
-
In the Change Source dialog box, underRecent, select the new source workbook. Or select Browse, and from thedialog box,open the new source file.
To change the source workbook for a particular reference within the destination workbook:
-
Find the workbook that you want to use as the new source for the external reference and note its location.
-
In the destination workbook, select the cell with the external reference that you want to change.
-
In the formula bar, look for a reference to another workbook, such as C:\Reports\[Budget.xlsx], and replace that reference with the location of the new source workbook.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select More Commands (...) next to the data workbook with links you want to fix, then selectChange source.
-
In the Change Source dialog box, browse to the location of the file containing the linked data.
-
Select the new source file.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select More Commands (...) next to the required workbookand then selectBreak links.
Important:
-
When you break a link to the source workbook of an workbook link, all formulas that use the value in the source workbook are converted to their current values. For example, if you break the link to the workbook link =SUM([Budget.xls]Annual!C10:C25), the SUM formula is replaced by the calculated value—whatever that may be. Also, because this action cannot be undone, you may want to save a version of the destination workbook as a backup.
-
If you use an external data range, a parameter inthe query may be using data from another workbook. You may want to check for and remove any of these type of links.
-
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
SelectBreak all at the top of the pane.
Findworkbook links
There is no automatic way to find all workbook links in a workbook.You need to look differently in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series.
There may be several workbook links in a workbook. Here's how to locate the one you want.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select the workbook from the list and selectFind next.
-
Press Ctrl+F to launch the Find and Replace dialog.
-
Click Options.
-
In the Find what box, enter .xl.
-
In the Within box, click Workbook.
-
In the Look in box, click Formulas.
-
Click Find All.
-
In the list box that is displayed, look in the Formula column for formulas that contain .xl. In this case, Excel found multiple instances of Budget Master.xlsx.
-
To select the cell with a workbook link, click the cell address link for that row in the list box.
Tip:Click any column header to sort the column, and group all of the workbook links together.
-
On the Formulas tab, in the Defined Names group, click Name Manager.
-
Check each entry in the list and look in the Refers To column for workbook links. Workbook links contain a reference to another workbook, such as [Budget.xlsx].
Tips:
-
Click any column header to sort the column, and group all of the workbook links together.
-
You can group multiple items with the Shift or Ctrl keys and Left-click if you want to delete multiple items at once.
-
-
Press Ctrl+G, the shortcut for the Go To dialog, then click Special > Objects > OK. This will select all objects on the active worksheet.
-
Press the Tab key to move between each of the selected objects, and then look in the formula bar for a reference to another workbook, such as [Budget.xlsx].
-
Click the chart title on the chart that you want to check.
-
In the formula bar , look for a reference to another workbook, such as [Budget.xls].
-
Select the chart that you want to check.
-
On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series that you want to check.
-
In the formula bar , look for a reference to another workbook, such as [Budget.xls] in the SERIES function.
Use the legacy Edit Links command
The legacyEdit Links command is replaced by the Workbook Links command. However, you can get the old Edit Links command back by adding the legacy Edit Links command to your custom group in the ribbon. You can't add the Edit Linkscommand to the Queries and Connections group.
Using Customize the Ribbon,you need to create your custom group and only then you can add Edit Linksto your custom group in ribbon as follows:
-
Right-click the ribbon and select Customize the Ribbon.
-
In theChoose commands fromdrop-down, select All Commands.
-
Select Edit Links and select the Data tab.
-
Click New Group and then select Add. Edit Links is added to your customized group.
You can also add Edit Links to the Quick Access Toolbar. For more information, see Customize the Quick Access Toolbar.The Edit Links command is dimmed if your workbook does not contain workbook links.
When you open the Edit Links dialog box, you're presented with several options for dealing with existing links.You can select individual workbooks with Ctrl+click, or all of them with Ctrl+A.
Update Values
This will update all selected workbooks.
Change Source
This option is useful when you want to point existing links to another source. For instance, you might have a prior year workbook, and need to point to a new workbook when a new year starts. Selecting Change source will launch a file explorer dialog box, where you can browse to the new source workbook. If the workbook has more than one worksheet, you will be prompted to specify which one to link to - just click the sheet you want and click OK.
Note:It is possible to point a workbook back to itself by selecting it from the Change source dialog. This will sever any formula links to the originally linked source workbook.
Open Source
This will open the source workbook.
Break Link
Important:When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xlsx]Annual!C10:C25) would be converted to the sum of the values in the source workbook. Because this action cannot be undone, you may want to save a version of the file first.
-
In the Edit Links dialog box, in the Source list, click the link that you want to break.
You can select individual workbooks with Ctrl+click, or all of them with Ctrl+A.
-
Click Break Link.
-
If the link used a defined name,you may also want to delete the name. To delete a name:
-
On the Formulas tab, in the Defined Names group, click Name Manager.
-
In the Name column, click the name that you want to delete, and then click Delete.
Note:If you use an external data range, a parameter of a query may also use data from another workbook. You may want to check for and remove any of these types of links.
-
Check Status
This simply displays a notification in the Edit Links pane whether a linked workbook is still a valid source. It should display OK, but if it doesn't then you'll need to check on the source workbook. In many cases, a source workbook may have been moved or deleted, cutting the link. If the workbook still exists, you can use the Change Source option to relink the workbook.
Frequently Asked Questions
Previous versions of Excel hide the links in names that are not currently in use in the workbook.The links have always existed and are nolonger hidden.To remove the link, selectBreak Links for the selected workbookin the Workbook Links pane.
Yes. When you replace a formula with its value, Excel permanently removes the formula. If you accidentally replace a formula with a value and you want to restore the formula, select Home and chooseUndoor press Ctrl+Z immediately after you enter or paste the value.
-
Press Ctrl+C to copy the cell with the formula.
-
Press Alt+E+S+V to paste the formula as its value, or select Home > Clipboard > Paste > Paste Values.
SelectDon't Updatein the Unsafe links warningdialog. Excel cannot update from a source that is not connected. For example, the source may be on a network, and you may not be connected to that network.
SelectDon't Updatein the Unsafe links warningdialog.
SelectDon't Updatein the Unsafe links warningdialog. If the data does not need to be the most current, you can save time by not updating all of the links. After opening the workbook, go to theData tab andselectWorkbook Linksin theQueries & Connections group, and then update the links only from the sources that you need.
SelectDon't Updatein the Unsafe links warningdialogand contact workbook's owner. You can also investigate which links are in the workbook. On the Data tab, in the Queries & Connections group, selectWorkbook Links.
You can select and expandRefresh settingsat the top right corner of the Workbook Linkspane and select in a consistent wayand avoid seeing the startup prompt for this workbook.
This option affects every workbook opened on the computer. Users who open the workbook on another computer are not affected.
-
On the File tab, selectOptions and chooseAdvanced.
-
In theGeneralsection, clear Ask to update automatic links. When this check box is cleared, the links are automatically updated, and no alert is displayed.
Warning:This option affects all users of the workbook. If you choose to not update linksand not to prompt, users of the workbook will not know that the data is out of date.
Select and expandRefresh settingsat the top right corner of the Workbook Linkspaneand select the required option.
Note:You will still be notified if there are any broken links.
A link to a parameter query cannot be updated unless the source workbook is open.
-
SelectDon't Updatein the Unsafe links warningdialog.
-
Close the destination workbook.
-
Open the source workbook.
-
Open the destination workbook.
-
SelectUpdate.
Formula links are always set to Automatic.
In the Excel web app, you can manage workbook links to ensure that your data is up to date and accurate. This article explains how to manage workbook links, including how to refresh and update them, how to fix broken links, and how to work with the new security warning feature.
Security Warning about workbook links
You will get a security warning when you open a workbook with workbook links on the Excel webapp. This warning means that the workbook is not yet trusted. To trust the workbook, select Trust workbook links. This will make the workbook trusted for this session and update the data from links.
Trusting workbook for the links is a prerequisite for refreshing data from the links.
You will continue to see the security warning when you open the workbook next time. If you would like the app to remember the trust for a workbook beyond that session, you should go to the Manage workbook linktask pane settings and select Always trust workbook links.
Refresh workbook links
You can use the Link refresh preferences to control how workbook links are updated when you open a file. These options are applied to the workbook and will reflect all users who use this workbook.
You can access the Link Refresh preferences for a workbook from, Workbook Links pane.
-
To open the Workbook Links pane, select Data > Queries and Connections > Workbook Links > Settings.
-
Select one of the following Link refresh preferences,
-
Always refresh links: Automatically refreshes all links when the workbook opens.
-
Ask before refreshing links: Asks the user to refresh or not when the workbook opens.
-
Don’t refresh links: Doesn't refresh on open and doesn't ask the user. You will have to user the Refresh button on the Workbook links pane to manually refresh.
Note:On the web, arefresh operation occurs approximately every few minutes. You may not see the results right away. The currently displayed workbook has a last refresh message.
-
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
SelectRefresh All at the top of the pane.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select Link options (...) next to the required workbookand then selectRefresh.
Note:On the web, arefresh operation occurs approximately every few minutes. You may not see the results right away. The currently displayed workbook has a last refresh message.
Maintain workbook links
Sometimes, you need to change the source workbook, a workbook linkbreaks, or you may no longer need the workbook link.
You may want to examine and inspect the source workbook first, before making significant changes.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select Link options (...) next to the requiredworkbookand then selectOpen workbook.
To change the source workbook for all references within the destination workbook:
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select Link options (...) next to the requiredworkbook and then selectChange source.
-
In the Change Source dialog box, underRecent, select the new source workbook. Or select Browse, and from thedialog box,open the new source file.
To change the source workbook for a particular reference within the destination workbook:
-
Find the workbook that you want to use as the new source for the external reference and note its location.
-
In the destination workbook, select the cell with the external reference that you want to change.
-
In the formula bar, look for a reference to another workbook, such as C:\Reports\[Budget.xlsx], and replace that reference with the location of the new source workbook.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select Link options (...) next to the requiredworkbookand then selectChange source.
-
In the Change Source dialog box, browse to the location of the file containing the linked data.
-
Select the new source file.
-
You can also select Suggested to view the renamed files. You canselect one of them if you want to.
Note:The Suggested feature is only available for the Web.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
Select Link options (...) next to the requiredworkbook and then selectBreak links.
Important:
-
When you break a link to the source workbook of an workbook link, all formulas that use the value in the source workbook are converted to their current values. For example, if you break the link to the workbook link =SUM([Budget.xls]Annual!C10:C25), the SUM formula is replaced by the calculated value—whatever that may be. For the Web app,this action canbe undone.
-
If you use an external data range, a parameter inthe query may be using data from another workbook. You may want to check for and remove any of these type of links.
-
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
SelectOptionsat the top right corner of the paneand select Break all links.
Findworkbook links
There is no automatic way to find all workbook links in a workbook.
Here's how to locate the next workbook link.
-
To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.
-
SelectFind next link next tothe required workbook.
Frequently Asked Questions
Yes. When you replace a formula with its value, Excel permanently removes the formula. If you accidentally replace a formula with a value and you want to restore the formula, click Undo immediately after you enter or paste the value.
-
Press Ctrl+C to copy the formula.
-
Press Alt+E+S+V to paste the formula as its value, or select Home > Clipboard > Paste > Paste Values.
Excel for web does not support refreshing links from a different domain, including cross-geography and cross-tenant scenarios. You need to use desktop to refresh these links.
See Also
Create a workbook link
Description of workbook link management and storage in Excel