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
Close all source workbooks. If one source workbook is open, and others are closed, the updates will not be uniform.
Open the destination workbook.
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
Open the workbook that contains the links.
Go to Data > Queries & Connections > Edit Links.
Data Connections
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.
Click Update Values.
Change the source workbook of one external reference without affecting other external references in 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 Button image , 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.
Go to Data > Queries & Connections > Edit Links.
Click Startup Prompt.
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.
Excels 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, 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.
– On the Data tab, in the Queries & Connections group, click Edit Links.
– 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.
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 re-link the workbook.
