Sending workbooks to clients is a common phenomenon if you are running a business. But, if your workbook contains links to other files, the recipient may not have access to those data. Possibilities are that the links may be broken if I moved the data file to a different location.
It will be a hectic situation fixing the problem without knowing the root cause.
By breaking the links between the workbooks, you can ensure that all the data is self-contained, and the recipient can use it with no issues.
But what if break links are not working in Excel?
In this guide, I will walk you through the steps to fix such problems easily so that you can maintain a good impression on your clients in the long run.
So, let’s get started.
What to do If Excel Break Links Not Working?
You have to unprotect the sheet or the workbook if the break links button gets grayed out. Normally, the button becomes non-functional and dimmed due to the worksheet being protected with a PIN number. Once you enter the correct password, the button for breaking the link will be functional.
We live in a world where people share so many Excel files with their friends, family without even proofreading or spell checking the contents inside. That is something utterly disappointing!
In fact, I saw one of my friends doing his research work, gathering data in multiple files, and linking them without knowing. And the second he emails his teacher all the files, the teacher responded to meet him in his room quickly. My friend found out that all the files were linked together.
He opened up the problem to me and asked,
How to Break Links Between Files in Excel?
To break links between two or more Excel Files, Go to the Data Option from the Ribbon and click on Edit Links. Select the Link you want to break the connection with and click on the Break Link Option. If the button for breaking the link is grayed out, unprotect the worksheet using the password.
No worries if you don’t know how to unprotect sheets in Excel.
Just go to the Review Tab and select the Unprotect sheet Option. Use the Password you used to protect the workbook.
I have seen different websites saying named ranges affect the break Link button being dimmed out. That’s not true at all.
The Break Link button is used to break links between cells in different worksheets or workbooks, and it is not affected by named ranges.
Named ranges in Excel are a way to assign a descriptive name to a specific cell, range of cells, formula, or constant value. Once a range is named, you can use that name instead of the cell reference in formulas and functions.
For example, let’s say you have a workbook with a sheet that contains a table of sales data. You want to calculate the total sales for a particular month, so you could select the range of cells that contain the sales data for that month, and then use the SUM function to add them up.
However, if you use named ranges, you can assign a name, such as March_Sales to the range of cells that contain the March sales data, and then use the name in the SUM formula, like this: =SUM(March_Sales).
6 Tricks to Fix Excel Break Links Not Working
For regular Excel users, minor glitches and major technical issues are really problematic. Keep in mind that breaking links won’t work for external links that have conditional formatting, data validation, and chart sources.
For such in-depth problems, I have some potential solutions in my sleeve.
Here are the ways to fix break links not working in Excel:
1. Unprotect The Excel Worksheet
Protecting sheets in Excel is important because it helps to prevent accidental or intentional changes to the data, formulas, or formatting of the sheet. When you protect a sheet, you can control which actions are allowed and which are restricted.
Like I said before, the break links option will be dimmed out if your workbook is protected with a passcode.
To remove the protection password from the worksheet, go to the Review Tab and click on Unprotect sheet. You will be asked for a password. Enter the passcode and click on OK.
Now that you removed the security from the worksheet, go to the Data Tab and click on Edit Links from the Queries and Connections Group. The Link Button will start working properly now. If not, go through the next method.
2. Break Data Validation Links
Like I said, breaking links won’t work for external links that have data validation. If you have some formula linked to the source file in the data validation field, it’s most likely that problems will arise. That’s why you have to break the links from the source file.
Follow the steps below to delete all data validation links:
- Click on the Data Tab from the Ribbon.
- Select Data Validation from the Data Tools Group.
- Remove the Source File formatted like a formula.
- Allow any value from the Data Validation criteria.
- Click on OK.
Apply these changes with the same settings to get rid of the break link problem.
Still can’t fix it?
Move on to the next step.
3. Remove External Links From Charts
This step only applies if you are dealing with charts, histograms, graphs, etc in any of your attached files. Creating charts is a great way of analyzing data and comparing between two or more datasets. It’s visually attractive and also time consuming. Yet, some problems like broken link still exist if any of the file is misplaced.
Let me provide an example. Suppose I made a visually attractive chart using VBA for analyzing the best products on Amazon currently. I am sending that file to a receiver A who doesn’t have VBA enabled in Excel. Most likely, the file won’t work properly. It’s because I didn’t remove external links from charts that contained VBA codes.
To remove all the external links:
- Right-click on the chart and Select Data.
- Go to the source workbook mentioned in the linked data range.
- Copy the whole dataset and paste it in a new worksheet in the source workbook.
- Right-click on the chart once again and click on Select Data.
- Change the reference of the new worksheet data from the chart data range box.
By performing this step, you linked the chart to your new workbook. And such, the broken link problem will be fixed immediately.
4. Make a Zip File
Nobody likes a disorganized file. It’s 90% easier to increase productivity if things are well organized. I call this method the Ultimate one. That’s because this method can’t go wrong.
It’s a two-step process. First, you have to make the .xlsx format a .zip file and then remove the external links folder. Finally, revert the format.
Follow the detailed procedure down below.
- Go to the folder where you saved the External File.
- Rename the file by right-clicking it.
- Change the extension from .xlsx to .zip.
- Open the zip file and the xl folder inside.
- Delete the externallinks folder.
- Change the file extension from .zip to .xlsx.
This will break all the links and your problems too.
5. Change The File Type
You can inter-convert the file type between xls and xlsx. The main difference between these two formats is that XLS is the default file format from Excel 97 version to Excel 2003. On the contrary, XLSX format is the default file format for Excel 2007 version and later.
If you enrolled into an Excel crash course, the first thing they teach are the names of each option and how to save Excel files. Not all courses teach you how to change the file format. Let’s learn how to do so in this guide.
Go to File and Select the Save As Option. A dialog box will open up, showing what will be the filename and format. Type any name of your preference and select the file format as Excel 97-2003 Workbook (*.xls). Check if the break link button is working or not.
6. Remove External Links of Conditional Formatting
External links used in conditional formatting formulas can affect Excel’s ability to break links because they create dependencies between the source workbook and the destination workbook.
What happens is that Excel prompts the user to update the links if the source file is moved or renamed. However, if the file contains conditional formatting that refers to an external source, Excel may not be able to break the links properly.
Let’s move on to the steps now.
- Go to the Home Tab and select Conditional Formatting.
- Select Manage Rules and Delete the Rule that contains the external links.
Now that you know all the six methods, you can avoid the broken link dimmed out problem easily.
Final Words
In short,
External links used in conditional formatting, data validation, or charts like histogram, pie chart, can create dependencies between workbooks in Excel, which can affect the ability to break links properly.
If the conditional formatting formula contains cell references that point to an external workbook, Excel may not break the link properly, and the user may receive an error message.
