Guide

Automating Reporting WorkflowsDeep dive

Add a Table of Contents Sheet with Hyperlinks in Excel

Q: What is the syntax for a link to another sheet?

A location beginning with a hash and the sheet reference, such as hash Summary exclamation A1. openpyxl writes it through cell.hyperlink, and Excel treats it as an internal jump rather than a web link.

Q: Why does my link look like plain text?

Setting cell.hyperlink creates the link but does not style it. Apply the built-in Hyperlink style, or set a blue underlined font yourself, so readers can see it is clickable.

Give a multi-sheet workbook a front door — an index sheet with internal hyperlinks, row counts and back-links, generated from whatever sheets the workbook happens to contain.

A workbook with twelve sheets has a navigation problem: the tab bar shows six of them, the names are truncated, and a reader looking for "the regional breakdown" clicks through four sheets to find it. An index sheet fixes it — a front page listing every sheet with a one-line description and a clickable link. openpyxl builds one in a loop, and the two things worth getting right are the internal-link syntax and generating the list rather than typing it. This guide covers both. It extends Building Multi-Sheet Excel Dashboards.

Prerequisites

Bash

pip install pandas openpyxl

A multi-sheet workbook to index:

Python

import pandas as pd

sheets = {
    "Summary": pd.DataFrame({"metric": ["revenue", "units"],
                             "value": [41614.15, 3204]}),
    "Regional": pd.DataFrame({"region": ["North", "South", "West", "East"],
                              "revenue": [5150.0, 4268.5, 3511.25, 2980.1]}),
    "Q3 Detail": pd.DataFrame({"order": range(1, 51),
                               "amount": [12.5 * i for i in range(1, 51)]}),
    "_raw": pd.DataFrame({"order": range(1, 501), "amount": range(1, 501)}),
}

with pd.ExcelWriter("dashboard.xlsx", engine="xlsxwriter") as writer:
    for name, frame in sheets.items():
        frame.to_excel(writer, sheet_name=name, index=False)

Note Q3 Detail — a sheet name with a space, which is the case that breaks naive link building.

Step 1 — Understand the link syntax

An internal hyperlink is a location string starting with #:

Python

from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")
ws = wb.create_sheet("Contents", 0)

ws["A1"] = "Summary"
ws["A1"].hyperlink = "#Summary!A1"          # works: no space in the name

ws["A2"] = "Q3 Detail"
ws["A2"].hyperlink = "#Q3 Detail!A1"        # broken: unquoted space
ws["A3"] = "Q3 Detail"
ws["A3"].hyperlink = "#'Q3 Detail'!A1"      # works: quoted

wb.save("dashboard_linked.xlsx")

Excel's rule is that a sheet name containing anything other than letters, digits and underscores must be wrapped in single quotes. Rather than remembering when, quote whenever the name is not a plain identifier — and escape any apostrophe inside it by doubling it:

Python

import re

PLAIN = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")

def sheet_ref(name, cell="A1"):
    """A safe internal-link reference for any sheet name."""
    if PLAIN.match(name):
        return f"#{name}!{cell}"
    escaped = name.replace("'", "''")
    return f"#'{escaped}'!{cell}"

print(sheet_ref("Summary"))       # #Summary!A1
print(sheet_ref("Q3 Detail"))     # #'Q3 Detail'!A1
print(sheet_ref("Bob's data"))    # #'Bob''s data'!A1

Setting hyperlink creates the link but leaves the cell looking like ordinary text. Style it too, or readers will not know it is clickable:

Python

ws["A1"].style = "Hyperlink"          # Excel's built-in style

Step 2 — Generate the index

Walking the workbook means the index cannot drift out of date, which a typed list always eventually does:

Python

from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font, PatternFill

DESCRIPTIONS = {
    "Summary": "Headline figures for the period",
    "Regional": "Revenue and variance by region",
    "Q3 Detail": "Every order in the quarter",
}

