Sum the Same Cell Across Many Sheets with 3D References from Python

Monthly workbooks often follow one pattern: a tab per month with an identical layout, and a summary tab that adds them up. In Excel the summary does not need twelve additions — a 3D reference such as =SUM(Jan:Dec!B2) adds cell B2 across every sheet from Jan to Dec in the tab order. When Python generates the monthly tabs, it can write the summary formulas too, so the workbook stays live: someone corrects a figure on the March tab and the summary updates in Excel without re-running the script. This guide, part of Working with Multiple Excel Sheets in Python, builds that workbook with openpyxl and covers the rules that make 3D references reliable.

Prerequisites

pip install openpyxl pandas

LibreOffice is optional; it is used at the end to recalculate the workbook and check the totals without Excel.

Step 1 — Write identical month sheets

3D references only make sense when every sheet has the same layout, so generate the month tabs from one function. Each gets the same labels in column A and that month's figures in column B:

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font

LINES = ["Revenue", "Cost of sales", "Gross margin", "Overheads", "Operating profit"]
MONTHS = {
    "Jan": [12400, 7100, None, 2900, None],
    "Feb": [11950, 6800, None, 2950, None],
    "Mar": [13210, 7450, None, 3010, None],
}

def month_sheet(wb, name, values, index=None):
    ws = wb.create_sheet(name, index)
    ws["A1"], ws["B1"] = "Line", name
    ws["A1"].font = ws["B1"].font = Font(bold=True)
    for row, (label, value) in enumerate(zip(LINES, values), start=2):
        ws.cell(row, 1, label)
        ws.cell(row, 2, value)
    ws["B4"] = "=B2-B3"          # gross margin
    ws["B6"] = "=B4-B5"          # operating profit
    ws.column_dimensions["A"].width = 20
    return ws

wb = Workbook()
summary = wb.active
summary.title = "Summary"
for name, values in MONTHS.items():
    month_sheet(wb, name, values)
print(wb.sheetnames)    # ['Summary', 'Jan', 'Feb', 'Mar']

The month sheets contain their own formulas for the derived lines. That keeps each tab correct on its own and means the summary can add any line, derived or not, with the same kind of 3D formula.

Step 2 — Add bookend sheets and write the 3D formulas

Referring to Jan:Mar works until April arrives, and then every summary formula needs editing. The standard fix is a pair of empty bookend sheets, Start and End, placed around the months. Formulas refer to Start:End, and any sheet dragged or inserted between the bookends is included automatically:

start = wb.create_sheet("Start", 1)        # right after Summary
end = wb.create_sheet("End")               # after the last month
for bookend in (start, end):
    bookend["A1"] = "Months between Start and End are added into the Summary."
    bookend.sheet_properties.tabColor = "CDD5E6"

summary["A1"], summary["B1"] = "Line", "Quarter total"
summary["A1"].font = summary["B1"].font = Font(bold=True)
for row, label in enumerate(LINES, start=2):
    summary.cell(row, 1, label)
    summary.cell(row, 2, f"=SUM(Start:End!B{row})")
summary.column_dimensions["A"].width = 20
wb.save("quarter.xlsx")

print(wb.sheetnames)                       # ['Summary', 'Start', 'Jan', 'Feb', 'Mar', 'End']
print(load_workbook("quarter.xlsx")["Summary"]["B2"].value)   # =SUM(Start:End!B2)

The bookends hold nothing that a SUM would pick up — a text note in A1 is ignored by SUM and does not collide with the B cells being added. A grey tab colour makes them easy to spot and hard to delete by accident.

Step 3 — Insert new months in the right position

Because the reference is positional, where a new sheet goes decides whether it counts. With openpyxl, pass an index to create_sheet so April lands before End:

wb = load_workbook("quarter.xlsx")
april = month_sheet(wb, "Apr", [12880, 7020, None, 3050, None], index=wb.sheetnames.index("End"))
wb.save("quarter.xlsx")
print(wb.sheetnames)   # ['Summary', 'Start', 'Jan', 'Feb', 'Mar', 'Apr', 'End']

Had create_sheet("Apr") appended it after End, the summary would silently ignore April. That is the main risk of 3D references in general — and the same thing happens when a person drags a tab around in Excel. Check the order in code whenever you add or reorder sheets, and put the check where a failure would stop the job:

names = wb.sheetnames
months = names[names.index("Start") + 1 : names.index("End")]
assert months == ["Jan", "Feb", "Mar", "Apr"], months

Step 4 — Quote names with spaces, once

Real month tabs are often named Jan 2026 or North - Q1. A 3D reference to such names needs quotes around the whole span, not around each name:

