Manage workbook links - Microsoft Support (2024)

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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. SelectRefresh all at the top of the pane.

    Manage workbook links - Microsoft Support (1)

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select the workbook from the list and selectRefresh.

    Manage workbook links - Microsoft Support (2)

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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. 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.

  1. Close all source workbooks. If one source workbook is open, and others are closed, the updates will not be uniform.

  2. Open the destination workbook.

  3. In the Unsafe links warningdialog, select Update. This updates all the data links in the workbook.

    Manage workbook links - Microsoft Support (3)

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:

  1. Select Enable Content.This makes the workbook trusted for this session, and updates the links.

  2. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select More Commands (...) next to therequired workbookand then selectOpen workbook.

To change the source workbook for all references within the destination workbook:

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select More Commands (...) next to the required workbookand then selectChange source.

  3. 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:

  1. Find the workbook that you want to use as the new source for the external reference and note its location.

  2. In the destination workbook, select the cell with the external reference that you want to change.

  3. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select More Commands (...) next to the data workbook with links you want to fix, then selectChange source.

    Manage workbook links - Microsoft Support (4)

  3. In the Change Source dialog box, browse to the location of the file containing the linked data.

  4. Select the new source file.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select the workbook from the list and selectFind next.

    Manage workbook links - Microsoft Support (5)

  1. Press Ctrl+F to launch the Find and Replace dialog.

  2. Click Options.

  3. In the Find what box, enter .xl.

  4. In the Within box, click Workbook.

  5. In the Look in box, click Formulas.

  6. Click Find All.

  7. 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.

    Manage workbook links - Microsoft Support (6)

  8. 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.

  1. On the Formulas tab, in the Defined Names group, click Name Manager.

  2. 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].

    Manage workbook links - Microsoft Support (7)

    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.

  1. Press Ctrl+G, the shortcut for the Go To dialog, then click Special > Objects > OK. This will select all objects on the active worksheet.

    Manage workbook links - Microsoft Support (8)

  2. Press the Tab key to move between each of the selected objects, and then look in the formula bar Manage workbook links - Microsoft Support (9) for a reference to another workbook, such as [Budget.xlsx].

    Manage workbook links - Microsoft Support (10)

  1. Click the chart title on the chart that you want to check.

  2. In the formula bar Manage workbook links - Microsoft Support (11), look for a reference to another workbook, such as [Budget.xls].

  1. Select the chart that you want to check.

  2. 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.

    Manage workbook links - Microsoft Support (12)

  3. In the formula bar Manage workbook links - Microsoft Support (13), 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:

Manage workbook links - Microsoft Support (14)

  1. Right-click the ribbon and select Customize the Ribbon.

  2. In theChoose commands fromdrop-down, select All Commands.

  3. Select Edit Links and select the Data tab.

  4. 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.

Manage workbook links - Microsoft Support (15)

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.

  1. 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.

  2. Click Break Link.

  3. If the link used a defined name,you may also want to delete the name. To delete a name:

    1. On the Formulas tab, in the Defined Names group, click Name Manager.

    2. 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.

  1. Press Ctrl+C to copy the cell with the formula.

  2. 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.

  1. On the File tab, selectOptions and chooseAdvanced.

  2. 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.

  1. SelectDon't Updatein the Unsafe links warningdialog.

  2. Close the destination workbook.

  3. Open the source workbook.

  4. Open the destination workbook.

  5. 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.

Manage workbook links - Microsoft Support (16)

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.

Manage workbook links - Microsoft Support (17)

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.

  1. To open the Workbook Links pane, select Data > Queries and Connections > Workbook Links > Settings.

  2. Select one of the following Link refresh preferences,

    1. Always refresh links: Automatically refreshes all links when the workbook opens.

    2. Ask before refreshing links: Asks the user to refresh or not when the workbook opens.

    3. 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.

      Manage workbook links - Microsoft Support (18)

      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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. SelectRefresh All at the top of the pane.

    Manage workbook links - Microsoft Support (19)

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the required workbookand then selectRefresh.

    Manage workbook links - Microsoft Support (20)

    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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the requiredworkbookand then selectOpen workbook.

To change the source workbook for all references within the destination workbook:

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the requiredworkbook and then selectChange source.

  3. 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:

  1. Find the workbook that you want to use as the new source for the external reference and note its location.

  2. In the destination workbook, select the cell with the external reference that you want to change.

  3. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the requiredworkbookand then selectChange source.

    Manage workbook links - Microsoft Support (21)

  3. In the Change Source dialog box, browse to the location of the file containing the linked data.

  4. Select the new source file.

    Manage workbook links - Microsoft Support (22)

  5. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. 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.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. SelectOptionsat the top right corner of the paneand select Break all links.

    Manage workbook links - Microsoft Support (23)

Findworkbook links

There is no automatic way to find all workbook links in a workbook.

Here's how to locate the next workbook link.

  1. To open the Workbook Links pane, selectData > Queries and Connections > Workbook Links.

  2. SelectFind next link next tothe required workbook.

    Manage workbook links - Microsoft Support (24)

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.

  1. Press Ctrl+C to copy the formula.

  2. 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

Manage workbook links - Microsoft Support (2024)
Top Articles
Latest Posts
Recommended Articles
Article information

Author: Jamar Nader

Last Updated:

Views: 5403

Rating: 4.4 / 5 (55 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Jamar Nader

Birthday: 1995-02-28

Address: Apt. 536 6162 Reichel Greens, Port Zackaryside, CT 22682-9804

Phone: +9958384818317

Job: IT Representative

Hobby: Scrapbooking, Hiking, Hunting, Kite flying, Blacksmithing, Video gaming, Foraging

Introduction: My name is Jamar Nader, I am a fine, shiny, colorful, bright, nice, perfect, curious person who loves writing and wants to share my knowledge and understanding with you.