Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

When we work on several worksheets, they become interconnected. However, sometimes,  we need to break those links and need to keep the values only. This article will demonstrate three methods to break links and keep values in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


3 Easy Methods to Break Links and Keep Values in Excel

To demonstrate the approaches, we consider 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. Now, we will break the links and keep the values.


1. Applying Edit Links Command from Data Tab

In this method, we will use the Edit Links command from the Data tab. The steps of this approach are given below:

📌 Steps:

  • First of all, go to the Data tab.
  • Now, click on the Edit Links options from the Queries & Connections group.

Choosing Edit Links Command from the Data Tab to Complete the Link Breaking Operation

  • As a result, a small dialog box called the Edit Links will appear.
  • Then, select the link which you want to break. The selection procedure is similar to the conventional selection procedure of your device.
  • After that, click on the Break Link option.

Select the Break Link Command to Continue the Process

  • Another dialog box with a warning will appear. The warning message will state to you that the link-breaking process is a permanent process, and after applying once, you cannot undo the task.
  • Click on Break Links to break the link. Otherwise, click Cancel.

  • Finally, click Close to close the Edit Links dialog box.
  • You will see that all the linking formulas are gone, and only values will display in the Formula Bar.

Applying Edit Links Command from Data Tab to Break Links and Keep Values

Thus, we can say that our method works perfectly, and we are able to break links and keep values in Excel.

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

Read More: How to Remove Broken Links in Excel (3 Simple Methods)


2. Using Excel Copy-Paste Feature

Here, we are going to use the Excel copy-paste feature to break lines and keep values. We can use the Excel copy-paste feature in two different ways. Both procedures are explained below:


2.1 Paste as Value

In this approach, we will paste the values as the Value format to break links and keep the values in our Excel spreadsheet. The steps of this process are given as follows:

📌 Steps:

  • First, select the range of cells C5:C14.
  • Now, press ‘Ctrl+C’ to copy the entities.

  • Then, right-click on your mouse, and from the Context Menu, choose the paste as a Value option.

Use of Value Option from Context Menu to Break External Data Links

  • You will notice that all linking formula is gone, and only values will show in the Formula Bar.

Excel Copy-Paste Feature to Break Links and Keep Values

Hence, we can say that our working approach works effectively, and we are able to break links and keep values in Excel.


2.2 From ‘Paste Special’ Option

In this process, we will use the Paste Special option to break links and keep values. The procedure is explained below step-by-step:

📌 Steps:

  • At first, select the range of cells C5:C14.
  • After that, press ‘Ctrl+C’ to copy the entities.

  • Now, in the Home tab, click the drop-down arrow of the Paste > Paste Special option from the Clipboard group.

Using 'Paste Special' Option

  • As a result, a small dialog box called Paste Special will appear.
  • Then, in the Paste section, choose the Values option.
  • Finally, click OK.

Choosing Suitable Option to Restore Only Value and Disintegrate the Data Links

  • You will see that all the linking formulas are gone, and only values will display in the Formula Bar.

Using Paste As Value Option to Break Links and Keep Values

Therefore, we can say that our working approach works precisely, and we are able to break links and keep values in Excel.

Read More: How to Break Links in Excel Before Opening File (With Easy Steps)


3. Embedding VBA Code

Writing a VBA code can also help you to break links and keep values. The steps of this process are given as follows:

📌 Steps:

  • To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Moreover, you can launch it by pressing the ‘Alt+F11’ button.

Opening Visual Basic Editor Box to Write a VBA Code to Break Down the Links

  • As a result, a small dialog box will appear.
  • Now, click on the Module option located in the Insert tab.

  • Then, write down 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
  • Afterward, press ‘Ctrl+S’ to save the code.
  • Close the Editor tab.
  • After that, in the Developer tab, click on Macros from the Code group.

  • As a result, 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.

Run the VBA Code to Keep only Values and Removing Links

  • You will get only the values inside the cells.

Embedding VBA Code to Break Links and Keep Values

Finally, we can say that our visual code worked successfully, and we are able to break links and keep values in Excel.

💬 Things You Should Know

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

Read More: How to Break Links in Excel When Source Is Not Found (4 Ways)


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to break links in excel and keep values. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo