Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Write a DataFrame to a Specific Cell in Excel with pandas

Place a DataFrame at B5 instead of A1 — with startrow and startcol, into a new file or into an existing template without wiping its title, formatting and notes — and know when to drop down to openpyxl.
Writing Excel filespandas

df.to_excel() puts the header in cell A1 by default, which is right for a data dump and wrong for almost any report. Reports have a title in the top rows, a date or a note beside the table, sometimes two tables on one sheet — and very often an existing template whose layout someone designed by hand. pandas handles all of these with two arguments, startrow and startcol, plus one writer setting that decides whether the rest of the sheet survives. This guide is part of Writing DataFrames to Excel with Pandas.

A DataFrame written with startrow=4 and startcol=1 The report title sits in A1 and a note in E2. The DataFrame header lands in B5 because startrow and startcol are zero-based offsets from A1, and its values fill B6 to D8. monthly_report.xlsx A B C D E 1 Monthly sales report 2 Prepared by ops 3 4 5 region units revenue 6 North 410 12,300 7 South 388 11,640 8 East 295 8,850 startrow=4, startcol=1 puts the header in B5 — both offsets count from zero at A1

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter
Python
import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "East"],
    "units": [410, 388, 295],
    "revenue": [12300.0, 11640.0, 8850.0],
})

Step 1 — Offset the table in a new file

startrow and startcol are zero-based offsets from A1. To leave four rows for a title block and one blank column on the left, put the header at B5:

Python
with pd.ExcelWriter("monthly_report.xlsx", engine="xlsxwriter") as writer:
    sales.to_excel(writer, sheet_name="Report", startrow=4, startcol=1, index=False)
    sheet = writer.sheets["Report"]
    sheet.write("A1", "Monthly sales report", writer.book.add_format({"bold": True, "font_size": 14}))
    sheet.write("E2", "Prepared by ops")

Writing through an ExcelWriter rather than a file name is what lets you add the title afterwards: writer.sheets gives you the engine's worksheet object for the sheet pandas just created, and writer.book the workbook. With the xlsxwriter engine you can then use its whole API — formats, merged titles, column widths — on the same sheet in the same pass.

Converting an Excel address to offsets is easy to get wrong by one, so let openpyxl do it:

Python
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string

def offsets(address: str) -> tuple[int, int]:
    letters, row = coordinate_from_string(address)
    return row - 1, column_index_from_string(letters) - 1

print(offsets("B5"))    # (4, 1)
print(offsets("AA10"))  # (9, 26)

Step 2 — Write into an existing template without wiping it

The more common case is a template that already exists: a designed header, a logo, notes, fixed column widths. Opening it with the default writer mode would create a new file and throw all of that away. Append mode with the openpyxl engine and if_sheet_exists="overlay" writes only the cells the DataFrame covers:

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

template = Workbook()
ws = template.active
ws.title = "Report"
ws["A1"] = "Monthly sales report"
ws["A1"].font = Font(bold=True, size=14)
ws["E2"] = "Prepared by ops"
ws.column_dimensions["B"].width = 18
template.save("template.xlsx")

