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.
How to Break Links and Keep Values in Excel: 3 Easy Ways
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.
- 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.
- 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.
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.
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.
- You will notice that all linking formula is gone, and only values will show in the Formula Bar.
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.
- As a result, a small dialog box called Paste Special will appear.
- Then, in the Paste section, choose the Values option.
- Finally, click OK.
- You will see that all the linking formulas are gone, and only values will display in the Formula Bar.
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
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.
- 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.
- You will get only the values inside the cells.
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
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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. Keep learning new methods and keep growing!
