How-to

Advanced Data Transformation And CleaningDeep dive

Extract Hyperlink URLs from Excel Cells with openpyxl

pandas reads the text a link displays, never the address behind it. Get the URLs out of Excel cells with openpyxl — inserted links, mailto links, links to other sheets and HYPERLINK formulas — and handle workbooks too large for normal mode.

Messy spreadsheets Reading Excel files openpyxl

A supplier list, a document register or a CRM export often has a column where each cell is a clickable link: the cell says "Acme Ltd", the link goes to https://acme.example.com/. Read that sheet with pd.read_excel and you get "Acme Ltd" — the address has vanished. Hyperlinks are stored next to the cell, not in it, and pandas reads only values. This guide, part of Extracting Data from Messy Excel Sheets, shows how to recover every kind of link with openpyxl and join the addresses back onto a DataFrame.

Prerequisites

Bash

pip install openpyxl pandas

The examples build the sample workbook above so you can run them as they are:

Python

from openpyxl import Workbook
from openpyxl.worksheet.hyperlink import Hyperlink

wb = Workbook()
ws = wb.active
ws.title = "Suppliers"
ws.append(["Supplier", "Website"])
ws.append(["Acme", "Acme Ltd"])
ws["B2"].hyperlink = "https://acme.example.com/"
ws.append(["Birch", "Birch Co"])
ws["B3"].hyperlink = "mailto:sales@birch.example"
ws.append(["Cedar", '=HYPERLINK("https://cedar.example.com/catalog","Cedar catalog")'])
ws.append(["Dune", "Internal"])
ws["B5"].hyperlink = Hyperlink(ref="B5", location="'Notes'!A1")
ws.append(["Elm", "no link"])
wb.create_sheet("Notes")
wb.save("links.xlsx")

Step 1 — See what pandas gives you

Python

import pandas as pd

print(pd.read_excel("links.xlsx"))

The Website column holds "Acme Ltd", "Birch Co", NaN for the formula cell (the file has no cached result because openpyxl wrote it) and "Internal". None of the addresses are there. There is no read_excel option that changes this; you need a library that reads the workbook's link records.

Step 2 — Read inserted links with cell.hyperlink

Links added with Excel's Insert → Link command — the most common kind — are loaded by openpyxl in normal mode and attached to each cell as a Hyperlink object:

Python

from openpyxl import load_workbook

wb = load_workbook("links.xlsx")
ws = wb["Suppliers"]
for cell in ws["B"][1:]:
    link = cell.hyperlink
    if link is None:
        print(cell.coordinate, "no link")
    elif link.target:
        print(cell.coordinate, "external:", link.target)
    else:
        print(cell.coordinate, "internal:", link.location)

Two attributes matter. target holds external addresses — web URLs, mailto: addresses, file paths and network shares. location holds a place inside the workbook, such as 'Notes'!A1 or a defined name. A link can have both — a URL plus an anchor within the target document — so check target first. The Cedar cell prints "no link", because a formula link is a different thing entirely.

Step 3 — Parse HYPERLINK formulas

Workbooks built from templates or exports often create links with =HYPERLINK(url, text). openpyxl keeps the formula as text in cell.value — as long as you do not load with data_only=True, which would give you only the cached display text. Pull the first argument out with a regular expression, and resolve it if it points at another cell:

Python

import re

FORMULA = re.compile(r'^=\s*HYPERLINK\(\s*("(?:[^"]|"")*"|[A-Z$]+[0-9$]+)\s*[,)]', re.IGNORECASE)

def formula_url(ws, cell):
    if not isinstance(cell.value, str):
        return None
    match = FORMULA.match(cell.value)
    if not match:
        return None
    arg = match.group(1).strip()
    if arg.startswith('"'):
        return arg[1:-1].replace('""', '"')
    try:
        return ws[arg.replace("$", "")].value     # =HYPERLINK(C4, "text")
    except (KeyError, ValueError):
        return None                               # an expression we cannot evaluate

print(formula_url(ws, ws["B4"]))                  # https://cedar.example.com/catalog
ws["D9"] = '=HYPERLINK("https://x.example/?id=" & A4, A4)'
print(formula_url(ws, ws["D9"]))                  # None

The pattern accepts a first argument that is either one quoted string or one cell reference, and only when a comma or closing bracket follows it. Quoted strings — the usual case — are returned directly, with Excel's doubled quotes unescaped. A plain cell reference is looked up. Anything more complex, such as =HYPERLINK("https://x.example/?id=" & A4, A4), does not match and returns None rather than a half-built address; for those, collect the formulas and handle them explicitly, or let Excel or LibreOffice calculate the sheet and read the concatenated address from a helper column.

Step 4 — Add a URL column to your DataFrame

Combine both sources into one function that returns a link for every row, then join it onto the frame pandas read:

Python

def link_for(ws, cell):
    if cell.hyperlink is not None:
        return cell.hyperlink.target or f"#{cell.hyperlink.location}"
    return formula_url(ws, cell)

def read_with_links(path, sheet, column):
    wb = load_workbook(path)
    ws = wb[sheet]
    header = [c.value for c in ws[1]]
    col_idx = header.index(column)
    urls = [link_for(ws, row[col_idx]) for row in ws.iter_rows(min_row=2)]
    frame = pd.read_excel(path, sheet_name=sheet)
    frame[f"{column}_url"] = urls
    return frame

df = read_with_links("links.xlsx", "Suppliers", "Website")
print(df[["Supplier", "Website_url"]])

