Guide

Formatting And Charting Excel Reports With PythonDeep dive

Add Hyperlinks to Excel Cells with Python

Attach web, sheet, email and file links with openpyxl and xlsxwriter — including the style line that makes them look clickable, and when the HYPERLINK formula is the better choice.

Links turn a multi-sheet workbook from a stack of tabs into something navigable: a contents page that jumps to each section, an identifier column where every row opens the record it came from, a footer pointing back to the dashboard. openpyxl writes all three, and the one thing it does not do automatically is make them look like links. This guide is part of Styling Excel Cells with openpyxl.

Prerequisites

Bash

pip install openpyxl xlsxwriter

A link to a web address

Python

from openpyxl import Workbook

book = Workbook()
sheet = book.active
sheet.title = "Orders"

cell = sheet["A1"]
cell.value = "Open the order in the CRM"
cell.hyperlink = "https://crm.example.com/orders/1001"
cell.style = "Hyperlink"

book.save("links.xlsx")

Assigning a string to cell.hyperlink is the short form; openpyxl builds the relationship for you. cell.style = "Hyperlink" is the line people leave out — without it the cell is clickable and looks exactly like plain text, which means nobody clicks it.

Linking a whole column

The realistic case is one link per row, built from an identifier.

Python

from openpyxl import Workbook

orders = [(1001, "A-100", 12400.0), (1002, "B-200", 9800.5), (1003, "C-300", 15320.25)]

book = Workbook()
sheet = book.active
sheet.append(["Order", "SKU", "Revenue"])

for order_id, sku, revenue in orders:
    sheet.append([order_id, sku, revenue])
    cell = sheet.cell(row=sheet.max_row, column=1)
    cell.hyperlink = f"https://crm.example.com/orders/{order_id}"
    cell.style = "Hyperlink"

sheet.column_dimensions["A"].width = 12
book.save("orders-linked.xlsx")

Writing the value first and then attaching the link keeps the cell's displayed text as the order number rather than the URL, which is what a reader wants. The alternative — writing the URL as the value — produces a column of unreadable addresses.

Internal links: a contents sheet

Links within a workbook use a location rather than a URL, and they are what make a twelve-tab report navigable.

Python

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

book = Workbook()
contents = book.active
contents.title = "Contents"
contents["A1"] = "Sections"

for index, name in enumerate(["Summary", "North", "South", "West"], start=2):
    book.create_sheet(name)
    cell = contents.cell(row=index, column=1, value=name)
    cell.hyperlink = Hyperlink(ref=cell.coordinate, location=f"'{name}'!A1")
    cell.style = "Hyperlink"

    back = book[name]["A1"]
    back.value = "← Contents"
    back.hyperlink = Hyperlink(ref="A1", location="'Contents'!A1")
    back.style = "Hyperlink"

contents.column_dimensions["A"].width = 24
book.save("navigable.xlsx")

Quoting the sheet name in the location handles the ones containing spaces, and it costs nothing on the ones that do not. The reciprocal "back to contents" link in each sheet is what turns a list of jumps into actual navigation — it is three lines and it is the difference between a workbook people explore and one they scroll.

The HYPERLINK formula, and when to prefer it

Python

sheet["D2"] = '=HYPERLINK("https://crm.example.com/orders/" & A2, "Open " & A2)'

A formula is the better choice when the target depends on other cells and those cells may change: the link follows the value automatically. A real hyperlink is better when the target is fixed, because it survives being sorted, copied into another workbook, or opened in a viewer that does not calculate formulas.

A practical rule: use the relationship for links you generate once, and the formula for links that have to track a cell the recipient will edit.

Links in xlsxwriter

For a workbook built from scratch, write_url does both halves in one call — the link and the appearance.

Python

import xlsxwriter

book = xlsxwriter.Workbook("links-xw.xlsx")
sheet = book.add_worksheet("Orders")
link = book.add_format({"font_color": "#4338CA", "underline": 1})

sheet.write_url("A1", "https://crm.example.com/orders/1001", link, "Order 1001")
sheet.write_url("A2", "internal:'Summary'!A1", link, "Go to summary")
sheet.write_url("A3", "mailto:reports@example.com", link, "Email the team")
sheet.write_url("A4", "external:reports/august.xlsx", link, "August workbook")
book.add_worksheet("Summary")
book.close()

The four prefixes cover every target type: a bare URL, internal: for another sheet, mailto: for an address, and external: for another file relative to this one. That last is the one worth knowing about — a monthly report can link to its predecessor without an absolute path that breaks the moment the folder moves.

Adding links to a sheet pandas wrote

The common shape is a DataFrame written with to_excel and then decorated, and the order matters: attaching links before the data is written means they are overwritten, since to_excel rewrites the cells it covers.

Python

import pandas as pd
from openpyxl.styles import Font

orders = pd.DataFrame({
    "Order": [1001, 1002, 1003],
    "SKU": ["A-100", "B-200", "C-300"],
    "Revenue": [12400.0, 9800.5, 15320.25],
})

with pd.ExcelWriter("orders.xlsx", engine="openpyxl") as writer:
    orders.to_excel(writer, sheet_name="Orders", index=False)
    sheet = writer.sheets["Orders"]
    link_font = Font(color="4338CA", underline="single")
    for offset, order_id in enumerate(orders["Order"], start=2):
        cell = sheet.cell(row=offset, column=1)
        cell.hyperlink = f"https://crm.example.com/orders/{order_id}"
        cell.font = link_font

