Write Clickable Links from a DataFrame Column to Excel

A report of support tickets, orders or documents usually has a URL for each row, and readers expect to click it. Whether the column comes out clickable depends on the engine pandas uses: with xlsxwriter, URLs become links automatically; with openpyxl, they are plain text. Neither shows friendly text like "Open ticket" instead of a long address. This page, part of Writing DataFrames to Excel with Pandas, shows three ways to write links from a DataFrame, which to choose, and two limits that silently lose data if you do not plan for them.

Prerequisites

pip install pandas openpyxl xlsxwriter

import pandas as pd
from openpyxl import load_workbook

tickets = pd.DataFrame({
    "ticket": ["T-101", "T-102", "T-103"],
    "status": ["open", "waiting", "closed"],
    "url": ["https://support.example.com/t/101",
            "https://support.example.com/t/102",
            "https://support.example.com/t/103"],
})

def describe(path, row=2):
    ws = load_workbook(path).active
    return [(c.value, c.hyperlink.target if c.hyperlink else None) for c in ws[row]]

Step 1 — Let xlsxwriter detect the URLs

With the xlsxwriter engine, pandas writes each string through xlsxwriter's general write() method, which recognises strings that start with http://, https://, ftp://, file:// or mailto: and writes them as hyperlinks with Excel's blue, underlined hyperlink style:

tickets.to_excel("auto_links.xlsx", index=False, engine="xlsxwriter")
print(describe("auto_links.xlsx"))
# [('T-101', None), ('open', None), ('https://support.example.com/t/101', 'https://support.example.com/t/101')]

tickets.to_excel("plain.xlsx", index=False, engine="openpyxl")
print(describe("plain.xlsx"))
# [('T-101', None), ('open', None), ('https://support.example.com/t/101', None)]

That is the whole solution when the raw URL is an acceptable thing to show. It is also a behaviour to know about when it is not wanted — a column of example URLs in documentation, say. Turn it off with engine_kwargs={"options": {"strings_to_urls": False}} on the ExcelWriter.

Step 2 — Show friendly text with HYPERLINK formulas

To show "Open T-101" and link to the address, build an Excel HYPERLINK formula in the column. Both engines write strings starting with = as formulas:

def hyperlink(url, text):
    url = str(url).replace('"', '""')
    text = str(text).replace('"', '""')
    return f'=HYPERLINK("{url}","{text}")'

out = tickets.assign(link=[hyperlink(u, f"Open {t}") for u, t in zip(tickets["url"], tickets["ticket"])])
out = out.drop(columns="url")
out.to_excel("formula_links.xlsx", index=False)
print(describe("formula_links.xlsx")[2])

Doubling any quotes inside the URL or text keeps the formula valid. The formula approach has two costs. The cell's value is now a formula, so anyone reading the file back with pandas gets the formula text, not the URL. And Excel limits text strings inside a formula to 255 characters, so long tracking URLs do not fit. Cells written this way also lack the hyperlink style unless you add it, though Excel applies it once the formula is edited.

Step 3 — Attach links with openpyxl after writing

The most flexible method writes the display text with pandas, then opens the workbook with openpyxl and attaches the URL to each cell. The cell keeps a plain value, the link can point anywhere, and the built-in "Hyperlink" style gives it the familiar look:

report = tickets.assign(link=[f"Open {t}" for t in tickets["ticket"]])
report.drop(columns="url").to_excel("attached_links.xlsx", index=False)

wb = load_workbook("attached_links.xlsx")
ws = wb.active
link_col = [c.value for c in ws[1]].index("link") + 1
for row, url in enumerate(tickets["url"], start=2):
    cell = ws.cell(row=row, column=link_col)
    cell.hyperlink = url
    cell.style = "Hyperlink"
wb.save("attached_links.xlsx")
print(describe("attached_links.xlsx")[2])
# ('Open T-101', 'https://support.example.com/t/101')

Iterating over tickets["url"] in the same order as the rows were written keeps text and links aligned; if you sort or filter before writing, sort the URL column the same way, or keep it in the DataFrame and read it from there. Reading the file back with openpyxl, cell.hyperlink.target returns the URL — Extract Hyperlink URLs from Excel Cells with openpyxl covers the reverse direction.

Which method to use comes down to three questions: must the display text differ from the URL, will anyone read the file back with code, and which engine does the job already use? The answers usually point at one method clearly:

A report that is both read by people and processed by another script later — a weekly export that a reconciliation job picks up, for instance — is the case where the choice matters most. Attached links keep the cell's value clean for the script and the link intact for the people.

Step 4 — Link to other sheets in the same workbook

The same hyperlink attribute takes an internal location: a # followed by a sheet and cell reference. This is how a summary row links to its detail sheet. Quote the sheet name, because names with spaces need it:

with pd.ExcelWriter("internal_links.xlsx") as writer:
    tickets[["ticket", "status"]].to_excel(writer, sheet_name="Summary", index=False)
    for t in tickets["ticket"]:
        pd.DataFrame({"ticket": [t], "history": ["created"]}).to_excel(writer, sheet_name=f"Ticket {t}", index=False)

