Python openpyxl: Merge Cells, Filters, and Data Validation
Use openpyxl to merge cell ranges, apply auto filters, and add data validation in Excel. A practical guide with code examples and tips for avoiding merged-cell and validation issues.

openpyxl can combine merged cell ranges, auto filters, and data validation in a single worksheet. This article shows the key openpyxl objects and the order in which to apply them while building a simple sales report.
Setting Up an openpyxl Workbook
Create a workbook and select the active worksheet:
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"Workbook() creates a new workbook, and wb.active returns the default worksheet. All merge, filter, and validation operations in this article are applied to that worksheet object.
Merging Cells with openpyxl
Use ws.merge_cells() to combine a range of cells. It accepts a range string such as "A1:E1" or keyword arguments for the start and end rows and columns. After a merge, openpyxl exposes the range through its top-left cell, so assign the value and alignment there.
ws.merge_cells("A1:E1")
ws["A1"] = "Quarterly Sales Summary"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")Alignment(horizontal="center", vertical="center") centers the title over the merged range. Use ws.unmerge_cells() with the same range to undo the merge.
Merged cells do not automatically change row heights, column widths, print areas, or page breaks, so inspect the layout after merging.
Applying AutoFilters to a Worksheet
An auto filter lets users filter rows by column values. Set ws.auto_filter.ref to the range containing the column headers and the data rows. If headers are in row 2 and data runs through row 20, use "A2:E20".
ws.auto_filter.ref = "A2:E20"This adds filter dropdowns to each column in the range. The filter does not hide rows until the user selects a filter; for a basic interactive filter, setting the reference is enough.
If a worksheet has a merged title row, keep the filter range on the row with actual column headers. In the example below, row 1 is merged, so the filter range starts at row 2.
Adding Data Validation Rules
Data validation controls what users can enter into a cell or range. Create a DataValidation object, add it to the worksheet, then apply it to a range.
A dropdown list is a common validation rule. If column E stores a status with allowed values "Open", "In Progress", and "Closed", use:
dv = DataValidation(type="list", formula1='"Open,In Progress,Closed"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("E2:E20")For a list rule, formula1 must be a comma-separated list enclosed in double quotes. allow_blank=True permits empty cells.
Numeric ranges use type="whole" with an operator. For integers between 1 and 100:
dv_num = DataValidation(type="whole", operator="between", formula1="1", formula2="100")
ws.add_data_validation(dv_num)
dv_num.add("D2:D20")openpyxl also supports validation types such as "date" and "decimal". The rule is stored in the .xlsx file, but openpyxl does not validate values when you set them; Excel enforces the rule when a user edits the sheet.
Combining Merged Cells, Filters, and Validation
Order matters: merge the title row first, set the filter range on the header row, then add validation to data columns. The following script builds a complete worksheet.
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"
# Merge title row
ws.merge_cells("A1:E1")
ws["A1"] = "Quarterly Sales Summary"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
# Headers
headers = ["Region", "Product", "Units", "Price", "Status"]
for col, header in enumerate(headers, start=1):
ws.cell(row=2, column=col, value=header)
# Sample data
data = [
["North", "Widget", 10, 25, "Open"],
["South", "Gadget", 5, 40, "In Progress"],
["East", "Widget", 8, 25, "Closed"],
]
for row_idx, row in enumerate(data, start=3):
for col_idx, value in enumerate(row, start=1):
ws.cell(row=row_idx, column=col_idx, value=value)
# Filter range includes header and data
ws.auto_filter.ref = "A2:E5"
# Data validation for Status column
dv = DataValidation(type="list", formula1='"Open,In Progress,Closed"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("E3:E5")
wb.save("sales_report.xlsx")The filter range A2:E5 covers the header row and the three data rows. The validation applies only to status cells in those rows. If you add rows later, update both the filter reference and the validation range.
Common Pitfalls and Compatibility Considerations
Keep merged cells outside the filter range. If you merge cells within a filtered range, Excel may not apply filters as expected because merged cells hide the underlying row values. Put merged titles above the header row.
Data validation on merged cells also requires care. Because merged cells are represented by their top-left cell, validation may apply only to that cell in some spreadsheet applications. Apply validation to the top-left cell if you must validate a merged block, and test the file in your target application.
Large sheets can be affected by the ranges you choose. Adding validation to thousands of cells is generally fine, but a very large auto filter range can slow initial loading. Use the smallest range that covers your data.
Finally, openpyxl writes .xlsx files according to the OOXML specification, but not every spreadsheet application supports every validation type or filter setting. Test generated files, especially when validation rules rely on custom formulas.
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.