Python openpyxl: Cells, Rows, Columns, and Worksheets
Learn to read, write, and manipulate Excel cells, rows, columns, and worksheets with Python openpyxl.

Working with Excel files in Python often means manipulating cells, rows, columns, and worksheets programmatically. The openpyxl library provides a straightforward API for these tasks, whether you are generating reports, transforming data, or updating existing spreadsheets. This article covers the core operations you need to read and write Excel workbooks efficiently.
Accessing Cells Directly
The most basic operation is reading or writing a single cell. openpyxl gives you two equivalent ways to address a cell: by its coordinate string (like "A1") or by row and column numbers. Both approaches return a Cell object whose value attribute holds the data.
from openpyxl import load_workbook
wb = load_workbook("example.xlsx")
ws = wb.active
# Access by coordinate
cell_a1 = ws["A1"]
print(cell_a1.value)
# Access by row and column (1-based)
cell_b2 = ws.cell(row=2, column=2)
cell_b2.value = "New value"
# Save changes
wb.save("example.xlsx")When writing a value, you can assign directly to cell.value or pass the value to the cell() method as an additional argument. The cell() method is especially useful when you need to compute row and column indices dynamically, for example in a loop.
A common mistake is forgetting that openpyxl uses 1-based indexing. ws.cell(row=0, column=1) raises a ValueError. Always start from 1.
Iterating Over Rows and Columns
Real-world data rarely fits in a single cell. You often need to process entire rows or columns. openpyxl provides iter_rows() and iter_cols() to iterate over rectangular ranges, returning tuples of Cell objects.
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Fill a small grid
for row in range(1, 4):
for col in range(1, 4):
ws.cell(row=row, column=col, value=row * col)
# Iterate over all rows in the used range
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
for cell in row:
print(cell.value, end=" ")
print()
# Iterate over columns instead
for col in ws.iter_cols(min_row=1, max_row=3, min_col=1, max_col=3):
print([cell.value for cell in col])You can also access a full row or column directly using the rows and columns properties, but those return generators that include empty cells up to the worksheet dimension. For sparse data, iter_rows with explicit bounds is more efficient.
If you only need values and not Cell objects, pass values_only=True to iter_rows(). This returns tuples of raw values, which is faster and more memory-efficient when you don't need formatting or style information.
Worksheet Management
A workbook can contain multiple worksheets. You can create, rename, and remove them as needed. The active property returns the currently selected sheet, but you can also access sheets by name with wb[name] or by index through wb.worksheets[index].
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.active
ws1.title = "Data"
# Create a new worksheet
ws2 = wb.create_sheet("Summary")
# Access by name
ws = wb["Data"]
# List all sheet names
print(wb.sheetnames)
# Remove a sheet
wb.remove(ws2)When you create a new workbook, it automatically contains one worksheet. If you need to start with a blank slate, you can remove the default sheet and add your own, but it's usually easier to rename the default sheet.
Be careful when removing sheets: wb.remove(ws) expects a worksheet object, not a name. If you have the name, use wb.remove(wb[name]).
Inserting and Deleting Rows and Columns
openpyxl supports inserting and deleting rows and columns at arbitrary positions. This is useful when you need to restructure a spreadsheet programmatically, for example to add a header row or remove a column that is no longer needed.
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.append(["A", "B", "C"])
ws.append([1, 2, 3])
ws.append([4, 5, 6])
# Insert a new row at the top (row 1)
ws.insert_rows(1)
ws["A1"] = "Header"
# Insert a column at position 2
ws.insert_cols(2)
ws["B1"] = "New Column"
# Delete the second row
ws.delete_rows(2)
# Delete the first column
ws.delete_cols(1)
wb.save("restructured.xlsx")Inserting a row shifts existing rows down; inserting a column shifts columns to the right. The insert_rows and insert_cols methods accept an idx parameter (1-based) and an optional amount to insert multiple rows or columns at once. Similarly, delete_rows and delete_cols remove rows or columns and shift the remaining content.
One important side effect: formulas that reference moved cells are not automatically updated by openpyxl. If your workbook contains formulas, you may need to recalculate them after structural changes, either manually or by re-evaluating the workbook in Excel.
Merging and Unmerging Cells
Merging cells is a common way to create headers that span multiple columns or rows. openpyxl provides merge_cells() and unmerge_cells() methods that take a range string like "A1:C1".
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Merge a range of cells
ws.merge_cells("A1:C1")
ws["A1"] = "Merged Header"
# Unmerge the same range
ws.unmerge_cells("A1:C1")After merging, only the top-left cell retains its value; the other cells become MergedCell placeholders with a value of None, and they are read-only. When you unmerge, the previously merged cells revert to individual cells, but the values that were in the non-top-left cells are lost. If you need to preserve data, copy it before merging.
Merging can also cause issues with iteration. When you use iter_rows(), the non-top-left positions yield MergedCell objects whose value is None, which can break data processing. Consider whether merging is truly necessary or if you can achieve the visual effect with formatting alone.
Performance Considerations for Large Workbooks
openpyxl loads an entire workbook into memory by default. For large files (tens of thousands of rows), this can consume significant RAM and slow down operations. When you only need to write data, you can use the write_only mode, which streams rows to disk instead of keeping them in memory.
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet("Large Data")
# Append rows one at a time
for i in range(100000):
ws.append([i, i * 2, i * 3])
wb.save("large.xlsx")In write_only mode, you cannot read or modify cells after writing; you can only append rows. This is ideal for generating large export files where the data is produced sequentially.
For reading large workbooks, consider using read_only mode in load_workbook. This loads the workbook in a streaming fashion, allowing you to iterate over rows without holding the entire file in memory. However, read_only mode has limitations: you cannot modify the workbook, and some features like merged cells are not fully supported.
from openpyxl import load_workbook
wb = load_workbook("large.xlsx", read_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
# process each row
pass
wb.close() # release the file handleAlways close a read_only workbook when you are done to free system resources. In both modes, the trade-off is between memory usage and flexibility. For interactive or small-file scenarios, the default mode is simpler and more capable.
When performance matters, avoid repeatedly accessing cells by coordinate in a loop. Instead, use iter_rows() or iter_cols() to process ranges in bulk. This reduces the overhead of individual cell lookups, especially when dealing with thousands of cells.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.