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.

1. Why Won't Excel Break Links

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

Click the link you want to break. You'll see a list of links that are active in your spreadsheet, single-clicking one will highlight it. If you want to select more than one link from the list, hold the CTRL (Windows) or CMD (Mac) key. If you want to select all the links, press CTRL + A (Windows) or CMD + A (Mac). Click Break Link. How to find broken links in Excel that can't be broken with 'Break Links'?Helpful? How to find broken links in Excel that can't be broken with 'Break Links'?Helpful? Please support me on. I would like to know if it is possible to link a pdf file into excel while also have it open to a specific page. Hyperlinks do not work because the links break when I email my excel file with all the links. The only other option I know is to embed the pdf file but I do not know how to make it open to a desired page. Any help appreciated. Basically, two things can happen here: One you might want to say something loudly I refrain from using that word. Second, you put extra effort and fix all the formulas. So, to avoid all this hassle we have written this in-depth article covering most of the reasons for excel formulas not working. Solutions to fix Excel hyperlinks not working problem. Open Excel Workbook -Go to File-Options-Advanced. Scroll down to find the General tab and click on Web Options. Web Options Window pops-up. On Web Options, pop-up, Click on Files and Uncheck the box for “Update Links on save”. Click OKbutton and your option is saved.

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.

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.

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

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.

Fanie has a workbook with two worksheets. In the first worksheet she created hyperlinks to the second worksheet. Those hyperlinks worked fine until Fanie saved and reopened the workbook. The second time the hyperlinks were all gray and would no longer work, and she's at a loss as to why this occurred.

There are a few things you can try here. First, you need to make sure that the problem isn't due to something you inadvertently did in your workbook. Check to make sure that you didn't rename the second worksheet—the one that is the target of the hyperlinks. When you create hyperlinks, each of them references the name of the worksheet you specify as the target. If you later rename the worksheet, then the hyperlinks may not work as expected. If this is the case, you will need to either change the name of the worksheet back to its original name, or you will need to recreate the hyperlinks to point to the renamed worksheet.

If that is not the problem, you may want to turn off an option in Excel that causes the program to check hyperlinks whenever the workbook is saved. (There have been some reports that Excel disables the links a bit too early sometimes, which could be the behavior that Fanie is seeing.) Do this by following these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel 2013 display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Advanced.
3. Scroll through the available options until you see the General section. (See Figure 1.)

Figure 1. The Advanced options of the Excel Options dialog box.

4. Click on the Web Options button. Excel displays the Web Options dialog box.
5. Make sure the Files tab is selected. (See Figure 2.)

Figure 2. The Files tab of the Web Options dialog box.

6. Clear the Update Links on Save check box.
7. Click on OK to close the Web Options dialog box.
8. Click on OK to close the Excel Options dialog box.

Why Won't Excel Break Links

Finally, the problem may not even be related to Excel it is possible that the program is getting a bit confused if you changed your default browser after you originally created the hyperlinks—especially if you deleted whatever browser used to be your default. If you think this may be the root of the problem, the only thing you can do is to recreate the hyperlinks.