Break Links in Excel (Table of Contents)
Introduction to Break Links in Excel
You may come across using data from one workbook to another. We can find many situations where we can see links from one to another workbook. These are called external links. These invisible links are mostly unnoticeable and may trouble you with your calculations. Excel consists of options to remove these external links. Most probably, we use to break these links while going with any calculations. For different calculations, you will be referring cells from one worksheet to another. In this case, there exist links from one to another. If we are deleting a workbook which linked with another workbook for any type of calculation, this will lead to an error in your calculations. In a similar way, if you make any changes to your referred workbook, your calculations keep changing.
You may have to break the links associated with:
- Normal workbook formulas
- Links from charts
- Links from pivot table etc.
How to Break Links in Excel?
- We have two workbooks named Example 1 and Example 2. In workbook Example 2, we have used data belongs to the workbook Example 1. Workbook Example 1 consists of a List of Products, Qty sold, and the price of individual products.
- We need to get the sum of sales done for individual products in workbook Example 2 to find some other data like the rate of sales and movement of individual products for a period or to make a sales chart etc.
- After using the formula, the output is shown below.
- To get the data on the sales column, we have applied the formula “='[Example 1.xlsx]Sheet1′!$C$2*'[Example 1.xlsx]Sheet1′!$D$2” in column C2 of Example2 workbook here the referred columns are from Example1.
- Once the entire column is applied with the same formula to find sales done for all listed products, you will see the result below.
- From this, it is clear that the sum of sales done for each product in workbook Example2 is completely dependent on the values are given in the workbook Example1. So the Example2 consists of reference from workbook Example1.
- Please go through the formula bar you can see the source file used and the reference links.
- If you want to make any changes in the applied formula, no need to go again with workbook Example1.
- You can change the referred links using edit links. To edit these external links, select “Data” from the ribbon.
- From the connection option along with Refresh All, Connections you can find Edit Links. Select the edit link option. You can see the pop-up shows the connection to another workbook where you can update or remove the external links.
- You will get the option to edit the corresponding external link.
- You have different options to make changes to the reference. Let’s see how the “Open Source” works. Click on the “Open Source” button.
- You will get the source workbook opened and can make any changes with it. Here Example 1 will be opened. If you made any changes, it would ask whether you want to save the changes made or not. Once you click on save, the changes made will be saved, and this will reflect on the reference workbook.
Change the Source Workbook of External References Used
You can make changes to the external workbooks used within an excel workbook. In any of the situations, if you are changing the referred data from one workbook to another, you can change the reference where the same workbooks are referred.
- Select the cell go to data -> Edit Links.
- Click On the “Change Source” button.
- You will get access to the system files to select another source workbook.
- Here I am selecting another workbook, “ Sales Report,” and then clicks the “OK” button.
- Since the same data contains the workbook “Sales Report,” you will get the same result as before, but the source will change as shown in the formula bar.
Break the External Links in Excel
In a large calculation, you may repeatedly use different sourcebooks as a reference. There is a chance to lose or delete the external workbook there has a chance to affect the current workbook data or calculations. If you make any changes in the source workbook, the calculations will keep changing. In these situations, you may use the “Break Link” option from Edit Link.
- Using Example 2 workbooks, we are making a chart representation to understand the sales done for each product easily.
- By selecting the entire data, go to the insert menu and select the insert bar chart.
- From the chart, it is visible sales are done for each product. Now we are making any changes to the data in the workbook Example 1, which is the reference for Example 2. In the chart you can see for product 9 sales done is near to 6000.
- Now I have changed the Qty as “0” for product 9; you can see the data, and the chart keeps changing.
- Now I have deleted the Qty details from Example 1. Now, if you open Example 2, it will show the error as below.
- To avoid this type of error, it is better to break the external links after making the calculations. To do this, select the edit link from the data.
- Select the Break Link button.
- You will get a confirmation if you want to break the external links. Select the “Break Links” button. You will get the previous window without any external link.
- Now whatever changes you made with workbook Example 1, Example 2 won’t get affected.
Things to Remember About Break Links in Excel
- Break links are an easy way to edit the source or references.
- To avoid any kind of data loss or minimize errors in calculation, break links are preferred.
- It is possible to change or update the source using edit links.
- Once applied to any chart or graphical representation with the calculated data, “Break Links” are the best option to avoid changes on graphs according to the change that happens to the data.
Recommended Articles
This is a guide to Break Links in Excel. Here we discuss How to Break Links in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
