How To Find And Fix Excel Broken Links?

repair ms Excel — Tue, 15 Dec 2020 10:48:49 +0000

Summary:

This blog emphasizes how to fix Excel broken links and different ways to fetch and remove broken Excel links. So, without wasting any more time just go through this post to explore each and every detail regarding Excel Broken Link.

What Is Excel Broken Link?

Excel spreadsheet cells can contain linked files that have relevant data like formulas, codes, etc. when such source files is been removed, deleted, or corrupted then the link assigned in the specific workbook will automatically get break down.

After that broken link won’t perform the targeted function anymore.

What Causes Broken Links in Excel?

Don’t get confused between Linked objects and hyperlinks. Both are quite different from hyperlinks.

So, the following procedure won’t help you to fix broken hyperlinks of Excel. Broken links in Excel is the foremost associated query with Excel broken links.

Two possible reasons are there behind Excel broken links problem.

The source folder is get deleted:

It's quite difficult to track data that is allotted in the cell; mainly after the source folder deletion.

Files/folders shifted to another location:

Broken excel links also due to the relocation of linked folders or files.

After getting information about the reasons for Excel broken links. It’s time to move onto the next topic i.e. how to find broken links in Excel.

How To Find Broken Links In Excel?

Method 1# Using Find And Replace Method

Try the following fixes to find and replace broken links in Excel.

Open an Excel spreadsheet that is frequently showing you the broken link.
Hit the Ctrl+F button to open your Find and Replace dialog box.
From the opened find and replace dialog box, go to the Find What box. Just enter the respective file extension that is linked with the cell.
Now from the “within” box option, you need to choose “Workbook”.
Within the Look in a box just hit the “Formulas” option.
Now press the find all button.

After these steps, you will get all the broken Excel links.

Now you need to replace problem causing links with the right or functional new link.

You may also like: 5 Way To Fix Excel Freeze Panes Not Working Issue

Method 2# Using Excel VBA

Another option that is left to fetch Excel broken link is by making use of Excel VBA.

Basically in this method, we are going to use the massive code for tracking up the broken link if it’s present in your Excel workbook.

The return value of the accessed source link can also be stored for finding whether the links are actually been broken or not.

To use the code, you need 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
oHttp.send
CheckHyperlink = oHttp.Status & " " & oHttp.statusText
Exit Function
ErrorHandler:
CheckHyperlink = "Error: " & Err.Description
End Function
Sub Test()
Debug.Print CheckHyperlink(Range("A1"))
End Sub

Just use these given codes to track the broken excel links in complete of your worksheet cells. After knowing the exact location of broken links, let’s move on to the next very important topic i.e fix Excel broken link.

Read more: 4 Ways To Import Excel Contacts To iPhone

How To Fix Broken Links In Excel?

Note: be careful while performing these steps, because this can’t be reversed. It’s better to make a complete backup of your Excel workbook before starting up this procedure.

Open the Excel workbook in which you are getting the broken link issue.
Tap the Data tab and choose the Edit Links option. If you are not getting the Edit Links option then this means there is no link in the Excel workbook.
From the Source file box, hit the broken link option which needs to get fixed.
Click the Change Source option.
Search for the Excel file location in which the source of your linked data is given.
Hit the new source file and tap the Change Source option.
Tap the Close option.

Now that you know how to find and fix Excel broken links. So you don’t need to suffer with this anymore.