How to Break Links in Excel and Keep Values (3 Easy Ways)

We have a dataset of 10 employees and their salaries for the first two months in a file titled Employee Salary Details.xlsx.

The sum of those salaries is shown in our final dataset. As a result, both files are linked with each other. We will break the links and keep the values.

Method 1 – Applying the Edit Links Command from the Data Tab

Steps:

Go to the Data tab.
Click on the Edit Links options from the Queries & Connections group.

A small dialog box called Edit Links will appear.
Select the link which you want to break.
Click on the Break Link option.

Another dialog box with a warning will appear.
Click on Break Links to break the link.

Click Close to close the Edit Links dialog box.
The most recent values will be displayed in the Formula Bar.

Things You Should Know

Using the Edit Links command, you can break a particular type of link all over the workbook at a glance. For example, if you apply a simple summation link from any external workbook like us in 4/5 different worksheets of your active workbook, then after using this command, all those links will break, and only the values will remain behind.

Method 2 – Using Excel Copy-Paste

Case 2.1 – Paste as Values

Steps:

Select the range of cells C5:C14.
Press Ctrl + C to copy the entities.

Right-click on the same range and choose the Values option.

Only the values will show in the Formula Bar.

Case 2.2 – From Paste Special

Steps:

Select the range of cells C5:C14.
Press Ctrl + C to copy the cells.

In the Home tab, click the drop-down arrow of Paste, then go to the Paste Special option from the Clipboard group.

A small dialog box called Paste Special will appear.
In the Paste section, choose the Values option.
Click OK.

Only values will display in the Formula Bar.

Method 3 – Embedding VBA Code

Steps:

Go to the Developer tab and click on Visual Basic. If you don’t have the tab, you have to enable the Developer tab. You can also use the Alt + F11 shortcut.

The VBA coding editor will appear.
Click on the Module option located in the Insert tab.

Insert the following VBA code in the empty editor box.

Sub Break_Links_and_Keep_Value()
    Dim Connection As Variant
    Connection = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    Do Until IsEmpty(Connection)
        ActiveWorkbook.BreakLink Name:=Connection(1), _
          Type:=xlLinkTypeExcelLinks
        Connection = _
          ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    Loop
End Sub

Press Ctrl + S to save the code.
Close the Editor tab.
In the Developer tab, click on Macros from the Code group.

A small dialog box titled Macro will appear.
Select the Break_Links_and_Keep_Value option and click the Run button to run the code.

You will get only the values inside the cells.

Things You Should Know

This VBA code will break all the external data links available in this workbook. If you need to break any specific link in any large dataset, follow the other procedures.

Download the Practice Workbooks

Break Links And Keep Values.xlsm

Employee Salary Details.xlsx

<< Go Back To Excel Break Links | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!