17. Excel How to break links ?


In Excel, links allow you to reference cells or ranges in one workbook in a formula in a different workbook.

Using links in formulas

To reference a cell or range of cells in another sheet in the same workbook we use the name of the sheet followed by a "!"

For example, we reference the range A1:C10 on sheet "Sheet2" in a formula in sheet "Sheet1" in the same workbook as follows:

Sheet2!A1:C10

If sheet "Sheet2" belongs to a different workbook than sheet "Sheet1", then add the workbook name in square brackets.

To reference the range A1:C10 of sheet "Sheet2" in "Workbook2" in a sheet in another workbook, then write :

[Workbook2.xlsx]Sheet2!A1:C10

And when the workbook "Workbook2" is closed, then the reference becomes :

C:\folder1\folder12\folder121\[Workbook2.xlsx]Sheet2!A1 :C10

That is, the name of the workbook is preceded by the full path where it is saved.

How do I break links?

To break links in a sheet, use the Edit Links command in the Connections group on the Data tab of the Ribbon.

Excel 2016 Edit Links command

In the Edit Links dialog box that appears, click on the Break Link button.

Excel 2016 Edit Links dialog box

The result is that the cells concerned by the link will have the current values and then the link to the referenced workbook is broken.

Changing the source

You can change the source of a sheet link by using the Change Source button in the Edit Links dialog box.

The Windows File Explorer is then opened allowing you to select the workbook for the new link.

Then the Select Sheet dialog box is opened to allow you to select the sheet to which the link applies.

Excel 2016 Select Sheet dialog box

NB. This method allows you to change the source of the link to point to a sheet in a different workbook. But how do you point to a sheet in the current workbook? In fact, by pointing to a sheet in the current workbook, it is not a link. The solution is to use the Find/Replace technique.

Changing the source using the Find/Replace technique

As described above, the reference for the range A1:C10 of a sheet "Sheet2" in "Workbook2" is written as :

[Workbook2.xlsx]Sheet2!A1:C10

To change a reference to point to the "SheetX" of the current workbook, then simply replace in each formula.

[Workbook2.xlsx]Sheet2

with

SheetX

If you have several formulas on your sheet, use the Find/Replace technique to do this replacement:

1 - Click on the Find and Select button in the Editing group on the Home tab of the Ribbon. Then on the Replace button.

Excel 2016 Replace Button

2 - Use the Find and Replace dialog box as follows:

Excel 2016 Find and Replace Dialog Box

3 - Then click on the Replace All button.


<< 16. Excel How to add times ?

18. Excel How to insert a line break in a cell ? >>