Internal links are prefixed with #, the same convention a web page uses for anchors, so they are easy to tell apart from external ones. The approach relies on pandas and openpyxl seeing the same rows — true when the sheet has one header row and no leading blank rows. For sheets with a report title above the table, find the header first, as in Find the Header Row in an Excel Sheet with a Shifting Layout, and pass the same offset to both.

Step 5 — Classify the addresses

A link column is rarely uniform. Before you feed it to a crawler, a mail merge or a document download, sort each address into a kind so every consumer gets only what it can handle:

Python

from urllib.parse import urlparse

def link_kind(url):
    if not isinstance(url, str) or not url:
        return "none"
    if url.startswith("#"):
        return "internal"
    scheme = urlparse(url).scheme.lower()
    if scheme in ("http", "https"):
        return "web"
    if scheme == "mailto":
        return "email"
    if scheme == "file" or len(scheme) == 1 or url.startswith(("\\\\", "..", "./")):
        return "file"
    return "other"

df["link_kind"] = df["Website_url"].map(link_kind)
print(df["link_kind"].value_counts().to_dict())

The single-letter scheme test catches Windows paths such as C:\Reports\q1.pdf, which urlparse reads as scheme c. Counting the kinds is also a quick quality check: a supplier list that should contain only websites but reports twenty "file" links has been edited by hand, and those rows need a look before anything automated follows them.

Step 6 — Very large workbooks

read_only=True is the usual answer to memory problems, but read-only cells have no hyperlink attribute: the links are simply not loaded. When a file is too big for normal mode, read the link records straight from the package. Each sheet lists its links in a <hyperlinks> element, and external addresses sit in the sheet's relationships file:

Python

import zipfile
import xml.etree.ElementTree as ET

NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
      "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
      "p": "http://schemas.openxmlformats.org/package/2006/relationships"}

def links_from_package(path, sheet_part="xl/worksheets/sheet1.xml"):
    with zipfile.ZipFile(path) as z:
        rels_part = sheet_part.replace("worksheets/", "worksheets/_rels/") + ".rels"
        targets = {}
        if rels_part in z.namelist():
            for rel in ET.fromstring(z.read(rels_part)).findall("p:Relationship", NS):
                targets[rel.get("Id")] = rel.get("Target")
        links = {}
        for _, elem in ET.iterparse(z.open(sheet_part)):
            if elem.tag == f"{{{NS['m']}}}hyperlink":
                rid = elem.get(f"{{{NS['r']}}}id")
                links[elem.get("ref")] = targets.get(rid) or f"#{elem.get('location')}"
            elif elem.tag == f"{{{NS['m']}}}row":
                elem.clear()                      # keep memory flat on huge sheets
    return links

print(links_from_package("links.xlsx"))

iterparse streams the sheet, and clearing each row as it passes keeps memory low even for sheets with hundreds of thousands of rows. The part name sheet1.xml follows the sheet order in simple files, but the reliable mapping is in xl/workbook.xml and its relationships — check it when the workbook has had sheets deleted or reordered. A link's ref can also be a range such as B2:C2; expand it if one link covers several cells.

Common pitfalls

Loading with data_only=True. You lose formula text, so HYPERLINK formulas become their display text and the address is gone. Load a second copy normally if you need both.
Assuming every link is a web page. Targets can be mailto: addresses, file:/// paths or relative paths like ..\docs\spec.pdf. Classify them before requesting anything.
Validating links by fetching them all at once. Checking thousands of URLs in a tight loop will get you rate-limited; add delays and a timeout, and cache results.
Writing links back. When you save a report that should keep clickable links, assign cell.hyperlink and apply the built-in "Hyperlink" style so the cell looks like one — see openpyxl: Append Data to an Existing Excel Sheet for writing into existing workbooks.

Conclusion

Hyperlinks live beside cell values, so pandas cannot see them. With openpyxl in normal mode, cell.hyperlink.target gives external addresses and cell.hyperlink.location gives jumps inside the workbook; HYPERLINK formulas are formula text you parse yourself. Combine both into a URL column next to the frame pandas read, and for workbooks too large for normal mode, stream the <hyperlinks> records straight from the package.

Frequently asked questions

Why does pandas show the link text but not the URL? A hyperlink is stored separately from the cell value. pandas reads only the value — the text you see — so the address is lost. openpyxl in normal mode loads the hyperlinks and exposes them as cell.hyperlink.

Why is cell.hyperlink None for a cell that clearly has a link? Either the link is a HYPERLINK formula, which openpyxl stores as the formula text in cell.value, or the workbook was opened with read_only=True, which does not load hyperlinks at all.

How do I get the URL from a HYPERLINK formula? Open the workbook normally (not data_only) so the formula text is kept, then parse the first argument of HYPERLINK. If that argument is a cell reference rather than a quoted string, look up that cell's value.

Can I extract hyperlinks from an .xls file? Not with openpyxl, which reads only .xlsx and .xlsm. Convert the file to .xlsx first, for example with LibreOffice in headless mode, and then read the links.

Up one level: Extracting Data from Messy Excel Sheets — pulling structure out of irregular sheets.
Read Cell Colours and Font Styles as Data with openpyxl — other information pandas ignores.
Skip Hidden and Filtered Rows When Reading Excel — combining pandas with openpyxl row metadata.
Speed Up openpyxl with Read-Only Mode — what read-only mode keeps and what it skips.

More on messy spreadsheets