def add_contents(path, dest, title="Contents", skip_prefixes=("_",),
                 descriptions=None, index_name="Contents"):
    """Build an index sheet listing every sheet, with links and row counts."""
    descriptions = descriptions or {}
    wb = load_workbook(path)

    if index_name in wb.sheetnames:
        del wb[index_name]
    index = wb.create_sheet(index_name, 0)

    index["A1"] = title
    index.merge_cells("A1:C1")
    index["A1"].font = Font(size=14, bold=True, color="4338CA")
    index["A1"].alignment = Alignment(horizontal="center", vertical="center")
    index["A1"].fill = PatternFill("solid", start_color="EBEBFD",
                                   end_color="EBEBFD")
    index.row_dimensions[1].height = 26

    header = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", start_color="4338CA",
                              end_color="4338CA")
    for column, label in zip("ABC", ("Sheet", "Contents", "Rows")):
        cell = index[f"{column}3"]
        cell.value = label
        cell.font = header
        cell.fill = header_fill

    row = 4
    for ws in wb.worksheets:
        if ws.title == index_name or ws.title.startswith(skip_prefixes):
            continue
        if ws.sheet_state != "visible":
            continue

        link = index.cell(row=row, column=1, value=ws.title)
        link.hyperlink = sheet_ref(ws.title)
        link.style = "Hyperlink"

        index.cell(row=row, column=2,
                   value=descriptions.get(ws.title, ""))
        index.cell(row=row, column=3,
                   value=max(ws.max_row - 1, 0)).number_format = "#,##0"
        row += 1

    index.column_dimensions["A"].width = 22
    index.column_dimensions["B"].width = 46
    index.column_dimensions["C"].width = 12
    index.freeze_panes = "A4"
    index.sheet_view.showGridLines = False

    wb.active = 0
    wb.save(dest)
    return row - 4

count = add_contents("dashboard.xlsx", "dashboard_indexed.xlsx",
                     descriptions=DESCRIPTIONS)
print(f"indexed {count} sheet(s)")

Three choices worth noting. Skipping sheets whose name starts with _ gives you a convention for working sheets that should not appear — the same convention used for hiding them in hiding sheets, rows and columns. Deleting an existing index before rebuilding makes the function idempotent, so a re-run does not produce Contents1. And wb.active = 0 opens the workbook on the index, which is the point of having one.

The row count uses ws.max_row - 1 to exclude the header — and inherits max_row's habit of overreporting when stray formatting extends the used range, so treat it as indicative rather than exact.

Step 3 — Add back-links

Navigation should work both ways. A link in A1 of every sheet costs one loop:

Python

from openpyxl import load_workbook
from openpyxl.styles import Font

def add_back_links(path, dest, index_name="Contents", label="← back to index"):
    """Put a link to the index in A1 of every other sheet."""
    wb = load_workbook(path)
    if index_name not in wb.sheetnames:
        raise KeyError(f"no sheet named {index_name!r}")

    added = 0
    for ws in wb.worksheets:
        if ws.title == index_name:
            continue

        # Make room so the link never overwrites data.
        ws.insert_rows(1)
        cell = ws.cell(row=1, column=1, value=label)
        cell.hyperlink = sheet_ref(index_name)
        cell.style = "Hyperlink"
        cell.font = Font(size=10, bold=True, underline="single",
                         color="4338CA")
        ws.freeze_panes = "A3"          # keep the link and header visible
        added += 1

    wb.save(dest)
    return added

insert_rows(1) is what stops the back-link overwriting a header. It does shift every row down by one, so run this before anything that depends on row positions — and remember that inserting rows does not rewrite formulas, as covered in inserting and deleting rows and columns.

Where the sheets already carry a title row, write the link into an unused cell to the right instead:

Python

ws.cell(row=1, column=ws.max_column + 2, value=label).hyperlink = \
    sheet_ref(index_name)

Step 4 — Order the index the way readers think

Workbook order is how the tabs appear, and the index should usually match — but the useful order is often not the order the sheets were written in.

Python

from openpyxl import load_workbook

PREFERRED = ["Contents", "Summary", "Regional", "Q3 Detail", "Detail"]

def reorder_sheets(wb, preferred=PREFERRED):
    """Put the named sheets first, in that order; leave the rest after."""
    known = [wb[name] for name in preferred if name in wb.sheetnames]
    rest = [ws for ws in wb.worksheets if ws not in known]
    wb._sheets = known + rest
    wb.active = 0
    return [ws.title for ws in wb.worksheets]

Reordering the workbook rather than just the index rows fixes three things at once: the tab order, the index order, and the page order if the workbook is later exported, as in converting only selected sheets to PDF.

Common pitfalls and fixes

Symptom	Cause	Fix
Link does nothing	Sheet name with a space, unquoted	Wrap it: `#'Q3 Detail'!A1`.
Link looks like plain text	Only `hyperlink` set	Apply the `Hyperlink` style.
Link opens a browser	Missing the leading `#`	Internal links must start with `#`.
`Contents1` after a re-run	Existing index not removed	Delete it before recreating.
Back-link overwrote a header	Written into an occupied A1	`insert_rows(1)` first.
Row counts far too high	`max_row` reflects the used range	Treat the count as indicative.
Workbook opens on the wrong sheet	`wb.active` not set	Set it to the index's position.
Link to a sheet with an apostrophe fails	Apostrophe not escaped	Double it inside the quotes.