def three_d(first: str, last: str, cell: str, func: str = "SUM") -> str:
    needs_quotes = any(ch in first + last for ch in " -'&()")
    span = f"{first}:{last}"
    if needs_quotes:
        span = "'" + span.replace("'", "''") + "'"
    return f"={func}({span}!{cell})"

print(three_d("Start", "End", "B2"))               # =SUM(Start:End!B2)
print(three_d("Jan 2026", "Dec 2026", "B2"))       # =SUM('Jan 2026:Dec 2026'!B2)
print(three_d("Start", "End", "B2", "AVERAGE"))    # =AVERAGE(Start:End!B2)

Apostrophes inside a sheet name are doubled, as elsewhere in Excel formulas. Keeping the logic in one helper means every summary cell follows the same quoting rule.

Step 5 — Check the totals without Excel

openpyxl writes formulas as text; it never calculates them. To check the summary before sending the file, recalculate it headlessly with LibreOffice, which understands 3D references, and read the cached values:

import subprocess
from pathlib import Path

Path("recalculated").mkdir(exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalculated", "quarter.xlsx"],
               check=True, capture_output=True, timeout=120)
values = load_workbook("recalculated/quarter.xlsx", data_only=True)["Summary"]
print(values["B2"].value, values["B6"].value)   # 50440 10160

Revenue across the four months is 50,440 and operating profit 10,160 — April included, which proves the new tab sits inside the bookends. A useful habit is to compute the same totals independently in pandas from the data you wrote, and compare them with the recalculated summary; a mismatch means a sheet is outside the span or a layout has drifted.

Pure-Python calculation engines are not a substitute here: the formulas package, for example, rejects Start:End!B2 as an invalid formula. The trade-offs between recalculation approaches are covered in Recalculate Excel Formulas Without Excel in Python.

For a conditional total across sheets — say, revenue only from months above target — put the condition in a helper cell on every month sheet (=IF(B2>12000,B2,0)) and 3D-sum the helper cell, or compute the figure in pandas and write it as a value.

Common pitfalls

• Sheets outside the bookends. A tab appended after End is ignored without any error. Always insert with an explicit index and assert the order.
• Layouts drifting apart. A 3D reference adds the same address on every sheet. If one month gains an extra row, B6 on that sheet is no longer operating profit. Generate every month from one function, as in Step 1.
• Hidden sheets still count. Hiding a month tab does not remove it from a 3D range. Move it outside the bookends to exclude it.
• Deleting a bookend. Excel adjusts the reference to the neighbouring sheet, so deleting Start quietly changes the span. Protect the workbook structure if others will edit it.

Performance and scale notes

A 3D formula is as cheap as a normal SUM for Excel, and writing a few dozen of them with openpyxl is instantaneous. The limits are human: beyond a year or two of monthly tabs the workbook becomes hard to navigate, and it is usually better to keep all months in one long table and let a pivot or SUMIFS build the summary. The one-table approach also makes the data easy to read back into pandas — see Read All Sheets from an Excel File into DataFrames for combining existing monthly tabs into one frame.

Conclusion

Generate identical month sheets from one function, surround them with empty Start and End bookends, and write summary cells as =SUM(Start:End!B2) — quoting the whole span when names contain spaces. Insert new tabs with an explicit index before End, assert the tab order in code, and recalculate with LibreOffice when you need to check totals before the file leaves Python. Keep to aggregate functions; conditional totals need a helper cell on each sheet.

Frequently asked questions

What is a 3D reference in Excel? A reference that spans a range of worksheets as well as cells, written First:Last!Cell — for example =SUM(Jan:Mar!B2) adds B2 on Jan, Mar and every sheet positioned between them in the tab order.

Which functions accept 3D references? Aggregates such as SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT and the STDEV and VAR families. Conditional and lookup functions such as SUMIF, COUNTIF and VLOOKUP do not accept them.

Why did my 3D total change when I moved a sheet? A 3D reference is defined by sheet positions, not a list of names. Any sheet dragged between the first and last sheet is included, and any sheet dragged outside is excluded, even though the formula text is unchanged.

How do I write a 3D reference when sheet names contain spaces? Quote the whole sheet span once, for example =SUM('Jan 2026:Dec 2026'!B2). Quoting each name separately is not valid.

Related

• Up one level: Working with Multiple Excel Sheets in Python — reading and writing multi-sheet workbooks.
• Working with Excel Formulas in Python — writing and reading formulas in general.
• Add a Summary Sheet to an Excel Report with Python — summaries computed in pandas instead.
• Create a Sheet for Each Group in a DataFrame — generating the per-period tabs.