Does your Excel workbook frequently show the error broken links in Excel every time when you try to open it?
Well, this issue can be easily fixed once you find and remove Excel broken links. But for that, it’s compulsory to have knowledge of how to find Broken Links in Excel and ways to remove Excel broken links.
Don’t worry…! If you don’t know how to fix Excel broken links. This blog cover complete detail on different ways to find, fix, and remove broken Excel links.
Those who don’t know what this Excel broken link is or what causes broken links in Excel just check out the next subtopic.
What Is Excel Broken Link?
Excel sheet cells are often linked with several different files that are having relevant data like formulas, codes, etc. for one or many reasons. When these source files are gets deleted, relocated, removed, or corrupted then the link related to a specific workbook cell will automatically break down. After that these broken links can’t perform the targeted function anymore.
To fix Excel corrupt files, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
What Causes Broken Links in Excel?
Always keep this in mind that Linked objects are completely different from hyperlinks. So, the following procedure won’t help you to fix broken hyperlinks of Excel.
What causes broken links in Excel is the foremost associated question with Excel broken links.
Main there are two possible reasons associated with Excel broken links problem:
- The source folder is get deleted: After the deletion of the source folder, it’s impossible to track data that is linked with the cell.
- Files/folders shifted to another location: This Excel broken link issue also arises when the location of the linked file or folder is relocated.
After knowing about the causes of Excel broken links, let’s move to our next topic on how to find broken links in Excel.
How To Find Broken Links In Excel?
Method 1# Using Find And Replace Method
The easiest way to find Excel broken links is by using the Find and Replace method of Excel.
When the link is created with the source file each worksheet cell obtains a unique file extension. This extension signifies that the source file is linked with that respective cell.
So when the source file is relocated or deleted from the device then the worksheet starts showing Excel broken link error.
Follow the below-given steps to find broken links in Excel:
- First of all, open your Excel sheet which is showing broken link issues.
- After that press the Ctrl+F button from your keyboard. This will open the Find and Replace dialog box.
- In the opened dialog box of find and replace go to the Find what box. Here you have to enter your respective file extension which is linked with the cell.
- Now in the “within” box option, choose the option “Workbook”.
- After that in the box of Look in you have to choose the “Formulas” option.
- At last press the find all button.
After performing these steps, you will easily get Excel broken links.
Now you have to replace the problem having a link with the functional or correct new link.
Method 2# Through Cell Relationship Diagram
Excel Cell relationship diagram is mainly used to elaborate links association of any particular worksheet cells with other cells, worksheets, or any workbook.
To find the Excel broken links using the cell relationship diagram, you need to add a specific add-in within your Excel workbook. After then only the cell relationship diagram will show you the cell link up detail.
Here are the steps to find broken links in Excel using the cell relationship diagram:
- From the menu bar choose the Files > Options tab.
- In the opened window of Excel options hit the Add-ins tab.
- Now from the drop-down choose the COM add-in and press the ok button. In the opened com Add-in dialog box choose the “Inquire”.
- After that, you will see that the tab of Inquire will get added in the Excel Ribbon.
- From the diagram group, you have to choose Cell Relationship.
- The next window of the cell relationship diagram will appear on the screen.
- Choose the options as per your desire from the opened dialog box and then press the OK button.
- Very soon you will see the diagram will start appearing on your Excel screen.
- You can zoom in to see the links between one cell to another cell, or one cell to another worksheet.
- Here you will see the entire cell relationship diagram having all the included links. In this diagram, you can also check the broken links present in your Excel workbook.
I found this method as the best way to find the cell having the broken Excel links.
Method 3# Using Excel VBA
Another method to find Excel broken link is by using the Excel VBA. In this method, we will use the massive code to track the broken link related with any Excel workbook cell.
You can also restore the return value of the accessing source links to find whether links are actually broken or not.
For using the code, you have to add reference Microsoft XML V3:
Private Function CheckHyperlink(HypelinksCell As Range) As String
On Error GoTo ErrorHandler
Dim oHttp As New MSXML2.XMLHTTP30
oHttp.Open “HEAD”, HypelinksCell.Text, False
CheckHyperlink = oHttp.Status & ” ” & oHttp.statusText
CheckHyperlink = “Error: ” & Err.Description
Using the above-given code you can easily track the broken link detail of any worksheet cell. Once you know the exact location of the broken link, now proceed to fix Excel broken link.
How To Fix Broken Links In Excel?
Caution: This step can’t be reversed, so it will be better if you keep the backup copy of your Excel workbook before starting up the procedure.
- Open your Excel workbook having the broken link issue.
- Go to the Data tab and tap to the Edit Links option. If you won’t get the Edit Links option then it means your workbook doesn’t contain any links.
- Now from the Source file box, choose the broken link which you need to fix off.
- Hit the Change Source option.
- Search the location of the Excel file having the source of your linked data.
- Now choose the new source file and hit the Change Source option.
- Tap to the Close option.
How To Remove Broken Link In Excel?
When you break any link in Excel, all the formula referring to that particular source file automatically gets converted to its current value. Suppose, if you have used the formula =SUM([Budget.xls]Annual!C10:C25) and your result is 45 then your formula will get converted to 45 after breaking the link.
Follow the below steps to remove broken links in Excel:
- Open the Excel workbook showing the broken link.
- Now go to the Data tab, hit the Edit Links button.
- Within the Source file box, choose the broken link which you need to remove.
- Hit the Break Link button.
- Tap to the Close option.
If your Excel workbook is having a link with the data present in a workbook or any other file which is moved to another location. In that case, you can fix Excel broken link issue by updating the source file location in the linked cell.
But if in case you won’t find the source file then immediately stop Excel to update the link and permanently remove that link.