Python Automating Excel Tasks

In the last lesson you learned Automating Files. There Python moved, renamed, and sorted files for you. Excel files are a special case. You could open one by hand. But a spreadsheet is really just rows and columns of data. And anything that is rows and columns, Python can fill in for you. So in this lesson we let Python build and read a real Excel report.

🤔 Why Automate Excel?

Picture this. Every Monday, Alex copies sales numbers into a spreadsheet. Then types out the headers. Then totals the column and saves the file. It takes twenty minutes. And it is the same twenty minutes every single week.

That is the pain. Repeating the same spreadsheet work by hand.

The fix is one line. You let Python write the rows and read them back for you. You write the steps once. Then you run it whenever you want a fresh report.

To do this we use a third-party library called openpyxl. Third-party means it does not come with Python. So you install it once before you use it.

📦 Installing openpyxl

openpyxl is the library that reads and writes modern Excel files (the .xlsx format). Install it with pip in your terminal.

pip install openpyxl

🧩 The Three Words You Need

Before the code, here are the three openpyxl words that show up everywhere. Think of a real Excel file you have opened.

So a workbook holds worksheets. And a worksheet holds cells. That is the whole mental model.

✍️ Writing an Excel File

Let’s build a small sales report. We will create a new file, add a header row, write three rows of sales, total them, and save it.

Here is the full script first. Then we walk through it line by line.

from openpyxl import Workbook

# Create a new, empty workbook
wb = Workbook()

# Grab the worksheet that comes with every new workbook
ws = wb.active
ws.title = "Sales"

# Write the header row
ws.append(["Product", "Units", "Revenue"])

# Write the sales rows
ws.append(["Notebook", 120, 600])
ws.append(["Pen", 300, 150])
ws.append(["Backpack", 45, 1350])

# Add a total row using an Excel formula
ws.append(["Total", "=SUM(B2:B4)", "=SUM(C2:C4)"])

# Save the file to disk
wb.save("sales_report.xlsx")

print("Saved sales_report.xlsx")

Now the walkthrough, line by line.

• Workbook() creates a brand new file in memory. Nothing is on disk yet.
• wb.active gives you the first worksheet, which every new workbook already has.
• ws.title = "Sales" renames that tab from the default “Sheet” to “Sales”.
• ws.append([...]) adds a whole row at once. The list items become cells left to right. The first append lands in row 1. The next in row 2. And so on.
• "=SUM(B2:B4)" is a normal Excel formula written as text. When you open the file, Excel calculates it for you.
• wb.save(...) is the moment the file is actually written to disk. Forget this line and you get nothing.

Run it and you see this in the terminal.

Saved sales_report.xlsx

Open sales_report.xlsx in Excel and the “Sales” tab looks like this.

🎯 Writing to a Specific Cell

append is great for whole rows. But sometimes you want to set one exact box. For that, use the cell’s address like a key.

This sets cell E1 directly and reads it back.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Set one cell by its address
ws["E1"] = "Report by Alex"

# You can also use row and column numbers (both start at 1)
ws.cell(row=2, column=5, value="Generated automatically")

print(ws["E1"].value)
print(ws.cell(row=2, column=5).value)

The output shows both ways read the same kind of data back.

Report by Alex
Generated automatically

So you have two ways to reach a cell. Use the "E1" style when you know the letter-number address. Use ws.cell(row=..., column=...) when your row and column come from a loop or a counter.

📖 Reading an Excel File

Writing is half the job. The other half is opening a file someone already made and pulling the numbers out. For that we use load_workbook instead of Workbook.

This opens the file we just saved and prints every row.

from openpyxl import load_workbook

# Open an existing file
wb = load_workbook("sales_report.xlsx")
ws = wb["Sales"]   # pick the worksheet by its tab name

# Read one specific cell
print("Top-left cell:", ws["A1"].value)

# Walk through every row of values
print("\nAll rows:")
for row in ws.iter_rows(values_only=True):
    print(row)

Here is what prints. Note the total row shows the formula text. That is because openpyxl reads the file without running Excel’s calculator.

