Tag: Hyperlinks in Excel

To quickly list all hyperlinks in an Excel sheet, you can use a VBA macro, since Excel doesn’t have a built-in formula to directly extract all hyperlinks from a sheet. Below are multiple methods depending on your need and comfort level.

---

✅ Method 1: Use VBA to List All Hyperlinks in the Sheet

📋 What it does:

This macro will loop through all cells in the sheet and list every hyperlink’s text and URL in a new sheet.

🔧 Steps:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code:

Sub ListAllHyperlinks()
    Dim ws As Worksheet
    Dim linkCell As Hyperlink
    Dim outputSheet As Worksheet
    Dim i As Long

    ' Create a new sheet for the hyperlink list
    Set outputSheet = ThisWorkbook.Sheets.Add
    outputSheet.Name = "Hyperlink List"

    ' Add headers
    outputSheet.Cells(1, 1).Value = "Text to Display"
    outputSheet.Cells(1, 2).Value = "Hyperlink Address"

    i = 2

    ' Loop through all sheets and all hyperlinks
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> outputSheet.Name Then
            For Each linkCell In ws.Hyperlinks
                outputSheet.Cells(i, 1).Value = linkCell.TextToDisplay
                outputSheet.Cells(i, 2).Value = linkCell.Address
                i = i + 1
            Next linkCell
        End If
    Next ws

    MsgBox "All hyperlinks listed in the sheet 'Hyperlink List'.", vbInformation
End Sub

4. Press F5 or run the macro from Excel.

---

📝 Output:

A new sheet named “Hyperlink List” will be created with two columns:

Text to Display	Hyperlink Address
Google	https://google.com
Training Site	https://trainingbyhimanshu.in

---

⚡ Method 2: Use Formula (If Hyperlink Is in a Cell)

You can extract a hyperlink URL from a cell using a User Defined Function (UDF) via VBA:

📌 VBA UDF to extract hyperlink address:

Function GetHyperlinkAddress(rng As Range) As String
    On Error Resume Next
    GetHyperlinkAddress = rng.Hyperlinks(1).Address
End Function

Use it like this in Excel:

=GetHyperlinkAddress(A2)

This works only if the hyperlink is inserted as a clickable link in the cell.

---

🚫 Limitation of Excel Formulas:

Built-in Excel formulas like =CELL("filename", A1) or =HYPERLINK(...) can’t extract the actual hyperlink address unless it’s added as a function result — which is rare.

---

🧠 Summary:

Method	Best For	Tools Needed
VBA Macro	Listing all links from any sheet	Basic VBA
VBA UDF	Extracting hyperlink from one cell	Formula + VBA
Manual	One or two links only	Copy-paste

---