Python in Excel vs openpyxl and xlwings

"Python and Excel" now means three quite different things. Python in Excel puts Python inside a workbook: a =PY formula runs in a Microsoft cloud container and returns its result to a cell. openpyxl, xlsxwriter and pandas run Python outside Excel: a script reads and writes .xlsx files without Excel installed at all. xlwings (and pywin32 underneath it on Windows) runs Python alongside Excel: a script drives the running application as if a person were clicking. They overlap less than the shared name suggests, and choosing the wrong one produces a solution that fights its tool at every step. This guide from Using Python in Excel compares them on the questions that decide real projects.

The same job, three ways

A concrete task makes the differences tangible: summarise a sales table by region and put the result in a workbook.

Python in Excel — a formula in the workbook itself, set to return Excel values:

sales = xl("Sales[#All]", headers=True)
sales.groupby("Region", as_index=False)["Amount"].sum().sort_values("Amount", ascending=False)

pandas with openpyxl — a script that reads one file and writes another, on any machine:

import pandas as pd

pd.DataFrame({
    "Region": ["North", "South", "North", "West"],
    "Amount": [1200.0, 950.0, 800.0, 400.0],
}).to_excel("sales.xlsx", sheet_name="Sales", index=False)

sales = pd.read_excel("sales.xlsx", sheet_name="Sales")
summary = sales.groupby("Region", as_index=False)["Amount"].sum().sort_values("Amount", ascending=False)
with pd.ExcelWriter("summary.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="By region", index=False)
print(summary)

xlwings — a script that opens the workbook in Excel, writes into it and lets Excel recalculate:

import pandas as pd
import xlwings as xw

with xw.App(visible=False) as app:
    book = app.books.open("sales.xlsx")
    sales = book.sheets["Sales"].range("A1").expand().options(pd.DataFrame, index=False).value
    summary = sales.groupby("Region", as_index=False)["Amount"].sum()
    sheet = book.sheets.add("By region")
    sheet.range("A1").options(index=False).value = summary
    book.save("summary_live.xlsx")

The pandas logic is identical in all three. What differs is everything around it: who runs the code, when, on what machine, and what happens to the result afterwards.

Question 1 — Who triggers the work, and when?

Python in Excel runs when the workbook calculates: when someone opens it, edits an input, refreshes a query or presses F9. That is ideal for analysis a person is actively doing, and useless for a report that must exist at 06:00 before anyone is awake. Nobody is there to open the workbook.

A pandas or openpyxl script runs whenever something starts it — cron, Windows Task Scheduler, a CI job, a web request — which is exactly what scheduled reporting needs; see Scheduling Python Excel Scripts with Cron. xlwings can be scheduled too, but it needs an interactive desktop with Excel installed, which makes unattended runs fragile.

Question 2 — What data can the code reach?

This is the second hard boundary. Python in Excel sees only what xl() can reference: cells, tables, names and Power Query results in the same workbook. It cannot open files, call APIs or query databases directly, and each calculation's data is capped at 100 MB. That sandbox is deliberate — it is why an organisation can allow it at all — and it means external data must come in through Power Query first.

Scripts reach anything the machine can: databases through SQLAlchemy, REST APIs through requests, cloud storage, other workbooks by the hundred. If the job starts with "pull yesterday's orders from the warehouse", a script is the natural fit, as in Moving Data Between Excel and Databases.

Question 3 — Which packages can you use?

Python in Excel ships a curated Anaconda-based set with pandas, NumPy, matplotlib, seaborn and statsmodels imported by default and more available to import, but nothing can be installed. Your own internal library, a niche statistics package or a newer release of something already present is not available. Scripts can use any package the environment can install, pinned to exact versions in a requirements file, which is also what makes their output reproducible six months later.

Question 4 — What does the output look like, and who can open it?

Python in Excel produces results in a live workbook: values that update when inputs change, and images of charts. Anyone who opens the file in a client without Python in Excel support sees error values instead of results, so a workbook that leaves your team usually needs its results converted to static values first.

openpyxl and xlsxwriter produce ordinary files that open anywhere — Excel on any platform, LibreOffice, Google Sheets — with native charts, conditional formats and tables that need nothing special to display. xlwings produces the same ordinary files, but because Excel itself saved them, formulas are recalculated and connections refreshed, which file-level libraries cannot do.

Question 5 — Who maintains it?

A Python in Excel workbook keeps its code next to its data, which is a genuine advantage for an analyst who owns both: there is no separate repository, environment or deployment. It is also a maintenance risk at scale. Code spread across cells is hard to review, has no version history beyond the file itself, and cannot be unit-tested in place.

A script lives in version control, runs in CI, and can be tested with ordinary tools — the practices in Testing and Packaging Excel Automation Scripts. The cost is that someone has to own an environment. A reasonable rule: if more than one person edits the logic, or if the output feeds a decision someone will be held to, it belongs in a script.

Combining them

The approaches compose well when each does what it is good at. A scheduled pandas script can build a clean, formatted workbook every morning from the database, and analysts can then add Python in Excel formulas on top for their own exploration, without either side touching the other's work. The script can even write its data as an Excel table so that xl("Sales[#All]", headers=True) picks up every refresh, using the technique in Create an Excel Table with Python.

import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

data = pd.DataFrame({"Date": pd.date_range("2026-09-01", periods=4),
                     "Region": ["North", "South", "North", "West"],
                     "Rep": ["Ana", "Cara", "Ben", "Dev"],
                     "Amount": [1200.0, 950.0, 800.0, 400.0]})
data.to_excel("analysis_base.xlsx", sheet_name="Data", index=False)

book = load_workbook("analysis_base.xlsx")
sheet = book["Data"]
table = Table(displayName="Sales", ref=f"A1:D{len(data) + 1}")
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
sheet.add_table(table)
book.save("analysis_base.xlsx")
print("wrote table Sales with", len(data), "rows")

Where xlwings fits into that picture is the narrow set of steps that need Excel itself: refreshing a Power Query connection, recalculating a model built from Excel formulas, or running a legacy macro — covered in When to Use xlwings Instead of openpyxl.

Common pitfalls

Performance and scale notes

Python in Excel round-trips data to the cloud and caps each calculation at 100 MB, so it suits analysis of workbook-sized data rather than bulk processing. File-level libraries scale with the machine and with techniques such as streaming writes — see Working with Large Excel Files in Python. xlwings is bounded by Excel's own speed and by the cost of crossing the process boundary, so it should move data in whole ranges rather than cell by cell.

Conclusion

Choose by where the work has to happen. Python in Excel is for interactive analysis inside a workbook by the people who use it, with data that already lives there. openpyxl, xlsxwriter and pandas are for scheduled, reproducible file generation that must run without Excel and reach external systems. xlwings is for the steps that genuinely need the Excel application — recalculation, refresh, macros. Many real setups use two of them: a script to produce the data, and in-cell Python for the questions people ask of it.

Frequently asked questions

Does Python in Excel replace xlwings? No. Python in Excel runs code inside cells in the cloud; xlwings controls Excel from a local script. xlwings can automate the application, reach local files and networks, and run on a schedule, none of which Python in Excel does.

Can Python in Excel read the files my openpyxl script writes? Not directly from disk. Open the generated workbook and reference its tables with xl(), or load it into another workbook through Power Query and reference the query.

Is Python in Excel free? It is included with eligible Microsoft 365 plans, and Microsoft offers a paid add-on for faster premium compute. Check the current plan list, because availability has widened over time. openpyxl, pandas and xlwings' open-source edition cost nothing but need a Python environment.

Which is best for a beginner? For analysis of data already in a workbook, Python in Excel has the least setup. For automating a recurring report, start with pandas and openpyxl, which teach skills that transfer to every other kind of automation.

Related

• Up one level: Using Python in Excel — the full guide to =PY formulas.
• Choosing a Python Excel Library — the file-level libraries compared with each other.
• When to Use xlwings Instead of openpyxl — the case for driving the application.
• openpyxl vs pandas for Excel Automation — the two script-side tools head to head.
• Reference Cells, Ranges and Tables with xl() in Python in Excel — the input side of in-cell Python.