Top-left cell: Product

All rows:
('Product', 'Units', 'Revenue')
('Notebook', 120, 600)
('Pen', 300, 150)
('Backpack', 45, 1350)
('Total', '=SUM(B2:B4)', '=SUM(C2:C4)')

A few things worth pulling out from that code.

• load_workbook("sales_report.xlsx") opens a file that already exists on disk.
• wb["Sales"] picks the worksheet by the tab name you gave it earlier.
• ws["A1"].value gets the value inside a cell. The .value part matters. Without it you get the cell object, not the text.
• ws.iter_rows(values_only=True) walks the sheet row by row and hands you a plain tuple of values each time. That values_only=True is what gives you the clean tuples instead of cell objects.

🔁 A Real Loop: Build a Report From Data

In real life your numbers live in a Python list or come from a database. You do not type them one by one. Here is the common pattern. You hold the data in Python. Then you loop it into the sheet and total it yourself.

This builds the same report from a list. It writes a real total number, not a formula. So reading it back gives you the number.

from openpyxl import Workbook

sales = [
    ("Notebook", 120, 600),
    ("Pen", 300, 150),
    ("Backpack", 45, 1350),
]

wb = Workbook()
ws = wb.active
ws.title = "Sales"

ws.append(["Product", "Units", "Revenue"])

total_revenue = 0
for product, units, revenue in sales:
    ws.append([product, units, revenue])
    total_revenue += revenue

# Write the total we computed in Python
ws.append(["Total", "", total_revenue])

wb.save("sales_report.xlsx")
print(f"Report saved. Total revenue: {total_revenue}")

The terminal confirms the run with the total computed in Python.

Report saved. Total revenue: 2100

Because we did the math in Python, that 2100 is a real number in the file. Read it back later and you get 2100, not a formula.

⚠️ Common Mistakes

A few traps catch almost everyone the first time.

• Forgetting to save. All your append and cell edits live in memory until wb.save(...). No save, no file.

# ❌ Avoid: edits are never written
ws.append(["Pen", 300, 150])

# ✅ Good: actually write the file
ws.append(["Pen", 300, 150])
wb.save("sales_report.xlsx")

• Reading the cell instead of its value. ws["A1"] is the cell object. You almost always want .value.

# ❌ Avoid: prints a Cell object, not the text
print(ws["A1"])

# ✅ Good: prints the actual content
print(ws["A1"].value)

• Expecting openpyxl to calculate formulas. It reads and writes only. A =SUM(...) stays as text until Excel opens it. If you need the number in Python, compute it in Python.
• Using the wrong row or column number. In openpyxl, rows and columns start at 1, not 0. So the first cell is row=1, column=1, which is A1.

✅ Best Practices

Small habits that keep your Excel scripts clean.

• Always import from openpyxl at the top, and pick the right tool. Use Workbook to create. Use load_workbook to open.
• Compute totals in Python when you plan to read them back. Use Excel formulas only when a person will open the sheet.
• Give your worksheet a clear ws.title instead of leaving the default “Sheet”. It makes the file much easier to understand when you come back to it later.
• Use append for rows of data. Use the "A1" style only for one-off cells like a title or a label.
• Keep the data in a Python list and loop it into the sheet. That way the same script works for three rows or three thousand.

🧩 What You’ve Learned

✅ openpyxl is a third-party library you install with pip install openpyxl to read and write .xlsx files.

✅ A workbook is the file, a worksheet is a tab, and a cell is one box found by an address like A1.

✅ Use Workbook() and ws.append([...]) to build rows, then wb.save(...) to write the file to disk.

✅ Set single cells with ws["A1"] = ... or ws.cell(row=..., column=..., value=...).

✅ Use load_workbook(...), then ws["A1"].value and ws.iter_rows(values_only=True) to read data back.

✅ openpyxl does not calculate formulas, so compute totals in Python when you need the number.

🚀 What’s Next?

You can now make Python build and read spreadsheets on its own. Next we let Python send messages for you. So a finished report can land in someone’s inbox automatically.

Sending Emails with Python