EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Break Links in Excel
 

Break Links in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 22, 2023

break links in excel

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You may have to break the links associated with the following:

  1. Normal workbook formulas
  2. Links from charts
  3. 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.

(Break Links in Excel) break link 6

  • 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.

breaks link in excel 2

  • After using the formula, the output is shown below.

(Break Links in Excel) break link 7

  • 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.

break links in excel

  • 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.

breaks link in excel 1-5

  • 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.

breaks link in excel 1-6

  • 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.

breaks link in excel 1-7

  • You will get the option to edit the corresponding external link.

breaks link in excel 1-8

  • You have different options to make changes to the reference. Let’s see how the “Open Source” works. Click on the “Open Source” button.

breaks link in excel 1-9

  •  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.

breaks link in excel 2-1

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.

Source Workbook of External References 1

  • You will get access to the system files to select another source workbook.

Source Workbook of External References 2

  • Here I select another workbook, “Sales Report,” and then click the “OK” button.

Source Workbook of External References 3

  • 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.

Source Workbook of External References 4

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.

(Break Links in Excel) insert chart bar

  • 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.

(Break Links in Excel) insert chart bar 1

  • Now I have deleted the Qty details from Example 1. Now, if you open Example 2, it will show the error as below.

Insert chart

  • 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.

insert chart

  • 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.

insert chart 1

  • 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 –

  1. Line Break in Excel
  2. Excel Insert Page Break
  3. Find External Links in Excel
  4. Remove Hyperlinks in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW