Python openpyxl: Merge Cells, Filters, and Data Validation

Python openpyxl merge cells filters and data validation are common requirements when building Excel reports programmatically. This article shows how to combine these features in a single worksheet.

Setting Up an openpyxl Workbook

Start by creating a workbook and selecting the active worksheet. openpyxl uses Workbook() and then active to get the default sheet. For this example, we'll build a sales report with a title row, column headers, and data rows.

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Alignment

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

The worksheet is the object that holds cells, merges, filters, and validation rules. All the operations we'll discuss apply to this ws object.

Merging Cells with openpyxl

Use ws.merge_cells() to combine a range of cells into one. The method takes a string like "A1:E1" or a tuple of start and end coordinates. After merging, only the top-left cell holds a value; the others become None.

ws.merge_cells("A1:E1")
ws["A1"] = "Quarterly Sales Summary"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

The Alignment object centers the text across the merged range. Without it, the text stays left-aligned in the top-left cell.

To unmerge, call ws.unmerge_cells() with the same range. Merged cells affect row heights and column widths, so check the layout after merging. Also note that merging cells does not automatically adjust the print area or page breaks.

Applying AutoFilters to a Worksheet

AutoFilters let users filter rows by column values. Set ws.auto_filter.ref to the range that includes the header and data rows. For example, if headers are in row 2 and data runs from row 2 to row 20, use "A2:E20".

ws.auto_filter.ref = "A2:E20"

This adds filter dropdowns to each column in the range. The filter itself does not hide rows until the user interacts with the dropdown, but you can also set filter criteria programmatically by adding FilterColumn and CustomFilter objects. For most use cases, simply setting the reference is enough to enable filtering in Excel.

If you merge cells in the header row, the filter range should start at the first row that contains actual column headers, not the merged title row. In our example, row 1 is merged, so the filter range starts at row 2.

Adding Data Validation Rules

Data validation restricts what users can enter into a cell or range. openpyxl provides the DataValidation class. You define a rule, add it to the worksheet, and then apply it to a range.

A common example is a dropdown list. Suppose column E contains a status field with allowed values "Open", "In Progress", and "Closed".

dv = DataValidation(type="list", formula1='"Open,In Progress,Closed"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("E2:E20")

The formula1 string must be a comma-separated list enclosed in double quotes. For numeric ranges, use type="whole" and set operator and formula1/formula2. For example, to allow only 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")

You can also use type="date" with formula1 as a date string, or type="decimal" for floating-point values. The allow_blank parameter controls whether empty cells are considered valid.

After adding validation, Excel enforces it when the user edits the sheet. openpyxl does not validate data itself; it only writes the rule into the file.

Combining Merged Cells, Filters, and Validation

These features work together, but order matters. Merge the title row first, then set the filter range on the header row, and finally add validation to data columns. Here's a complete example:

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):
    cell = 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 three data rows. The validation applies only to the status cells in those rows. If you later add more rows, update both the filter reference and the validation range.

Common Pitfalls and Compatibility Considerations

Merged cells and filters can interact in unexpected ways. If you merge cells within the filter range, Excel may not apply filters correctly because merged cells hide the underlying column values. Keep merged cells outside the filter range, typically above the header row.

Data validation on merged cells is also tricky. Excel only validates the top-left cell of a merged range; the other cells are ignored. If you need validation for a merged block, apply it to the top-left cell and rely on the merge to cover the visual area.

Performance matters when you have large sheets. Adding validation to thousands of cells is fine, but setting a filter range over a huge area can slow down Excel's initial load. Use the smallest range that covers your data.

Finally, openpyxl writes the .xlsx file according to the OOXML specification. Some Excel features, like certain validation types or filter criteria, may not be fully supported by all spreadsheet applications. Test the generated file in your target application, especially if you use custom formulas in validation rules.

The combination of merged cells, auto filters, and data validation covers most interactive reporting needs. Keep the ranges consistent and avoid overlapping features to produce a reliable workbook.