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 link from one to another workbook. This is called external links. These invisible links are mostly unnoticeable and may trouble you on 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 on your calculations. In a similar way, if you make any changes on 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 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 formula the output shown below.
- To get the data on 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 applied with the same formula to find sales done for all listed products you will get the result as 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 this 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 on the reference. Let’s see how the “Open Source” works. Click on “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 will 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 “OK” button.
- Since the same data contains within the workbook “Sales Report” you will get the same result as before but the source will change as it shows in the formula bar.
Break the External Links in Excel
In a large calculation, you may repeatedly use different sourcebooks as a reference. In any situation there happen a chance to lost or delete the external workbook there have chances 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.
- By using Example 2 workbook we are making a chart representation to easily understand the sales done for each product.
- By selecting the entire data go to insert menu and select 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 keep changing.
- Now I have deleted the Qty details from Example 1. Now if you open the Example 2 it will show the error as below.
- To avoid this type of errors it is better to break the external links after making the calculations. To do this select the edit link from data.
- Select the Break Link button.
- You will get a confirmation if you want to break the external links. Select on the “Break Links” button. You will get the previous window without any external link.
- Now whatever changes you made with workbook Example 1 the Example 2 won’t get affected.
Things to Remember About Break Links in Excel
- Break links are the easy way to edit the source or references.
- To avoid any kind of data loss or minimize errors on calculation break links are preferred.
- It is possible to change or update the source using edit links.
- Once applied any chart or graphical representation with the calculated data, “Break Links” are the best option to avoid changes on graphs according to the change 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 downloadable excel template. You can also go through our other suggested articles –
