How to Use Python with Excel: A Step-by-Step Guide

Quick Summary: In this tutorial, you’ll learn how to read, write, and format Excel files with Python, using pandas, openpyxl, and xlwings. By the end, you’ll have a working script that replaces a repetitive manual Excel task.

Introduction

When I first started connecting Python to Excel, I was a VBA person. I had macros held together with hope, and one renamed column could break everything.

Python fixed that for me. The syntax is easier to read, the error messages are clearer, and the same skills carry over to databases, APIs, and web data. VBA only works inside Office.

In this guide, I’ll walk you through the exact workflow I use in my daily automation work. You don’t need advanced programming knowledge, just basic comfort running a script.

Prerequisites & Best Libraries Needed

You need three things before you start:

• Python 3.9 or newer installed on your computer
• A code editor such as VS Code (free and beginner-friendly)
• A sample Excel file to practice on (never practice on your only copy of important data)

Python has several Excel libraries, but these three cover almost everything I do:

• pandas: Best for reading, cleaning, and summarizing large tables of data. It’s the fastest way to get data in and out of Excel.
• openpyxl: Best for working with the file itself, such as fonts, colors, column widths, and formulas. It reads and writes .xlsx files without Excel installed.
• xlwings: Best for controlling a live, open Excel window. It needs Excel installed (Windows or Mac) but can run macros and update workbooks in real time.

My rule of thumb: use pandas for data, openpyxl for formatting, and xlwings only when I need Excel itself to do something. Most beginners never need xlwings at first.

Step 1: Setting Up Your Python Environment

Open your terminal (Command Prompt or PowerShell on Windows, Terminal on Mac) and create a project folder:

bash

mkdir excel-automation

cd excel-automation

I recommend a virtual environment, which keeps this project’s libraries separate from everything else on your machine:

bash

# Create the virtual environment

python -m venv venv

# Activate it on Windows

venv\Scripts\activate

# Activate it on Mac/Linux

source venv/bin/activate

Now install the libraries:

bash

pip install pandas openpyxl xlwings

Confirm everything installed correctly:

bash

python -c “import pandas, openpyxl; print(‘All set!’)”

If you see All set!, you’re ready.

[Image: Terminal showing successful pip install of pandas and openpyxl]

Step 2: Reading Excel Data with Python

Let’s say you have a file called sales_data.xlsx with a sheet named Sales. It has columns for Date, Region, Product, and Revenue.

Put the file in your project folder, then create a script called read_excel.py:

python

import pandas as pd

# Load the “Sales” sheet into a DataFrame (think of it as a table in memory)

df = pd.read_excel(“sales_data.xlsx”, sheet_name=”Sales”)

# Preview the first 5 rows to confirm the data loaded correctly

print(df.head())

# Check column names and data types

print(df.info())

# Quick summary: total revenue per region

region_totals = df.groupby(“Region”)[“Revenue”].sum()

print(region_totals)

Run it from your terminal:

bash

python read_excel.py

[Image: Python script reading Excel data in VS Code]

A DataFrame is simply a table with rows and columns, like a worksheet that lives in memory. Once your data is in one, you can filter, sort, and summarize it in a single line.

A few useful variations:

• Read every sheet at once: pd.read_excel(“file.xlsx”, sheet_name=None) returns a dictionary of DataFrames.
• Skip junk rows at the top: pd.read_excel(“file.xlsx”, skiprows=2).
• Load only the columns you need: usecols=[“Date”, “Revenue”].

A tip from experience: always run df.info() first. It shows right away if a “number” column was actually read as text, which is the cause of about half the weird bugs I see.

Step 3: Writing and Updating Excel Files Automatically

Reading is useful, but writing is where automation starts to save real time. There are two common jobs: creating a new file, and updating an existing one.

Creating a New Excel File

Here’s how to filter your data and save the result to a fresh workbook:

python

import pandas as pd

df = pd.read_excel(“sales_data.xlsx”, sheet_name=”Sales”)

# Keep only high-value sales

big_sales = df[df[“Revenue”] > 1000]

# Build a summary table by region

summary = df.groupby(“Region”, as_index=False)[“Revenue”].sum()

# Write both tables to separate sheets in ONE new file

with pd.ExcelWriter(“sales_report.xlsx”, engine=”openpyxl”) as writer:

    big_sales.to_excel(writer, sheet_name=”Big Sales”, index=False)

    summary.to_excel(writer, sheet_name=”Summary”, index=False)

print(“Report created!”)

Notice index=False. Without it, pandas adds an extra column of row numbers, and it’s the most common beginner annoyance.

Updating an Existing File

To add data without wiping out the rest of the workbook, use openpyxl directly:

python

from openpyxl import load_workbook

# Open the existing workbook

wb = load_workbook(“sales_data.xlsx”)

ws = wb[“Sales”]

# Append a new row at the bottom of the data

ws.append([“2025-01-15”, “North”, “Widget A”, 1250])

# Change a specific cell

ws[“E1”] = “Notes”

# Save under a NEW name so the original stays safe

wb.save(“sales_data_updated.xlsx”)

Always save to a new filename while testing. I learned this the hard way after overwriting a client’s workbook with a half-finished script. Once your script is solid, you can save over the original.

[Image: Before and after view of an updated Excel sheet]

Step 4: Automating Excel Formatting & Formulas

