What is Break Links in Google Sheets?
Break links in Google Sheets is the action of severing the connection between a cell and a linked source present in another spreadsheet. All the formulas referencing the linked data are converted to their static values when a link is broken. Thus, the link to the original source is broken by this method. Thus, any updates in the source do not affect the destination sheet. When a link is broken, the formulas referencing the linked data are converted to their current, static values, and the link to the original source is removed. This prevents further updates from the source from affecting the destination sheet.
When one breaks a link, the formula is replaced by its current calculated value. The cell is no longer relying on the external data and the formulas are no longer linked to the source dynamically. Break links in Google Sheets can be done using the “Break Link” option within the “Edit Links” dialog or by copying and pasting values only, which replaces formulas with their results. For example, if a cell in Sheet1 pulls data from Sheet2 as it contains a formula =Sheet2!A1. The cell A1 in Sheet2 has a value 25.
If the link between the two sheets is broken, the formula in Sheet1 will be replaced with the current value in Sheet2!A1.
Why Break Links in Google Sheets?
If you are left wondering why we must break a link, read on to know more.
There could be several reasons to break a link. They are:
- Performance: If there are several linked sheets, as the spreadsheets keep adding, the performance of your sheet can slow down. Hence, it is removed to improve speed.
- Consistency: External links can depend on other sheets. If by chance, that file is deleted, we end up with erroneous values due to the broken links.
- Portability: In cases where you must share the sheet, if there is a connected link, it could cause problems if the sheet where we have links is not available to others.
- Independence: You do not have to rely on changes in external sources affecting your sheet; it ensures your data remains static.
How To Break Links in Google Sheets?
To break links in Google Sheets, we can follow a few steps.
Step 1: Look for cells with formulas that pull in data like IMPORTRANGE with static values. You can select such cells or ranges. in a simple example, we have a cell A1 that contains the following formula:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1DyowwlN98fZ2V7KLLeAZ7oQdiYXd_CtEncidtKioeA4/edit?gid=1712690680#gid=1712690680”, “A1:A5”)
Here, you can replace the first argument with the URL of the source spreadsheet and then enter the cell range you want to import as the second argument.
Step 2: To break the link, we do the following. Right-click the selected range and choose Copy. Then, choose Paste special > Values only. We do this in the sheet where we have copied the values.
Step 3: The formulas will be replaced by their current displayed values, effectively breaking the link. This method ensures your data remains unchanged even if the source file is edited or deleted.
You can see that the IMPORTRANGE function is gone and only the values have been replaced.
Examples
Now, that the whys and how’s have been addressed in the above sections, let us look at how to break different links in Google Sheets.
Example #1 – Breaking Links from Cell References in Google Sheets
When dealing with cell references, breaking the link is a relatively simple process. Let us look at this method. We have a cell here linked to another cell of a different sheet.
Step 1: As a first step, we identify the cells containing formulas like =Sheet2!A1. In this example we have a simple reference as shown below in Sheet4 with the formula
=Sheet3!A2
Step 2: Now, copy the data of the cells with the links by pressing Ctrl + C (or Cmd + C on a Mac).
Step 3: Paste the copied cells as values by right-clicking on the same selection and choosing “Paste special” and then “Paste values only.” Thus, the value is fixed without the formulas being removed, effectively breaking the link.
With this simple step, you ensure that the data does not change, regardless of any changes made in the source sheet.
Example #2 – Breaking IMPORTRANGE Links in Google Sheets
When we use IMPORTRANGE, we must be careful, as it involves connections between different spreadsheets. Let us look at how to approach this problem.
Step 1: Search and locate cells using the IMPORTRANGE function to pull in data from another spreadsheet.
Step 2: As in the previous example, let’s copy the imported range. Use Ctrl + C to copy the data.
Step 3: Let us right-click the selection, choose “Paste special,” then “Paste values only.” This will break the link, and we only have static values in the target cell with no link to the previous cell.
This method helps ensure that the data remains unchanged. Thus, it is useful when you are sharing your sheets or archiving them, as it breaks links, avoiding dependency.
Example #3 – Breaking Links in Charts in Google Sheets
If you have charts that are linked to formulas and change dynamically, it is linked to some data. However, sometimes you need the chart to reflect a static snapshot of your data at a particular moment. Let us look at an example of how to do it. Below is a chart which has been created dynamically. The Column D calculates the final sales after discount and is dependent on the formula:
=((C2/100)*B2)+B2
This is a dynamic chart. So, whenever we change the data for the discount or sales value, the chart gets update automatically as shown below.
Step 1: Now, let us remove this dependency. Select Column D which has formulas for dependency. Then, copy it using Ctrl + C.
Step 2: Paste the column again only with values. This can be done by selecting “Paste special” and then “Values only.”
Thus, by pasting the column as values, we convert the chart such that it no longer updates with changes in the underlying data. Thus, you get a visual representation without dynamic values.
Important Things To Note
- Before breaking any link, think about how it could affect your work. Breaking links is a one-way operation. Once done, it cannot be undone. Always save a backup copy of your spreadsheet before you break links, in case you need the original linked data later.
- If your formulas have been defined with names, breaking the link will not automatically remove those names. They must be manually deleted from the “Name Manager.”
- Google Sheets does not have a built-in feature to automatically break external links like Excel has. You must replace the formulas with static values to break the connection manually.
- Remember, breaking links means converting these formulas to plain values.
Frequently Asked Questions (FAQs)
We can break links in Google Sheets only in a manual way as unlike Excel, there’s no one-click “Break Links” feature. The method to break links in Google Sheets involves manually replacing formulas like IMPORTRANGE, GOOGLEFINANCE or references to other sheets with their cell values. This can be done by copying the cells and using Paste Special -> Values only.
Once you break links using the paste values option over a formula or link, it cannot be restored. It is important that you have a backup in this case, or the Undo must be done almost immediately. To reconnect later, you can keep a backup. Excel, on the other hand, often gives you the option to restore or manage links more directly.
Below are the different types of links we might have to break in Google Sheets:
1. IMPORTRANGE: We use it to pull data from another Google Sheet using a shared URL and range.
2. Using references across sheets: We often use formulas like =’Sheet4′!A1 to link data within the same file.
3. GOOGLEFINANCE: This function gives live stock prices, currency exchange rates, etc.
4. IMPORTDATA and IMPORTHTML: We use this to get data from public sources such as CSV files.
5. Linked charts or pivot tables: Visual elements are often dynamic based on data from other sources.
Download Template
This article must help understand Break Links in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is We learn why and how to break links in Google Sheets in different ways with examples.. We learn why and how to break links in Google Sheets in different ways with examples. You can learn more from the following articles. –
Leave a Reply