with pd.ExcelWriter("template.xlsx", mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    sales.to_excel(writer, sheet_name="Report", startrow=4, startcol=1, index=False)

check = load_workbook("template.xlsx")["Report"]
print(check["A1"].value, check["A1"].font.b)       # Monthly sales report True
print(check["B5"].value, check["C6"].value)        # region 410
print(check.column_dimensions["B"].width)          # 18.0

The title, its font, the note and the column width all survive. That is the whole difference between the if_sheet_exists options:

What each if_sheet_exists option does to an existing sheet error refuses to write if the sheet exists. new creates a second sheet with a numbered name. replace deletes the sheet and writes into a fresh empty one, losing formatting. overlay writes into the existing sheet, changing only the cells the DataFrame covers. "error" (default) raises ValueError nothing written "new" writes to Report1 template left untouched "replace" sheet deleted first title, widths, notes lost "overlay" writes into the sheet only covered cells change

overlay also means stale values remain. If last month's table had five rows and this month's has three, rows four and five from last month are still there underneath. Clear the data area before writing, as in Step 4.

Step 3 — Fill a designed header with values only

Templates often have the header row already styled — merged, coloured, maybe with line breaks in the labels. Write just the values under it with header=False, starting one row lower:

Python
with pd.ExcelWriter("template.xlsx", mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    sales.to_excel(writer, sheet_name="Report", startrow=5, startcol=1, index=False, header=False)

The designed header in row 5 is untouched and the values land from B6. This is the core move of template-driven reporting; the full workflow, including keeping charts and images alive, is in Populate an Excel Template Without Losing Formatting.

Step 4 — Clear the old data area first

Because overlay only writes where the new frame has cells, clear the whole data region before every run. openpyxl can blank a block while keeping cell formatting:

Python
def clear_block(path: str, sheet: str, first_row: int, first_col: int, n_cols: int) -> None:
    wb = load_workbook(path)
    ws = wb[sheet]
    for row in ws.iter_rows(min_row=first_row, max_row=ws.max_row,
                            min_col=first_col, max_col=first_col + n_cols - 1):
        for cell in row:
            cell.value = None
    wb.save(path)

clear_block("template.xlsx", "Report", first_row=6, first_col=2, n_cols=3)   # B6 downwards
with pd.ExcelWriter("template.xlsx", mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    sales.head(2).to_excel(writer, sheet_name="Report", startrow=5, startcol=1, index=False, header=False)

report = load_workbook("template.xlsx")["Report"]
print([report.cell(row=r, column=2).value for r in range(6, 10)])  # ['North', 'South', None, None]

Setting cell.value = None keeps the cell's number format, fill and borders, so the designed banding of the data area stays in place for the next run.

Step 5 — Stack several tables on one sheet

Offsets also let you place several frames on one sheet, one under another. Track the next free row as you go, leaving a gap and a caption between tables:

Python
by_region = sales.set_index("region")
top = sales.nlargest(2, "revenue")

with pd.ExcelWriter("stacked.xlsx", engine="xlsxwriter") as writer:
    row = 0
    for caption, frame in [("By region", by_region), ("Top two", top)]:
        frame.to_excel(writer, sheet_name="Summary", startrow=row + 1)
        writer.sheets["Summary"].write(row, 0, caption)
        row += len(frame) + 4          # caption + header + rows + gap

len(frame) counts data rows only, so add one for the header and one for the caption line; a MultiIndex header takes more rows, as Write a MultiIndex DataFrame to Excel with pandas explains. Side-by-side layouts work the same way with startcol — the pattern is covered in Write Multiple DataFrames to One Excel File.

Two tables stacked on one sheet with a running row counter The first table starts at row 0 with its caption, its header and rows follow, then a gap. The row counter advances by the table length plus caption, header and gap rows, and the second table starts there. row 0: caption, row 1: header first table starts at startrow=1 row += len(frame) + 4 row 7: caption, row 8: header second table starts at startrow=8

Step 6 — Anchor the table to a named cell, not a hard-coded address

Hard-coding startrow=5, startcol=1 couples your script to the template's layout. The day someone adds a row to the title block, every run writes one row too high, over the header. A sturdier contract is a defined name in the template: whoever designs the sheet marks the top-left data cell as data_start, and the script looks it up.

Python
from openpyxl.workbook.defined_name import DefinedName

wb = load_workbook("template.xlsx")
wb.defined_names["data_start"] = DefinedName("data_start", attr_text="Report!$B$6")
wb.save("template.xlsx")

def anchor(path: str, name: str) -> tuple[str, int, int]:
    book = load_workbook(path)
    sheet_name, ref = next(iter(book.defined_names[name].destinations))
    return sheet_name, *offsets(ref.replace("$", ""))

sheet_name, start_row, start_col = anchor("template.xlsx", "data_start")
print(sheet_name, start_row, start_col)     # Report 5 1

with pd.ExcelWriter("template.xlsx", mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
    sales.to_excel(writer, sheet_name=sheet_name, startrow=start_row, startcol=start_col,
                   index=False, header=False)

When the template designer inserts rows above the table in Excel, Excel moves the defined name with the cell, so the script keeps writing to the right place without a code change. It also documents intent: a reader of the template can open Name Manager and see exactly where automation writes. Named ranges are covered in more depth in Create a Named Range in Excel with openpyxl.

Common pitfalls

  • One-based offsets. startrow=5 puts the header in row 6. Use the offsets() helper instead of converting in your head.
  • mode="a" with xlsxwriter. xlsxwriter only creates new files, so append mode requires the openpyxl engine; pandas raises if you combine them.
  • The index column shifts everything. With the default index=True, the first DataFrame column lands one column right of startcol. Pass index=False or allow for it.
  • Overlay leaves stale rows. Clear the data area first when the row count can shrink between runs.

Performance and scale notes

Append mode loads the whole existing workbook with openpyxl, edits it in memory and saves it again. For a template of a few sheets that takes a fraction of a second; for a workbook with hundreds of thousands of existing cells it can take many seconds and a lot of memory, and saving through openpyxl can drop features it does not support. When the template is large, keep the template itself small — layout and formatting only — and put bulk data on a separate sheet written in one pass with xlsxwriter.

Conclusion

startrow and startcol place a DataFrame anywhere, counting from zero at A1. For a new file, write through an ExcelWriter and decorate the sheet with the engine's API in the same pass. For an existing template, use mode="a", the openpyxl engine and if_sheet_exists="overlay", write values only under a designed header, and clear the data area first so shrinking tables do not leave stale rows behind.

Frequently asked questions

Are startrow and startcol zero-based? Yes. startrow=0 and startcol=0 is cell A1, so to put the header in cell B5 you pass startrow=4 and startcol=1.

How do I write into an existing sheet without deleting what is already there? Open the file with pd.ExcelWriter in mode="a" with the openpyxl engine and if_sheet_exists="overlay". pandas then writes only the cells covered by the DataFrame and leaves the rest of the sheet, including formatting, as it was.

What is the difference between overlay and replace? replace deletes the existing sheet and creates a fresh empty one with the same name before writing, so titles, notes and column widths are lost. overlay writes into the existing sheet.

Can I write only the values, without the header? Yes. Pass header=False, and index=False if you do not want the index either. This is how you fill the data area of a template whose header row is already designed.

More on writing excel files

More on writing excel files →