Reaching through writer.sheets means the decoration happens before the file is saved, so there is no second open-and-rewrite pass. Using an explicit Font rather than the named Hyperlink style is worth doing here because it keeps the colour under your control — the built-in style follows the workbook theme, which can be an unexpected purple in a template somebody else designed.

The wider pattern of writing with pandas and finishing with openpyxl is set out in openpyxl vs pandas for Excel Automation.

Checking that the links point somewhere

A workbook full of links to a system that has been renamed is worse than one with none, because the reader trusts them. Where the targets follow a pattern, a short check before distribution is cheap insurance — and for internal links it is entirely local.

Python

from openpyxl import load_workbook

def broken_internal_links(path: str) -> list[str]:
    book = load_workbook(path)
    names = set(book.sheetnames)
    problems = []
    for sheet in book.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                link = cell.hyperlink
                if link is None or not getattr(link, "location", None):
                    continue
                target = link.location.split("!")[0].strip("'")
                if target not in names:
                    problems.append(f"{sheet.title}!{cell.coordinate} -> missing sheet {target!r}")
    return problems

print(broken_internal_links("navigable.xlsx"))

Internal links are the ones worth checking automatically, because they break for a reason entirely within your control — a renamed or removed sheet — and Excel gives no warning until somebody clicks. Folding this into the pre-send checks in Validate an Excel Report Before Sending It costs one function call.

Common pitfalls

Symptom	Cause	Fix
The link works but looks like plain text	Only `hyperlink` was set	Also set `cell.style = "Hyperlink"`
The cell shows a long URL	The URL was written as the value	Write the label as the value, attach the link separately
Excel reports a broken internal link	The target sheet does not exist, or the name is unquoted	Create the sheet first; quote names with spaces
Links vanish after a rewrite	The cell was overwritten by a later `to_excel`	Attach links after the data is written
Only 65,530 links are written	Excel's per-sheet hyperlink limit	Use the HYPERLINK formula past that count
Relative file links break	The workbook moved	Prefer `external:` relative targets, or absolute URLs

Removing links you did not want

Data pasted from a web page or an email export often arrives with hundreds of unwanted mailto: and http: links attached. Stripping them is a short loop, and worth doing before the sheet is distributed.

Python

from openpyxl import load_workbook

book = load_workbook("pasted.xlsx")
removed = 0
for sheet in book.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if cell.hyperlink is not None:
                cell.hyperlink = None
                cell.style = "Normal"
                removed += 1
book.save("cleaned.xlsx")
print(f"removed {removed} hyperlink(s)")

Resetting the style as well as the link is the part that completes it — a cell left in the Hyperlink style still looks clickable, which is arguably worse than one that is.

Performance and scale

Each hyperlink is a relationship entry in the file's XML, so a sheet with fifty thousand of them is noticeably larger and slower to open than the same data without. Excel's own limit is 65,530 per worksheet, and files approaching it behave badly well before they reach it.

Where a large table needs links on every row, the HYPERLINK formula is the lighter option: it is a formula rather than a stored relationship, so the file stays small and the limit does not apply.

Python

for row in range(2, 50002):
    sheet.cell(row=row, column=4).value = (
        f'=HYPERLINK("https://crm.example.com/orders/"&A{row}, "Open")'
    )

The trade is that the links only work where formulas are evaluated, which rules out some viewers and any downstream tool reading values rather than formulas — the distinction covered in Read Formula Results with openpyxl data_only.

Conclusion

Set both cell.hyperlink and cell.style = "Hyperlink", and keep the readable label as the cell value rather than the URL. Use a Hyperlink with a quoted location for internal jumps, add the reciprocal link back so navigation works in both directions, and prefer write_url with its internal: and external: prefixes when building with xlsxwriter. Past a few thousand rows, switch to the HYPERLINK formula to keep the file small.

Frequently asked questions

Why does my hyperlink look like ordinary text? Setting cell.hyperlink attaches the link but does not change the appearance — Excel's blue underline comes from the Hyperlink cell style. Set cell.style = 'Hyperlink' as well, or apply a font with a colour and underline yourself.

What is the difference between the HYPERLINK formula and cell.hyperlink? cell.hyperlink writes a real hyperlink relationship into the file, which survives sorting and is what Excel creates when you insert a link by hand. The HYPERLINK formula is a formula whose result happens to be clickable, and it recalculates — useful when the target is computed from other cells.

How do I link to another sheet in the same workbook? Use a location-style target: cell.hyperlink = Hyperlink(ref=cell.coordinate, location="'Summary'!A1"). Quote the sheet name when it contains a space, and remember the sheet must exist or Excel reports a broken link on click.

Can I remove every hyperlink from a sheet? Yes — iterate the cells and set cell.hyperlink = None. Worth doing on a sheet built from pasted content, where a column of email addresses arrives as several hundred mailto links nobody wants.

Up one level: Styling Excel Cells with openpyxl — the wider styling API these cells use.
Add a Table of Contents Sheet with Hyperlinks in Excel — the navigation pattern built out in full.
Apply a Reusable Style Theme Across an Excel Report — named styles, of which Hyperlink is one.
Add Comments and Notes to Excel Cells with Python — the other way to attach context to a cell.
Build a Dashboard Sheet with Charts from Multiple Tabs — where internal links earn their place.