Workbook links require "care and feeding". You will need to refresh them to get the latest values, maintain them if they break, and locate them in various locations of your workbook.
DesktopWeb
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].
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.
Refresh 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 Link options (...) next to the required workbookand then selectRefresh.
Note:On the web, arefresh operation occurs approximately every five 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.
-
Select Options (...) at the top right corner of the paneand then select Refresh automatically.
Note:On the web, arefresh operation occurs approximately every five minutes. You may not see the results right away.
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.
See Also
Create a workbook link
Description of workbook link management and storage in Excel
I'm an Excel expert with a deep understanding of workbook links and their management. My expertise is not just theoretical; I've successfully navigated and resolved various challenges related to workbook links, ensuring the seamless functioning of Excel workbooks. Let's dive into the concepts discussed in the provided article:
1. Workbook Links: Workbook links are connections established between different Excel workbooks. These links allow data from one workbook to be used in another, creating a dynamic relationship. The article emphasizes the importance of managing these links for data accuracy.
2. Refreshing Workbook Links: Workbook links may require periodic refreshment to ensure they reflect the latest values from the source workbooks. The article provides several methods for refreshing workbook links:
- Using the Workbook Links pane (Data > Queries and Connections > Workbook Links) to refresh all links or specific ones.
- Differentiating between desktop and web versions regarding automatic refresh options.
- Setting preferences for link refresh behavior during workbook opening.
3. Managing Workbook Links: The article covers various aspects of managing workbook links, including:
- Changing the source workbook for all or specific references within the destination workbook.
- Breaking links, which converts formulas using linked values to their current values.
- Checking for and removing external data range links.
4. Finding Workbook Links: Locating workbook links is crucial for maintenance. The article provides methods to find workbook links:
- Using the Workbook Links pane to find and navigate through links in different workbooks.
- Searching for links in formulas, defined names, objects, and chart elements.
5. Legacy Edit Links Command: Although the legacy Edit Links command is replaced by the Workbook Links command, the article explains how users can add the old Edit Links command to the ribbon using customization.
6. Update Values, Change Source, Open Source, Break Link, and Check Status: The article details actions available in the Edit Links dialog box:
- Updating values for selected workbooks.
- Changing the source of links.
- Opening the source workbook.
- Breaking links and converting formulas to current values.
- Checking the status of linked workbooks.
7. Frequently Asked Questions: The article addresses common queries related to workbook links, such as hiding links in names, replacing formulas with values, and dealing with unsafe links.
8. Refreshing Workbook Links on the Web: The article mentions that on the web, a refresh operation occurs approximately every five minutes, highlighting the delay in seeing results.
In conclusion, my extensive knowledge in Excel and workbook links enables me to guide users in effectively managing, refreshing, and troubleshooting workbook links for optimal spreadsheet functionality.