Here’s a real task I automate often: a weekly sales report that needs to look professional before it goes to a manager. Doing this by hand takes 15 minutes. With Python it takes about two seconds.

python

from openpyxl import load_workbook

from openpyxl.styles import Font, PatternFill, Alignment

from openpyxl.utils import get_column_letter

wb = load_workbook(“sales_report.xlsx”)

ws = wb[“Summary”]

# — Style the header row —

header_fill = PatternFill(start_color=”1F4E78″, end_color=”1F4E78″, fill_type=”solid”)

header_font = Font(bold=True, color=”FFFFFF”)

for cell in ws[1]:  # ws[1] is the first row

    cell.fill = header_fill

    cell.font = header_font

    cell.alignment = Alignment(horizontal=”center”)

# — Format revenue as currency —

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):

    for cell in row:

        cell.number_format = ‘”$”#,##0.00’

# — Add a total row using a real Excel formula —

last_row = ws.max_row

ws[f”A{last_row + 1}”] = “TOTAL”

ws[f”B{last_row + 1}”] = f”=SUM(B2:B{last_row})”

ws[f”A{last_row + 1}”].font = Font(bold=True)

ws[f”B{last_row + 1}”].font = Font(bold=True)

# — Auto-fit column widths —

for col_idx, column in enumerate(ws.columns, start=1):

    longest = max(len(str(cell.value or “”)) for cell in column)

    ws.column_dimensions[get_column_letter(col_idx)].width = longest + 4

# Freeze the header so it stays visible while scrolling

ws.freeze_panes = “A2”

wb.save(“sales_report_formatted.xlsx”)

print(“Formatted report saved!”)

Here’s what this script does:

1. Styles the header with a dark blue background and white bold text.
2. Applies currency formatting to the revenue column.
3. Inserts a live formula (=SUM(…)), so the total still updates if someone edits the numbers later.
4. Auto-fits column widths so nothing is cut off.
5. Freezes the top row for easier scrolling.

[Image: Final formatted Excel report with blue headers and total row]

Important: openpyxl writes the formula but doesn’t calculate it. The value appears the moment someone opens the file in Excel, so don’t be alarmed if Python shows None when reading it back.

Common Pitfalls & How to Fix Them

These are the errors I see most often, both in my own scripts and when helping beginners.

1. “PermissionError: [Errno 13] Permission denied”

This almost always means the Excel file is still open in Excel. Windows locks open files, so Python can’t write to them.

Fix: Close the workbook in Excel, then run the script again. Also check for hidden ~$filename.xlsx temporary files in the same folder.

2. “ModuleNotFoundError: No module named ‘openpyxl'”

Python can’t find the library. Usually you installed it in one environment and ran the script in another.

Fix: Activate your virtual environment, then run pip install openpyxl again. In VS Code, also check that the correct Python interpreter is selected in the bottom-right corner.

3. “ValueError: Worksheet named ‘Sales’ not found”

The sheet name in your code doesn’t match the one in the file. A trailing space or a capital letter is enough to cause this.

Fix: Print the real sheet names first:

python

import pandas as pd

# Lists every sheet name exactly as Excel stores it

print(pd.ExcelFile(“sales_data.xlsx”).sheet_names)

Then copy the name exactly as shown.

4. Old .xls files won’t open

Modern openpyxl only works with .xlsx.

Fix: Open the old file in Excel and save it as .xlsx, or install the xlrd library to read legacy .xls files with pandas.

5. Formatting disappears after saving with pandas

If you load a styled workbook with pandas and re-save it, pandas rewrites the file and your formatting is lost.

Fix: Use pandas for data, then apply formatting afterward with openpyxl. That’s why my scripts almost always use both.

Final Verdict / Next Steps

Python with Excel is one of the most practical skills you can pick up for office work. You don’t need to learn everything at once, because one small script is enough to prove its value.

Here’s what I’d suggest doing this week:

1. Pick one repetitive task, such as a weekly report, a data cleanup, or a copy-paste routine.
2. Install pandas and openpyxl using the commands from Step 1.
3. Start with just reading your file and printing the first few rows.
4. Add one feature at a time: filtering, then writing, then formatting.
5. Save your script and rerun it next week instead of repeating the work by hand.

Your first script won’t be perfect, and mine weren’t either. But the first time you run it and a finished report appears in seconds, you’ll understand why I stopped writing VBA.

If you want to go further, look into xlwings for live Excel control and scheduled scripts that run automatically every morning. Start small, test often, and build from there.

Frequently Asked Questions

Can Python completely replace Excel VBA?

For most automation tasks, yes. Python handles data cleaning, reporting, and file processing more cleanly than VBA, though VBA still has an edge for tiny in-Excel tweaks like custom buttons.

Do I need Excel installed to use Python with Excel files?

Not for pandas or openpyxl, which read and write .xlsx files directly. You only need Excel installed if you use xlwings, since it controls the actual Excel application.

Which is better: pandas or openpyxl?

They do different jobs. Use pandas for analyzing and transforming data, and openpyxl for styling cells, adding formulas, and editing existing workbooks.

Is Python fast enough for large Excel files?

Yes, pandas handles hundreds of thousands of rows far more smoothly than most manual Excel workflows. For truly huge datasets, consider saving to CSV, which loads much faster than .xlsx.