openpyxl vs pandas vs xlwings — which one when

1. Setup (one time)

1. Install Python 3.11 or newer from python.org (Windows: tick Add python.exe to PATH).
2. Open a terminal (Command Prompt / PowerShell / Terminal) in your project folder.
3. Create a virtual environment, so each project has its own libraries:

python -m venv .venv
# Windows
.venv\Scripts\activate
# Mac / Linux
source .venv/bin/activate

pip install pandas openpyxl xlwings

4. Use VS Code (with the Python extension) to write and run scripts.

2. The three libraries

3. How they work together

The most common pattern:

pandas reads + cleans + summarises  →  pandas writes the sheets  →  openpyxl formats them

pandas actually uses openpyxl behind the scenes to read and write .xlsx files.

4. Choosing — quick guide

5. A taste of each

import pandas as pd
df = pd.read_excel("sales.xlsx")
print(df.groupby("Region")["Amount"].sum())

from openpyxl import load_workbook
from openpyxl.styles import Font
wb = load_workbook("sales.xlsx")
ws = wb.active
ws["A1"].font = Font(bold=True)
wb.save("sales.xlsx")

import xlwings as xw
wb = xw.Book("sales.xlsx")          # opens it in Excel
print(wb.sheets[0]["A1"].value)

6. Two important facts about openpyxl

• It doesn't calculate formulas. It writes =SUM(B2:B10) correctly, and Excel calculates it when opened. Reading with load_workbook(path, data_only=True) gives the value Excel saved last time (empty if the file was never opened in Excel).
• It works only with .xlsx/.xlsm, not old .xls (use pandas.read_excel with the xlrd package for those).

7. What about "Python in Excel"?

Microsoft 365 has a =PY() function that runs Python inside a cell, in Microsoft's cloud. It's great for analysis inside a workbook, but it can't read your local files, send emails or run on a schedule. For automation, the libraries in this module are what you need.

Common mistakes

Installing libraries without activating the virtual environment. Using xlwings for a script that must run on a server. Expecting openpyxl to give formula results.