Excel Breaking Links Not Working

admin

When we work with several excel files and use formula to get the work done, we intentionally or unintentionally create links between different files. Normal formula links can be easily broken by using break links option.

Locate the link that you wish to remove and click on the 'break link option.' By so doing, the link to the file will be broken. This is a general solution, but there are cases where the break link option dims out and fails to work. Sometimes, the break link button may work but doesn't break the link. Let's take a look at each of the cases. Excel Questions. VBA Break Link Loop not working. Break links loop not working K. Kyddrivers New Member. Joined Mar 22, 2013 Messages 42. Jun 18, 2020 #1. Links and Linking in Excel: 0: November 3rd 05 09:47 AM: Deleting links to other spreadsheets: KarenH: Excel Worksheet Functions: 2: October 28th 05 04:09 AM: Working days left in the month compared to previous months: qwopzxnm: Excel Worksheet Functions: 8: October 24th 05 08:00 PM: excel links update not working in auto, calculations in auto. To break links click DATA RIBBON – (CONNECTIONS sub menu) EDIT LINKS. You will see a list of external links that your current file is linking to. You can click each of the files and then click BREAK LINK. If BREAK LINK is grayed out then go back to step 1 as you must still have a protected sheet in your workbook.

When we break links between two files, the values that were dynamically pulled by linked file become static. And this is what we want. But some times the break links option doesn't work properly. For such cases, we have this article. This article can help you if you have problem breaking links in Excel file.

Break Links Between Files Effectively

Now if you are working with multiple files and your final file has links to other files that you don't want to keep, just follow these steps:

Go to Data --> Edit Links. Here you will see all the links to the file. Select the link and click on the break links option.

This should break the link to selected file.

Now sometimes it happens that the break link button is dimmed out and does not work. And sometimes the break link option button is working but id does not break links. Let's see each case and solve them.

1. Break links button is dimmed out

Sometimes when you click on the edit links menu for breaking the links between the excel files, the break links button gets dimmed out and becomes non functional. It happens when the sheet or the workbook is protected. You need to unprotect the sheet or the workbook.

Go to Review and click on the button unprotect sheet.

It is possible that the sheet is protected using a password. You will need to enter that password. Once the sheet is unprotected, the button for breaking the link will be functional.

This was an usual case. There are some unusual cases of breaking links that does not seem to work properly.

2. Breaking link dialog box pops up every time workbook is opened.

Breaking

Now this is an rare case but annoying one. Sometimes we break the links to the source file but when reopen the file main file, it again shows the break link dialog box. The links are not actually removed.

It happens when the file has data validations linked to other files. Normal link breaking does work on these. In such cases do this.

1. First Duplicate the file and save it. Now start working on the duplicate file so that your main file is safe.

Excel Breaking Links Not Working

2. Check the data validation if they contain links to external files. Remove them.

2. Similarly check the conditional formatting formulas you have used. If they contain links to external files then remove them to. Make these conditional formatting local.

3. Check Chart series ranges. If they contain ranges from different workbook, remove them. Do check the headers. We often link the headers to a cell.

4. Check the named ranges used from different workbook. This is a rare one. But to be on the safe side check for the named ranges if they contain range from external file. Remove or amend them to suit you.

When you have checked all these. Try again to remove the links and save the duplicate file. I am sure that all the external links that you wanted to remove, will be removed from the main file

Breaking

I hope this was helpful. If you have any doubts regarding this topic or any other Excel/VBA related topic, ask me in the comments section below. Till than keep excelling.

Related Articles:

How to use the REPT function in Excel | The Excel REPT function is a simple function, that prints a given text to given times. In this article, we will explore how REPT function works, how REPT functions can be used, and where REPT function is used best, what tricks can be done using this function

How to Find the Last ROW of data in Excel | To find the last row of data in an excel range we can use the REPT function and MATCH function together.

How to Remove leading and trailing spaces from text in Excel | The trailing spaces disturb your data set and it is necessary to remove any trailing or leading space from the text in excel. This formula removes all trailing spaces.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use theExcel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

How do you fix a broken link in Excel?

Fix a broken link

  1. Open the workbook that contains the broken link.
  2. On the Data tab, click Edit Links.
  3. In the Source file box, select the broken link that you want to fix.
  4. Click Change Source.
  5. Browse to the location of the file containing the linked data.
  6. Select the new source file, and then click Change Source.

Why do my links keep breaking in Excel?

Excel cells may often link to other workbooks to pull relevant information from there. When a source workbook gets deleted, relocated, or damaged, external references to that file break down and your formulas start returning errors.

How do I stop hyperlinks breaking in Excel?

Solutions to fix Excel hyperlinks not working problem

  1. Open Excel Workbook ->Go to File->Options->Advanced.
  2. Scroll down to find the General tab and click on Web Options.
  3. Web Options Window pops-up.
  4. On Web Options, pop-up, Click on Files and Uncheck the box for “Update Links on save”
  5. Click OKbutton and your option is saved.

How do I remove all hyperlinks from Excel?

Select all cells that contain hyperlinks, or press Ctrl+A to select all cells. Right-click, and then click Remove Hyperlinks.

How do you increase the size of an Excel spreadsheet?

Maximum Memory or File Size Exceeded

  1. Delete any tables you don’t need. Delete them from the Power Pivot window. See Start Power Pivot in Microsoft Excel add-in to open the window.
  2. Re-import data, setting filters to reduce the number of rows added to the data model. To avoid confusion in the data model, delete existing table before re-importing them.

What to do when Excel file is too large?

Working
  1. Do you want to know how to reduce your Excel file size?
  2. First, audit your large Excel file.
  3. Tip #1: Remove conditional formatting.
  4. Tip #2: Crop each worksheet to its used range.
  5. Tip #3: Remove unnecessary formulas.
  6. Tip #4: Compress pictures in Excel.
  7. Tip #5: Clear data formatting.
  8. Tip #6: Delete watches.

How do I delete a row in Excel?

If you don’t need any of the existing cells, rows or columns, here’s how to delete them:

  1. Select the cells, rows, or columns that you want to delete.
  2. Right-click, and then select the appropriate delete option, for example, Delete Cells & Shift Up, Delete Cells & Shift Left, Delete Rows, or Delete Columns.

How do you delete multiple columns in Excel not next to each other?

Hold down the Command key, and select each of the other rows of the group. After all rows are selected, right-click or control-click, and click Delete from the popup menu. Or, after all rows are selected, choose Edit from the main menu, and click Delete.

How do you make excel not infinite columns?

Press Ctrl + Shift keys and press down arrow and select all rows beneath. Now do a similar thing with columns. After selecting all, go to Format >> column >> Hide.

How do you hide infinite columns in Excel?

Hide Unused Rows and Columns

  1. Select the row header just beneath the used area of your spreadsheet, where you want to start hiding rows.
  2. Press Ctrl + Shift + Down Arrow. This will highlight everything from your selected row through the bottom of the worksheet.
  3. From the worksheet’s Format menu, choose Row, then Hide.