Control when external references links are updated

When you create external references to other workbooks (also called links), you can control if and when they're updated. This is important because if someone else changes a linked cell's value, Excel does not update the link and display the new value unless you tell it to.

The workbook that contains the links is called the destination workbook, and the workbook that it's linked to is called the source workbook. You can link multiple source workbooks to a single destination workbook.

1. The destination workbook contains the external reference (link).

2. The external reference (or link) is a reference to a cell or range in the source workbook.

3. The source workbook contains the linked cell or range, and the actual value that is returned to the destination workbook.

Note: While there's no limit to the number of workbooks you can link to, it's generally a good idea to link to as few as possible. Otherwise, you may run into performance issues with formula recalculation. It is also possible to cross link workbooks, where each acts as both the source and destination, but this can get confusing and difficult to manage.

Control links to other workbooks

When the source and destination workbooks are opened on the same computer, links are updated automatically. When you open a destination workbook, and the source workbook is not open, you may be alerted by the Trust Bar whether 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.

In the following sections we'll review the most common options for managing how links are updated.

Manually update all or none of the links in a workbook

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. To update the links, on the Trust Bar, click Update. Close the Trust Bar if you don't want to update the links (look for the X on the right-hand side).

Manually update only some of the links to other workbooks

1. Open the workbook that contains the links.

2. Go to Data > Queries & Connections > Edit Links.

   

3. In the Source list, click the linked object that you want to update.

   You can select individual workbooks with Ctrl+click, or all of them with Ctrl+A.

4. Click Update Values.

   

Change the source workbook of one external reference without affecting other external references in 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.

Control the startup prompt for updating links

You can decide whether the links in this workbook are updated when opening the file according to the user's setting, not updated when opening the file, or updated automatically without prompting the user.

Warning: This option affects all users of the workbook. If you choose not to update links, and not to prompt, users of the workbook will not know that the data is out of date.

1. Go to Data > Queries & Connections > Edit Links.

2. Click Startup Prompt.

3. Select from one of the following three options:

• Let users choose to display the alert or not

• Don't display the alert and don't update automatic links

• Don't display the alert and update links.

Notes: 

• Automatic vs. Manual update options - Formula links are always set to Automatic.

• Even if you specify that Excel should not ask whether to update links, you will still be notified if there are any broken links.

Edit Link options

When you open the Edit Links dialog (Data > Queries & Connections > Edit Links), 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, 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. On the Data tab, in the Queries & Connections group, click Edit Links.

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

  3. Click Break Link.

     Notes: 

     • If the link used a defined name, the name is not automatically removed, so you may also want to delete the name.

     • To delete a name:

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

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

• 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

• Can I replace a single formula with its calculated value?

  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 go to Home > Clipboard > Paste > Paste Values.

• What if I'm not connected to the source?

  Click Don't Update. 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.

• I don't want my current data replaced with new data

  Click Don't Update.

• I tried to update last time, but it took too long

  Click Don't Update. 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, on the Data tab, in the Connections group, click Edit Links, and then update the links only from the sources that you need.

• Someone else created the workbook, and I don't know why I'm seeing this prompt

  Click Don't Update, and contact workbook's owner. You can also investigate which links are in the workbook. On the Data tab, in the Queries & Connections group, click Edit Links.

• I answer the prompt the same way every time, and don't want to see it again

  You can answer the startup prompt in a consistent way, and avoid seeing the startup prompt for this workbook.

  • Don't prompt for all workbooks I open, and update the links automatically    

    This option affects every workbook opened on the computer. Users who open the workbook on another computer are not affected.

    1. Go to File > Options > Advanced.

    2. Under General, clear the Ask to update automatic links check box. If this check box is cleared, the links are automatically updated, and no alert is displayed.

  • Prompt in the same way for every user of this workbook    

    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.

    Go to Data > Queries & Connections > Edit Links > Startup Prompt, then select the option that you want..

    Note: You will still be notified if there are any broken links.

• What if I'm using a parameter query?

  1. Click Don't Update.

  2. Close the destination workbook.

  3. Open the source workbook.

  4. Open the destination workbook.

  5. Click Update.

  A link to a parameter query cannot be updated unless the source workbook is open.

• Why can't I choose "Manual" as an update option for a specific external link?

  Formula links are always set to Automatic.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.