Updated August 22, 2023
Introduction to Break Links in Excel
In this article, we will learn about 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 workbook to another. 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 used to break these links while going with any calculations. You will be referring cells from one worksheet to another for different calculations. In this case, there exist links from one to another. Deleting a workbook linked with another workbook for any type of calculation will lead to an error in your calculations. Similarly, your calculations keep changing if you make any changes to your referred workbook.
You may have to break the links associated with the following:
- 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. Workbook Example 1 consists of a List of Products, Qty sold, and the price of individual products. In workbook Example 2, we have used data belonging to workbook Example 1.
- We need to get the sum of sales done for individual products in the workbook Example 2 to find 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 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 Example 2 completely depends on the values given in workbook Example 1. So Example 2 consists of reference from workbook Example 1.
- Please go through the formula bar. You can see the source file used and the reference links.
- If you want to change the applied formula, there is no need to go again with workbook Example 1.
- 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. 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 can open the source workbook and make any changes to it. Specifically, 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 change the referred data from one workbook to another, you can change the reference where the same workbooks are referred.
- Select the cell and 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 select another workbook, “Sales Report,” and then click 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
You may repeatedly use different sourcebooks as a reference in a large calculation. The calculations will keep changing if you make any changes in the source workbook. There has a chance to affect the current workbook data or calculations. There is a chance to lose or delete the external workbook. 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.
- Now we are making any changes to the data in the workbook Example 1, the reference for Example 2. From the chart, it is visible that sales are made for each product. In the chart, you can see that for product 9, sales are near 6000.
- Now I have changed the Qty to “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.
- It is better to break the external links after making the calculations to avoid this type of error. 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 and 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.
- Break links are preferred to avoid any kind of data loss or minimize errors in calculation.
- 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” is 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
