Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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. 

 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, select Data > Queries and Connections > Workbook Links.

  2. Select Refresh all at the top of the pane.

    windows refresh all
     

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

  2. Select the workbook from the list and select Refresh.

    windows refresh specific workbook
     

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, see Change 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, select Data > Queries and Connections > Workbook Links.

  2. Select and expand Refresh settings at the top right corner of the pane and select one of the following:

    • Ask to refresh    Asks the user to refresh or not when the workbook opens.

    • Always refresh    Automatically refreshes all links when the workbook opens.

    • Don't refresh    Doesn'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 links if 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 warning dialog, select Update. This updates all the data links in the workbook.

    unsafe link dialog

If you only want to update specific links, select Don't Update, and see Change the source workbook below. 

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 link breaks, 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, select Data > Queries and Connections > Workbook Links.

  2. Select More Commands (...) next to the required workbook and then select Open workbook.

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

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

  2. Select More Commands (...) next to the required workbook and then select Change source.

  3. In the Change Source dialog box, under Recent, select the new source workbook. Or select Browse, and from the dialog 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, select Data > Queries and Connections > Workbook Links.

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

    fix broken link windows

  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, select Data > Queries and Connections > Workbook Links.

  2. Select More Commands (...) next to the required workbook and then select Break 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 in the 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, select Data > Queries and Connections > Workbook Links.

  2. Select Break all at the top of the pane.
     

Find workbook 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, select Data > Queries and Connections > Workbook Links.

  2. Select the workbook from the list and select Find next.

    windows find next workbook

  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.

    Find and Replace dialog

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

    Name Manager dialog

    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.

    Go To > Special dialog

  2. Press the Tab key to move between each of the selected objects, and then look in the formula bar Button image for a reference to another workbook, such as [Budget.xlsx].

    Shape selected to show link name in the Formula bar

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

  2. In the formula bar Button image, 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.

    Select a Series option in Chart options > Format > Current Selection

  3. In the formula bar Button image, look for a reference to another workbook, such as [Budget.xls] in the SERIES function.

Use the legacy Edit Links command

The legacy Edit 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 Links command to the Queries and Connections group. 

Using Customize the Ribbon, you need to create your custom group and only then you can add Edit Links to your custom group in ribbon as follows:

old edit links

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

  2. In the Choose commands from drop-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.

Excel's Edit Links dialog from Data > Queries & Connections > Edit Links

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 no longer hidden. To remove the link, select Break Links for the selected workbook in 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 choose  Undo or 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.

Select Don't Update in the Unsafe links warning dialog. 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.

Select Don't Update in the Unsafe links warning dialog.

Select Don't Update in the Unsafe links warning dialog. 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 the Data tab and select  Workbook Links in the Queries & Connections group, and then update the links only from the sources that you need.

Select Don't Update in the Unsafe links warning dialog and contact workbook's owner. You can also investigate which links are in the workbook. On the Data tab, in the Queries & Connections group, select  Workbook Links.

You can select and expand Refresh settings at the top right corner of the Workbook Links pane and select in a consistent way and 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, select Options and choose Advanced.

  2. In the General section, 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 links and not to prompt, users of the workbook will not know that the data is out of date.

Select and expand Refresh settings at the top right corner of the Workbook Links pane and 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. Select Don't Update in the Unsafe links warning dialog.

  2. Close the destination workbook.

  3. Open the source workbook.

  4. Open the destination workbook.

  5. Select Update.

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.

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

  2. Select Refresh All at the top of the pane.

    Manage Links pane in Excel for the web, with the Refresh All command circled.

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

  2. Select Link options (...) next to the required workbook and then select Refresh.
    web refresh specific

    Note: On the web, a refresh operation occurs approximately every five 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, select Data > Queries and Connections > Workbook Links.

  2. Select Options (...) at the top right corner of the pane and then select Refresh automatically.

    The 'More Options' menu with the 'Refresh automatically' command circled.

    Note: On the web, a refresh 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 link breaks, 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, select Data > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the required workbook and then select Open workbook.

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

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

  2. Select Link options (...) next to the required workbook and then select Change source.

  3. In the Change Source dialog box, under Recent, select the new source workbook. Or select Browse, and from the dialog 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, select Data > Queries and Connections > Workbook Links.

  2. Select Link options (...) next to the required workbook and then select Change source.

    fix broken link web

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

  4. Select the new source file.

    change source for web

  5. You can also select Suggested to view the renamed files. You can select one of them if you want to.

    Note: The Suggested feature is only available for the Web.

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

  2. Select Link options (...) next to the required workbook and then select Break 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 can be undone.

  • If you use an external data range, a parameter in the 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, select Data > Queries and Connections > Workbook Links.

  2. Select Options at the top right corner of the pane and select Break all links.

    'Break all links' command in the Manage Links pane.

Find workbook 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, select Data > Queries and Connections > Workbook Links.

  2. Select Find next link next to the required workbook.

    web find next wkbk
     

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.

See Also

Create a workbook link

Description of workbook link management and storage in Excel

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×