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.
In the Edit Links dialog box that appears, click on the Break Link button.
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.
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.
2 - Use the Find and Replace dialog box as follows:
3 - Then click on the Replace All button.