Write Formulas That Reference Other Sheets with openpyxl

A summary sheet is only as good as its links. =SUM(Sales!B2:B50) works; =SUM(Sales Data!B2:B50) does not; ='Q1'!B2 fails for a reason that has nothing to do with the quotes; and after a script renames a sheet, formulas that looked fine yesterday point at nothing. Cross-sheet formulas written from Python follow Excel's reference syntax exactly, and openpyxl does none of the bookkeeping Excel does for users. This page, part of Working with Excel Formulas in Python, shows the syntax, a helper that gets the quoting right every time, and how to keep formulas working when sheets are renamed. Results were checked by recalculating the files in LibreOffice.

Prerequisites

pip install openpyxl

from openpyxl import Workbook, load_workbook
from openpyxl.utils import quote_sheetname

wb = Workbook()
summary = wb.active
summary.title = "Summary"
data = wb.create_sheet("Sales Data")
data.append(["region", "revenue"])
for region, revenue in [("North", 1200), ("South", 950), ("East", 700)]:
    data.append([region, revenue])
wb.create_sheet("O'Brien")["A1"] = 5
wb.create_sheet("Q1")["A1"] = 7

Step 1 — Quote sheet names that are not plain words

A sheet name made only of letters, digits and underscores can be written bare: =Sales!B2. Anything else — a space, a hyphen, an ampersand, a leading digit — needs single quotes around the name. Leave them out and the formula is broken:

summary["A1"] = "Total (quoted)"
summary["B1"] = "=SUM('Sales Data'!B2:B4)"      # 2850 when calculated
summary["A2"] = "Total (unquoted)"
summary["B2"] = "=SUM(Sales Data!B2:B4)"        # an error: the space ends the name
print(summary["B1"].value, summary["B2"].value)

An apostrophe inside a sheet name is doubled within the quotes, the same way a quote is doubled inside an Excel text string:

summary["B3"] = "='O''Brien'!A1"                 # 5 when calculated

There is one more case that catches people: names that look like cell addresses. Q1, A1, FY2026 and R1C1 are all valid addresses in one notation or another, and a bare =Q1!A1 does not reliably reach a sheet called Q1. Quoting is harmless for every name, so the simplest rule is to always quote.

Step 2 — Build references with quote_sheetname, not f-strings

Hand-written quoting is where cross-sheet formulas go wrong in generated workbooks: the sheet names come from data, and one of them eventually contains an apostrophe or a space. openpyxl's quote_sheetname always adds the quotes and doubles apostrophes; wrap it in a helper and build every reference through it:

def ref(sheet, address):
    return f"{quote_sheetname(sheet)}!{address}"

for name in ["Sales Data", "O'Brien", "Q1", "Plain"]:
    print(ref(name, "B2:B4"))

summary["B4"] = f"=SUM({ref('Sales Data', 'B2:B4')})"
summary["B5"] = f"={ref('Q1', 'A1')}*2"
print(summary["B4"].value, summary["B5"].value)

Every reference the helper produces is valid whatever the name — 'Sales Data'!B2:B4, 'O''Brien'!B2:B4, 'Q1'!B2:B4. Using it consistently removes a whole class of bug from report code, including the ones that only appear the day a new region called "Asia-Pacific" is added.

Step 3 — Reference whole columns, or better, defined names

For summary formulas over data sheets whose length changes, a whole-column reference avoids updating the range every run: =SUM('Sales Data'!B:B). It includes the header row, which SUM ignores because it is text, but functions such as COUNTA count it, so choose accordingly.

A defined name is often better still. It gives the range a readable name, keeps the sheet quoting in one place, and survives rows being added if you update the definition when the data is written:

from openpyxl.workbook.defined_name import DefinedName

last = data.max_row
wb.defined_names["Revenue"] = DefinedName("Revenue", attr_text=f"{ref('Sales Data', f'$B$2:$B${last}')}")
summary["A6"] = "Total (named)"
summary["B6"] = "=SUM(Revenue)"
print(wb.defined_names["Revenue"].attr_text)

=SUM(Revenue) reads better than any range address, and the next person to open the workbook can find the definition in Excel's Name Manager. Create a Named Range in Excel with openpyxl covers names in depth.

Step 4 — Rename sheets without breaking formulas

When a user renames a sheet in Excel, Excel rewrites every formula that refers to it. openpyxl does not — changing ws.title changes only the tab:

wb["Q1"].title = "Quarter 1"
print(summary["B5"].value)
# ='Q1'!A1*2 — still the old name; after recalculation this is an error

Recalculating that workbook turns the cell into an error, because no sheet called Q1 exists any more. Rename through a helper that also rewrites references in every formula and every defined name:

import re