Performance and scale notes

Building an index is cheap — one sheet, one row per sheet. The cost is load_workbook, which parses the whole file, so adding an index to a 60 MB workbook pays the full parse for a few dozen cells of output.

Two ways to avoid that. Build the index during the original write, when the sheet names and row counts are already known, so no second load is needed:

Python

import pandas as pd

def write_with_contents(sheets, path, descriptions=None):
    """Write every sheet and an index, in one pass."""
    descriptions = descriptions or {}
    order = ["Contents", *sheets]

    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        book = writer.book
        index = book.add_worksheet("Contents")
        writer.sheets["Contents"] = index

        title = book.add_format({"bold": True, "font_size": 14,
                                 "font_color": "#4338CA",
                                 "bg_color": "#EBEBFD", "align": "center"})
        header = book.add_format({"bold": True, "bg_color": "#4338CA",
                                  "font_color": "#FFFFFF"})
        link = book.add_format({"font_color": "blue", "underline": 1})

        index.merge_range("A1:C1", "Contents", title)
        for column, label in enumerate(("Sheet", "Contents", "Rows")):
            index.write(2, column, label, header)

        for position, (name, frame) in enumerate(sheets.items(), start=3):
            index.write_url(position, 0, f"internal:'{name}'!A1", link, name)
            index.write(position, 1, descriptions.get(name, ""))
            index.write_number(position, 2, len(frame))
            frame.to_excel(writer, sheet_name=name, index=False, startrow=1)

            sheet = writer.sheets[name]
            sheet.write_url(0, 0, "internal:'Contents'!A1", link,
                            "← back to index")
            sheet.freeze_panes(2, 0)

        index.set_column("A:A", 22)
        index.set_column("B:B", 46)
        index.set_column("C:C", 12)
        index.freeze_panes(3, 0)
        index.hide_gridlines(2)

    return path

xlsxwriter's write_url uses internal: rather than a leading #, and quoting the sheet name is required for the same reason. Writing the index first also means it is sheet zero, so the workbook opens on it with no extra call.

Read only the metadata when you must load an existing file. read_only=True gives you sheet names and dimensions without materialising cells:

Python

from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx", read_only=True)
inventory = {ws.title: ws.max_row for ws in wb.worksheets}
wb.close()

Use that to plan the index, then do the single write pass that produces it. And note the practical ceiling on the whole idea: an index earns its place from about four sheets upwards, and stops helping beyond about thirty — at that point the workbook is a database, and the answer is a filterable table rather than a longer list of links.

Conclusion

An index sheet turns a pile of tabs into a document with a front page. The syntax is a location starting with #, with the sheet name in single quotes whenever it is not a plain identifier — and an apostrophe inside it doubled. Style the cells so readers can see they are links, put a back-link in every sheet so navigation works both ways, and generate the list by walking the workbook so it cannot drift out of date. Order the sheets the way a reader wants them rather than the order they were written, and build the index during the original write when you can, so no second parse is needed.

Frequently asked questions

What is the syntax for a link to another sheet? A location beginning with # and the sheet reference, such as #Summary!A1. openpyxl writes it through cell.hyperlink, and Excel treats it as an internal jump rather than a web link.

How do I link to a sheet whose name has a space? Wrap the sheet name in single quotes inside the reference — #'Q3 Detail'!A1. Without the quotes Excel cannot resolve the reference and the link does nothing.

Why does my link look like plain text? Setting cell.hyperlink creates the link but does not style it. Apply the built-in Hyperlink style, or set a blue underlined font yourself, so readers can see it is clickable.

Should every sheet link back to the index? Yes, in cell A1 or just above the data. A workbook where you can reach the index from anywhere is far easier to navigate than one where you have to find the tab.

Can I generate the index automatically? Yes, and you should. Walk the workbook's sheets, skip the ones you do not want listed, and build a row per sheet with its name, a description and its row count — so the index cannot drift out of date.

Up to the parent: Building Multi-Sheet Excel Dashboards — the workbook this indexes.
Add a Summary Sheet to an Excel Report with Python — the sheet the index should list first.
Write Multiple DataFrames to One Excel File — the single-pass write the index fits into.
Hide Sheets, Rows and Columns with openpyxl — keeping working sheets out of the index.
Rename, Reorder and Delete Excel Sheets with openpyxl — the ordering operations used here.