Manage workbook links

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.

   

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.
    

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.

   

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.

   

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

   

   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.

   

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

   

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

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

   

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

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:

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.

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.

Open Source

This will open the source workbook.

Break Link 

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.