Authors Note: I am learning as I write this series, so I might make mistakes or do things that are not optimal. If you find any of these situations, please comment so readers have the correct or better knowledge and I too can learn.

Key Takeaway: openpyxl is a powerful Python library that lets you create, read, and modify Excel files without needing Microsoft Excel installed. Perfect for automating repetitive spreadsheet tasks!

Excel spreadsheets are everywhere in the business world, and as a Python programmer, you’ll often need to work with them. Whether you’re analyzing sales data, creating reports, or automating data entry, the openpyxl library is your gateway to Excel automation. This guide will teach you everything you need to know to start working with Excel files using Python.

What is openpyxl?

openpyxl is a Python library that allows you to read, write, and modify Excel 2010 xlsx/xlsm/xltx/xltm files . Think of it as your Python toolkit for Excel automation. Unlike other libraries, openpyxl doesn’t require Microsoft Excel to be installed on your computer, making it perfect for servers and automated systems.

Why Use openpyxl?

• No Excel Required: Works without Microsoft Excel installed • Full Feature Support: Handles formulas, charts, styling, and more • Active Development: Regularly updated and well-maintained • Beginner Friendly: Simple syntax that’s easy to learn

Excel Terminology Made Simple

Before diving into code, let’s understand the basic Excel terms you’ll encounter:

Term	Explanation	Example
Workbook	The main Excel file you work with	`mydata.xlsx`
Worksheet	A single sheet within a workbook	Sheet1, Sheet2
Column	Vertical line, labeled with letters	A, B, C, D…
Row	Horizontal line, labeled with numbers	1, 2, 3, 4…
Cell	Intersection of column and row	A1, B2, C3…

Getting Started: Installation

Installing openpyxl is straightforward. Open your terminal or command prompt and run:

pip install openpyxl

That’s it! You’re ready to start working with Excel files in Python.

Creating Your First Excel File

Let’s start with the basics - creating a new Excel workbook and adding some data:

from openpyxl import Workbook

# Create a new workbook
workbook = Workbook()
sheet = workbook.active

# Add some data
sheet["A1"] = "Product"
sheet["B1"] = "Price" 
sheet["C1"] = "Stock"

# Add product information
sheet["A2"] = "Laptop"
sheet["B2"] = 999.99
sheet["C2"] = 15

# Save the file
workbook.save("products.xlsx")

This creates a new Excel file called products.xlsx with a simple product table .

Reading Existing Excel Files

Reading data from existing Excel files is just as simple:

from openpyxl import load_workbook

# Load an existing workbook
workbook = load_workbook("products.xlsx")
sheet = workbook.active

# Read cell values
product_name = sheet["A2"].value
price = sheet["B2"].value

print(f"Product: {product_name}, Price: ${price}")
# Output: Product: Laptop, Price: $999.99

Product: Laptop, Price: $999.99

Reading Multiple Cells at Once

For larger datasets, you can iterate through rows efficiently:

# Read all data from the spreadsheet
for row in sheet.iter_rows(values_only=True):
    print(row)

('Product', 'Price', 'Stock')
('Laptop', 999.99, 15)

This prints each row as a tuple, making it easy to process data in bulk .

Working with Cells and Data

Accessing Cells in Different Ways

openpyxl gives you multiple ways to access cells:

# Method 1: Cell reference (like in Excel)
sheet["A1"] = "Hello"
value = sheet["A1"].value

# Method 2: Row and column numbers (1-based indexing)
sheet.cell(row=1, column=1, value="Hello")
value = sheet.cell(row=1, column=1).value

# Method 3: Appending rows (great for adding data)
sheet.append(["Mouse", 29.99, 150])

# Read all data from the spreadsheet
for row in sheet.iter_rows(values_only=True):
    print(row)

('Hello', 'Price', 'Stock')
('Laptop', 999.99, 15)
('Mouse', 29.99, 150)

Data Types and Conversion

openpyxl automatically handles different data types:

• Text: sheet["A1"] = "Product Name" • Numbers: sheet["B1"] = 299.99 • Dates: sheet["C1"] = datetime.date(2024, 1, 15) • Formulas: sheet["D1"] = "=B1*C1"

Adding Style and Formatting

Making your spreadsheets look professional is important. Here’s how to add formatting:

from openpyxl.styles import Font, PatternFill, Alignment

# Make headers bold and centered
sheet["A1"].font = Font(bold=True)
sheet["A1"].alignment = Alignment(horizontal='center')

# Add background color
sheet["A1"].fill = PatternFill(start_color="366092", 
                               end_color="366092", 
                               fill_type="solid")

# Format numbers as currency
sheet["B2"].number_format = '$#,##0.00'

Common Formatting Options

Format Type	Code Example	Result
Bold Text	`Font(bold=True)`	Bold
Currency	`'$#,##0.00'`	$1,234.56
Percentage	`'0.00%'`	25.50%
Date	`'YYYY-MM-DD'`	2024-01-15

Working with Multiple Worksheets

Most real-world Excel files have multiple sheets. Here’s how to manage them:

# Create additional sheets
data_sheet = workbook.create_sheet(title="Sales Data")
summary_sheet = workbook.create_sheet(title="Summary")

# Switch between sheets
current_sheet = workbook["Sales Data"]

# List all sheet names
print(workbook.sheetnames)  # ['Sheet', 'Sales Data', 'Summary']

# Rename a sheet
summary_sheet.title = "Monthly Summary"

['Sheet', 'Sales Data', 'Summary']

Adding Formulas and Calculations

One of Excel’s most powerful features is automatic calculations. openpyxl makes this easy:

# Add formulas (Excel will calculate them)
sheet["D1"] = "Total Value"
sheet["D2"] = "=B2*C2"  # Price × Stock

# Create summary calculations
sheet["B5"] = "=SUM(B2:B4)"  # Sum of prices
sheet["C5"] = "=AVERAGE(C2:C4)"  # Average stock

Important Note: openpyxl stores formulas as text. Excel calculates the results when you open the file .

Creating Simple Charts

Visual data representation makes reports more compelling:

from openpyxl.chart import BarChart, Reference

# Create a bar chart
chart = BarChart()
chart.title = "Product Stock Levels"
chart.x_axis.title = "Products"
chart.y_axis.title = "Stock Quantity"

# Define data range
data = Reference(sheet, min_col=3, min_row=1, max_row=4)
categories = Reference(sheet, min_col=1, min_row=2, max_row=4)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# Add chart to worksheet
sheet.add_chart(chart, "E2")
# Save the workbook
workbook.save("products.xlsx")

Best Practices for Beginners

Essential Tips

• Always save your workbook: Use workbook.save("filename.xlsx") after making changes • Use descriptive variable names: student_worksheet instead of ws1 • Test with small datasets first: Verify your code works before processing large files • Handle errors gracefully: Use try-except blocks for file operations • Keep backups: Always backup important files before modifying them

Common Mistakes to Avoid

Mistake	Why It Happens	Solution
Forgetting to save	Code runs but file doesn’t update	Always call `workbook.save()`
Wrong file extension	Using `.xls` instead of `.xlsx`	openpyxl only works with `.xlsx`
1-based vs 0-based indexing	Excel uses 1-based indexing	Remember: A1 is row=1, column=1

Practical Example: Sales Report

Let’s put everything together with a real-world example:

from openpyxl import Workbook
from openpyxl.styles import Font
import datetime

# Create sales report
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# Add headers with formatting
headers = ['Date', 'Product', 'Quantity', 'Unit Price', 'Total']
ws.append(headers)

# Make headers bold
for cell in ws[1]:
    cell.font = Font(bold=True)

# Add sales data
sales_data = [
    [datetime.date(2024, 1, 15), 'Laptop', 2, 999.99],
    [datetime.date(2024, 1, 16), 'Mouse', 5, 29.99],
    [datetime.date(2024, 1, 17), 'Keyboard', 3, 79.99]
]

for row in sales_data:
    ws.append(row)

# Add total formulas
for row in range(2, 5):
    ws.cell(row=row, column=5).value = f'=C{row}*D{row}'
    ws.cell(row=row, column=5).number_format = '$#,##0.00'
    ws.cell(row=row, column=4).number_format = '$#,##0.00'

# Save the report
wb.save('sales_report.xlsx')

Quick Reference Table

Task	Code Example	Description
Create workbook	`wb = Workbook()`	New Excel file
Load workbook	`wb = load_workbook('file.xlsx')`	Open existing file
Access sheet	`ws = wb.active`	Get current sheet
Read cell	`value = ws['A1'].value`	Get cell value
Write cell	`ws['A1'] = 'Hello'`	Set cell value
Add row	`ws.append(['A', 'B', 'C'])`	Add data row
Save file	`wb.save('file.xlsx')`	Save changes

Conclusion

Working with Excel spreadsheets in Python using openpyxl opens up endless possibilities for data automation and analysis. You’ve learned how to create, read, and modify Excel files, add formatting and charts, and follow best practices that will serve you well in real-world projects.

The key to mastering openpyxl is practice. Start with simple tasks like reading data from existing files, then gradually work up to creating complex reports with multiple sheets, formulas, and charts. Remember to always test your code with small datasets first, handle errors properly, and keep backups of important files.

Ready to automate your Excel workflows? Start with the examples in this guide and gradually build more complex solutions. Your future self will thank you for learning this valuable skill!

References

Based on the research findings and best practices for digital content citations, here’s a properly formatted references section with four working, relevant clickable links for the Excel/openpyxl tutorial:

References

openpyxl Official Documentation - The comprehensive official documentation for the openpyxl library, including installation guides, API reference, and advanced features.
https://openpyxl.readthedocs.io/
A Guide to Excel Spreadsheets in Python With openpyxl – Real Python - An in-depth tutorial covering practical use cases, detailed code examples, and best practices for manipulating Excel spreadsheets using Python.
https://realpython.com/openpyxl-excel-spreadsheets-python/
Reading an Excel File Using Python openpyxl Module – GeeksforGeeks - A step-by-step beginner’s guide with code snippets for reading and extracting data from Excel files using the openpyxl library.
https://www.geeksforgeeks.org/python/python-reading-excel-file-using-openpyxl-module/
How to Read Excel File in Python using Openpyxl – Medium - An accessible tutorial explaining the fundamentals of reading Excel files, accessing worksheets, and retrieving data with openpyxl for Python beginners.
https://medium.com/@vidvatek/how-to-read-excel-file-in-python-using-openpyxl-354f3729b1cf

Have you tried automating Excel tasks with Python? Share your experiences and questions in the comments below, and don’t forget to share this guide with fellow Python beginners on social media!

Happy Coding! 🚀

You can connect with me at any one of the below:

Telegram Channel here: https://t.me/steveondata

LinkedIn Network here: https://www.linkedin.com/in/spsanderson/

Mastadon Social here: https://mstdn.social/@stevensanderson

RStats Network here: https://rstats.me/@spsanderson

GitHub Network here: https://github.com/spsanderson

Bluesky Network here: https://bsky.app/profile/spsanderson.com

My Book: Extending Excel with Python and R here: https://packt.link/oTyZJ

You.com Referral Link: https://you.com/join/EHSLDTL6