I've seen this exact scenario play out countless times with junior devs. You spend an hour writing a script to automate a tedious spreadsheet task, you run the code, the terminal says it finished successfully, but when you open the Excel file... nothing has changed. You start questioning your sanity or wondering if Python is just ignoring you.

The Mystery of the Missing Updates

import openpyxl

# Let's say we have a file called 'inventory.xlsx' 
# with product names in Col A and prices in Col B.
wb = openpyxl.load_workbook('inventory.xlsx')
sheet = wb.active

# I want to give everything a 10% price bump
for row in range(2, 6): 
    current_price = sheet.cell(row=row, column=2).value
    sheet.cell(row=row, column=2).value = current_price * 1.10

print("Prices updated successfully!")

If you run this, it works perfectly in your computer's memory. But the file on your hard drive remains untouched. The mistake here is a classic: openpyxl doesn't "live-sync" with the file. It loads a snapshot of the spreadsheet into a Python object. You can mangle that object however you like, but unless you explicitly tell Python to write those changes back to the disk, they vanish the moment the script ends.

Committing Changes to the Disk

To fix this, you need to call the .save() method on the workbook object (wb), not the worksheet object (sheet). This is a common point of confusion—remember that the workbook is the entire file, while the sheet is just one tab inside it.

# Add this to the bottom of the previous script
wb.save('inventory.xlsx')

Now the changes are permanent. I usually recommend saving to a new filename (like 'inventory_updated.xlsx') while you're testing, just so you don't accidentally overwrite your only copy of the data with a bugged calculation.

Targeting Specific Cells and Ranges

You'll notice in the example above I used sheet.cell(row=row, column=2). This is great for loops because it takes integers. But when you're just grabbing a single specific value, the "Excel-style" coordinate system is much faster to write.

If you know exactly where your data is, just use the coordinate string:

# Get the value of cell B2
price = sheet['B2'].value

# Set the value of cell C2
sheet['C2'] = "Updated"

I prefer .cell() for dynamic work and ['A1'] for static references. It keeps the code readable.

Looping Through Data Without Knowing the Size

Hardcoding range(2, 6) is a recipe for disaster. The moment your client adds a seventh row, your script misses it. Instead, use the max_row and max_column attributes. These tell you exactly where the data ends.

# This loops through every row that actually contains data
for row in range(2, sheet.max_row + 1):
    # We add 1 because range() is exclusive at the stop point
    cell_value = sheet.cell(row=row, column=1).value
    print(f"Processing item: {cell_value}")

One quick tip: openpyxl is powerful, but it can be slow with massive files (think 100k+ rows). If you find your script crawling, that's when you'd move to something like pandas, but for most day-to-day automation, openpyxl is exactly what you need because it preserves the formatting and styling of the original file.

📋 Practical Task

Exercise: The Monthly Sales Tax Calculator

You've been handed an Excel file named sales_report.xlsx. The file has three columns: Product Name (Col A), Unit Price (Col B), and Quantity Sold (Col C). Your boss wants you to add a fourth column, Total with Tax (Col D), which calculates: (Price * Quantity) * 1.07.

Your Task: Write a script that does the following:

• Loads sales_report.xlsx.
• Iterates through all rows (starting from row 2 to skip the headers).
• Calculates the total including a 7% tax for each row.
• Writes that result into Column D.
• Saves the result as a new file called sales_report_final.xlsx.