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.
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.
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.
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.
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 the Excel 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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