def rename_sheet(wb, old, new):
    pattern = re.compile(r"(?<![\w'])(?:'" + re.escape(old.replace("'", "''")) + r"'|" + re.escape(old) + r")!")
    replacement = quote_sheetname(new) + "!"
    wb[old].title = new
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if cell.data_type == "f" and isinstance(cell.value, str):
                    cell.value = pattern.sub(lambda m: replacement, cell.value)
    for name, dn in wb.defined_names.items():
        dn.attr_text = pattern.sub(lambda m: replacement, dn.attr_text)

wb["Quarter 1"].title = "Q1"                 # undo the plain rename for the demo
rename_sheet(wb, "Q1", "Quarter 1")
rename_sheet(wb, "Sales Data", "Sales 2026")
print(summary["B5"].value, "|", summary["B4"].value, "|", wb.defined_names["Revenue"].attr_text)

The pattern matches the old name both quoted and bare, and the lookbehind stops it matching inside a longer name — renaming Q1 must not touch references to Q10. Sheet-scoped defined names, data validation lists and chart references can also mention sheet names; update them the same way if your workbook uses them.

Step 5 — Check the result by recalculating

A cross-sheet mistake does not raise anything in Python; it only shows as an error value once the workbook is calculated. Recalculate a saved copy and read the results back as a final check:

import subprocess

wb.save("linked.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "linked.xlsx"],
               check=True, capture_output=True, timeout=120)
calc = load_workbook("calc/linked.xlsx", data_only=True)["Summary"]
print({f"B{r}": calc[f"B{r}"].value for r in range(1, 7)})

Every cell calculates — including B2, the deliberately unquoted Sales Data reference from Step 1. That is a side effect worth understanding: rename_sheet matched the bare form Sales Data! and rewrote it as 'Sales 2026'!, quotes included, so renaming repaired it. Without the rename, B2 recalculates to an error (#VALUE! in LibreOffice), which is why the check belongs at the end of every job that writes cross-sheet formulas: it is the only place such a mistake becomes visible. Find #REF!, #DIV/0! and Other Formula Errors in a Workbook turns this check into a reusable scan.

A word on INDIRECT

=INDIRECT("'" & A1 & "'!B2") builds a reference from text at calculation time, which is tempting when a summary should follow a sheet name typed in a cell. It works, but it is volatile — it recalculates whenever anything changes — its references are invisible to Excel's trace-precedents tools, and a sheet rename breaks it in a way no helper can fix, because the name lives in a text string. When Python is writing the workbook anyway, write the direct reference instead; reserve INDIRECT for sheets where users genuinely choose the source at run time.

Common pitfalls

• Unquoted names with spaces. Always quote; quote_sheetname makes it automatic.
• Names that look like addresses. Q1 and A1 need quotes even though they have no spaces.
• Renaming with ws.title alone. Formulas and defined names keep the old name.
• Referencing a sheet before it exists. openpyxl accepts the formula; the error appears only on calculation. Create sheets first, then write formulas that refer to them.

Performance notes

Formula strings cost nothing to write; the rename helper visits every cell once, which takes a second or two on large workbooks and is still far cheaper than debugging a broken summary. If a workbook is regenerated from scratch every run, create sheets with their final names and skip renaming altogether.

Conclusion

Cross-sheet references are sheet!address, with the sheet name in single quotes whenever it is anything but a plain word — and quoting always is the safe habit. Build references through quote_sheetname, prefer defined names for ranges that grow, rename sheets with a helper that rewrites formulas and names because openpyxl will not, and recalculate a copy to confirm every link resolves.

Frequently asked questions

When does a sheet name need quotes in a formula? When it contains anything other than letters, digits and underscores, or starts with a digit, or could be read as a cell address — "Sales Data", "O'Brien", "2026", "Q1". Quoting always is harmless, so the simplest rule is to always quote.

How do I write a sheet name that contains an apostrophe? Double the apostrophe inside the quotes: ='O''Brien'!A1. openpyxl.utils.quote_sheetname does this for you.

Does renaming a sheet in openpyxl update formulas? No. Excel rewrites references when a user renames a sheet, but openpyxl only changes the title. Formulas that used the old name point at a sheet that no longer exists. Update them yourself, as shown on this page.

Should I use INDIRECT to build sheet references from text? Only when the sheet really must be chosen at calculation time. INDIRECT is volatile, so it recalculates on every change, and its references are invisible to Excel's dependency tools and to renames. Writing the reference directly from Python is almost always better.

Related

• Up one level: Working with Excel Formulas in Python — the formula basics.
• Link Dashboard Cells to Data Sheets with Formulas — cross-sheet links in a dashboard.
• Sum the Same Cell Across Many Sheets with 3D References from Python — references spanning a run of sheets.
• Fix Invalid Worksheet Name Errors When Writing Excel — choosing names that are safe to reference.