Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~3 min readPython
#openpyxl#Excel#spreadsheet#data validation
Diagram showing merged cells, filter arrows, and a validation dropdown in an Excel sheet generated with openpyxl.

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:

python
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.

python
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".

python
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:

python
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:

python
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.

python
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 tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.