wb = load_workbook("internal_links.xlsx")
ws = wb["Summary"]
for row in range(2, ws.max_row + 1):
    cell = ws.cell(row=row, column=1)
    cell.hyperlink = f"#'Ticket {cell.value}'!A1"
    cell.style = "Hyperlink"
wb.save("internal_links.xlsx")
print(wb["Summary"]["A2"].hyperlink.location or wb["Summary"]["A2"].hyperlink.target)

Add a Table of Contents Sheet with Hyperlinks in Excel builds a full navigation sheet from this pattern.

Step 5 — Plan for the two limits that lose data

URL length. Excel limits a hyperlink to 2,079 characters. xlsxwriter enforces this by warning and leaving the cell empty:

import warnings

long_url = "https://example.com/search?q=" + "x" * 2100
with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    pd.DataFrame({"url": [long_url]}).to_excel("long.xlsx", index=False, engine="xlsxwriter")
print(len(caught), load_workbook("long.xlsx").active["A2"].value)
# 1 None

The warning scrolls past in a job log and the value is gone. Check lengths before writing, and write over-long URLs as plain text (with strings_to_urls off) or shorten them:

too_long = tickets["url"].str.len() > 2079
print(too_long.sum(), "URL(s) exceed Excel's limit")

Formula injection. Because both engines write strings beginning with = as formulas, a value typed by a user — a ticket title of =HYPERLINK("http://evil.example","Click") — becomes a live formula in your report. For columns that hold user-supplied text, stop xlsxwriter from interpreting them:

titles = pd.DataFrame({"title": ["Printer jam", '=HYPERLINK("http://evil.example","Click")']})
with pd.ExcelWriter("safe.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    titles.to_excel(writer, index=False)
print(load_workbook("safe.xlsx").active["A3"].data_type)
# 's' — stored as text

openpyxl has no such switch, but you can force a cell to be text after assigning it: setting data_type = "s" stores the value as a string, formula-looking or not.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for row, title in enumerate(titles["title"], start=1):
    cell = ws.cell(row=row, column=1, value=title)
    if isinstance(title, str) and title.startswith("="):
        cell.data_type = "s"
wb.save("safe_openpyxl.xlsx")
print(load_workbook("safe_openpyxl.xlsx").active["A2"].data_type)
# 's'

Write your own HYPERLINK formulas in a separate step, after user text has been neutralised, so the only formulas in the sheet are the ones you meant to create.

Common pitfalls

• Expecting links from the openpyxl engine. It writes URLs as text; attach links yourself or switch engines.
• Losing links when reading back. pd.read_excel returns cell values only; a HYPERLINK formula's target or an attached link is not in the DataFrame.
• Unquoted sheet names in internal links. #Ticket T-101!A1 fails; #'Ticket T-101'!A1 works.
• Links to local files. file:// paths and network-share links work only for readers who can reach the same path; for a report that is emailed, link to a shared location such as SharePoint or an intranet page instead.
• Mixing URL and text columns. Keep the URL in the DataFrame until the links are attached, then drop it if readers should not see it.

Performance notes

Excel allows at most 65,530 hyperlinks per worksheet, and xlsxwriter warns and writes plain strings past that point. For exports larger than that, link only the rows that matter or split the data across sheets. Attaching links with openpyxl costs roughly the same as any per-cell pass — a second or two per 100,000 cells.

Conclusion

With the xlsxwriter engine, URL strings become links automatically; with openpyxl they stay text. Use =HYPERLINK() formulas for friendly text when the URLs are short, attach links with cell.hyperlink and the "Hyperlink" style when you want plain values, any text and internal sheet links, and check URL lengths and turn off formula conversion for user data, because both limits otherwise lose or corrupt data without an error.

Frequently asked questions

Why are my URLs clickable with one engine and plain text with another? pandas' xlsxwriter engine writes strings through xlsxwriter's write(), which turns anything that looks like a URL into a hyperlink by default. The openpyxl engine writes the same string as plain text.

How do I show friendly text instead of the full URL? Either write =HYPERLINK("url", "text") formulas into the column, or write the text and attach the URL with openpyxl's cell.hyperlink. The second keeps the cell a plain value, which is easier to read back.

What happens to very long URLs? Excel limits hyperlinks to 2,079 characters. xlsxwriter warns and leaves the cell empty for a longer URL, so the value is lost. Check lengths before writing and shorten or store long links as text.

Can user data accidentally become a formula? Yes. Both engines write any string starting with = as a formula. For data from users or external systems, turn off strings_to_formulas on the xlsxwriter engine, or prefix such values so they stay text.

Related

• Up one level: Writing DataFrames to Excel with Pandas — the writer and its engines.
• Add Hyperlinks to Excel Cells with Python — hyperlinks cell by cell with openpyxl.
• openpyxl vs xlsxwriter vs pandas.ExcelWriter — why the engines differ.
• Fix Invalid Worksheet Name Errors When Writing Excel — safe names for the sheets you link to.