This guide will discuss how to break links and keep values in Excel using two simple and efficient methods.
Excel is a powerful tool that allows its users to link data from different workbooks. So we can input a data set that is linked to another workbook. And when we change the value of our data set in the source workbook, it will automatically change or update in the existing workbook it is linked to.
But a problem occurs when you try to send a workbook containing the links. Because it can cause some issues and affect the file, it is much better to break the links and only keep the values instead.
Two Methods for Breaking Links and Keeping Values in Excel
So there are two methods that we can use to break the links and keep only the values in Excel. Firstly, we can utilize the copy and paste features in Excel. There are many options for pasting something in Excel.
For example, we can simply paste, or we can also be more specific in what we want to paste. So we can paste formulas, paste values, paste formatting, paste transpose, and paste links.
Secondly, we can break all links in a workbook in the Data tab of Excel. Furthermore, this method is especially efficient when dealing with a massive data set containing multiple links. Because we can simply choose to break all links present in the workbook, we can do this really quickly.
Let’s take a sample scenario.
Suppose you want to send a file to your colleague. But the file contains several links to another file that may not work anymore when sent. So you want first to break the links and keep the values only in the file.
Worry not! You can use any of the two methods to be discussed to break the links and send the file without any issues to your colleague.
Great! Now let’s dive into a real example of breaking links and keeping values in Excel.
A Real Example of Breaking Links and Keeping Values in Excel
Let’s say we have an Excel file with a data set containing several links to another workbook for its values. For instance, we have a data set regarding the total sales of each month. And the values for the total sales are linked to another file.
So there are two ways we can go about breaking the links and keeping the values only. Firstly, we can use the copy and paste features in Excel. Essentially, we will simply copy the data and paste them as values only using the Paste Value option.
Secondly, we have the option to break all links in a workbook using the Workbook Links under the Data tab. Excel is an extremely helpful tool that allows users to link different workbooks with one another. Because of this function, we might have difficulty keeping track of all the linked files.
So Excel has the Workbook Links feature in the Data tab that displays all the external links currently existing in our workbook. And it is here we can simply break these links only to keep the values. And this will allow us to easily remove all links no matter how many are in the workbook.
You can make your own copy of the spreadsheet above using the link attached below.
Awesome! Now let’s move on and learn the steps of how to break links and keep values in Excel using these two simple and efficient methods. But, let’s first discuss the first method.
How to Break Links and Keep Values in Excel using Paste Value
In this section, we will discuss the steps of how to use the copy and paste features in Excel, specifically the Paste Value option to break links and keep values in Excel.
1. Firstly, we need to copy the cell range containing the data with links to another workbook. So select the cell range and click Ctrl + C to copy the data. Then, go to the Home tab and click on Clipboard. In the dropdown menu, select Paste Value.
But, we can also simply right-click after copying and choose Pasta Value in the menu that appears. Additionally, the Paste Value option is the clipboard with the number 123 on it.
2. And tada! We have successfully broken the links and kept the values in Excel using the copy and paste method.
How to Break Links and Keep Values in Excel using Break all links
In this section, we will explain the step-by-step process of how to break links and keep values in Excel using the Break all links feature.
1. Firstly, go to the Data tab and select Workbook Links.
2. Afterward, the Workbook Links window will appear on the right side. If you have any existing links to another workbook, it will most likely display in the window.
Then, click on the three dots in the upper right. Lastly, simply select Break all links.
3. And tada! We have successfully broken all links and kept only the values in Excel using the second method.
And that’s pretty much it! We have explained two simple and efficient methods of how to break links and keeping values in Excel. Firstly, we have the copy and paste method, which involves using the Paste Value option. Then, our second method is using the Break all links option in the Workbook Links feature.
Now you can make use of any of these two simple and efficient methods whenever you need to.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
