For targeted edits to an existing .xlsx or .xlsm workbook, openpyxl is the most direct option covered here. Load with formula expressions enabled, use keep_vba=True for macro-enabled files, and save to a new file with the matching extension. This preserves important content, but it is not a guarantee that every Excel feature will survive a save-and-reopen cycle. Work on a copy and check the result in the spreadsheet application that will use it.
Choose the Python route that matches the job
| Need | Suitable route | Main caveat |
|---|---|---|
| Make targeted changes to cells in an existing workbook | openpyxl |
It does not support every workbook feature; validate the saved file. |
| Keep formula expressions while editing | openpyxl with its default data_only=False |
It does not calculate formulas or refresh cached results. |
| Retain existing VBA project content | openpyxl with keep_vba=True |
VBA is preserved, not editable through openpyxl; retain a macro-enabled extension. |
| Write tabular data with a DataFrame | pandas.ExcelWriter using the openpyxl engine |
The existing workbook is rewritten; content the engine cannot represent may be lost. |
| Create a new formatted workbook | XlsxWriter | It cannot read or modify an existing workbook, and it does not calculate formula results. |
The right choice depends on whether you are editing an existing file or generating a new one. XlsxWriter is designed for new workbooks, while openpyxl is suited to targeted edits. pandas can write DataFrames into existing workbooks, but its append workflow still rewrites the workbook.
As an Amazon Associate I earn from qualifying purchases.
Use openpyxl to edit an existing workbook
Keep formula expressions available
load_workbook() defaults to data_only=False, so a formula cell is read as its formula expression. For example, a cell containing =SUM(A1:A5) remains available as that expression when you load the workbook this way. The openpyxl tutorial explains that the data_only option determines whether formula cells expose the formula or the value stored the last time Excel read the sheet.
from openpyxl import load_workbook
wb = load_workbook("input.xlsx", data_only=False)
ws = wb["Sheet1"]
ws["B2"] = 42
wb.save("output.xlsx")Use data_only=True only when you want the cached result last stored by a spreadsheet application rather than the formula expression. It does not cause openpyxl to calculate a formula. If you need fresh calculated values, open and recalculate the saved workbook in Excel or another compatible calculation engine, then check the values there.
Preserve VBA content in an .xlsm file
For a macro-enabled workbook, load with keep_vba=True and save with a macro-enabled extension such as .xlsm:
from openpyxl import load_workbook
wb = load_workbook("input.xlsm", keep_vba=True, data_only=False)
# make targeted changes
wb.save("output.xlsm")The openpyxl documentation says that keep_vba controls whether Visual Basic elements are preserved; preserved VBA is still not editable through openpyxl. Keep the input and output types aligned: mismatched template or workbook extensions can produce files Excel cannot open. Retaining the VBA project binary does not establish that a macro behaves correctly, so check the saved file in its intended Excel environment.
What happens to formatting and other workbook features?
Openpyxl can preserve and work with many common workbook elements, including cell styles and number formats, but a successful save is not proof that every part of a complex workbook survived. The current openpyxl tutorial warns that shapes may be lost; documentation has also warned about possible loss of images and charts. The risk depends on the features present in the particular file.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Before editing, make an inventory of anything the workbook relies on: formulas, number formats, conditional formatting, merged cells, charts, images, shapes, external links, named ranges, and VBA. Save to a separate output path because Workbook.save() overwrites an existing path. Compare representative formulas and styles after saving, then inspect the workbook in its target spreadsheet application.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Append DataFrames with pandas carefully
Use pandas when the data you need to write is naturally represented as a DataFrame and rewriting the workbook is acceptable. For an existing workbook, ExcelWriter append mode uses openpyxl. Current pandas development documentation warns that append mode rewrites the workbook and may drop content the engine cannot represent; exact behavior can vary by pandas release, so verify the version in use.
import pandas as pd
with pd.ExcelWriter(
"output.xlsx",
mode="a",
engine="openpyxl",
if_sheet_exists="overlay",
) as writer:
df.to_excel(writer, sheet_name="Sheet1", index=False, startrow=10)Choose if_sheet_exists deliberately. The overlay option writes without first removing existing sheet content, which is useful for filling a planned range but can overwrite data if the target coordinates collide. For macro-enabled append workflows, pass engine_kwargs={"keep_vba": True} as appropriate and save with an .xlsm output path; validate both workbook features and macro behavior afterward.
When XlsxWriter is the better fit
Choose XlsxWriter to create a new workbook with formatting and other supported output features, not to edit an existing template. Its FAQ states that it cannot read or modify an existing Excel file. It can write formulas, but it does not calculate them: by default, the cached result is zero and spreadsheet software is asked to recalculate when the file opens. Some viewers that do not calculate formulas may therefore show zero until the workbook is recalculated.
Free tools Windows power users keep installed
One-click scans. No signup required.
XlsxWriter can also add an extracted VBA project binary to a newly written workbook. That is not the same as loading and preserving an arbitrary existing macro-enabled workbook.
Quick Recap
Best Value
Validate the saved workbook before relying on it
- Start with a copy. Record the file type and the workbook features that must remain intact; save edits to a new path.
- Reopen the output with openpyxl. Check representative formula strings and cell styles or number formats.
- Open it in the target spreadsheet application. Inspect features that may not round-trip, such as shapes, images, and charts, and recalculate formulas if fresh results are needed.
- For macro-enabled workbooks, check the VBA project and run the relevant macro in the intended Excel environment. A preserved binary project alone does not confirm that the workbook operates correctly.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
