Python openpyxl Data Validation Examples Guide

Data validation ensures accurate Excel data entry. It restricts input to specific values. Python openpyxl automates this process.

This guide covers practical data validation examples. You will learn to create dropdown lists and restrict numbers. You will also validate dates and use custom formulas.

Getting Started with openpyxl

First, ensure openpyxl is installed. Use pip for installation. Check our Install Openpyxl in Python Step by Step guide if needed.

Import the necessary modules. You need Workbook and DataValidation classes. These handle Excel file creation and validation rules.

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

Creating a Basic Dropdown List

Dropdown lists limit choices to predefined options. They prevent data entry errors. Use the DataValidation class for this.

Set the validation type to "list". Define the source values. Apply the validation to specific cells.

 
# Create workbook and select active sheet
wb = Workbook()
ws = wb.active

# Create data validation for dropdown
dv = DataValidation(type="list", formula1='"Option A,Option B,Option C"')

# Add validation to worksheet
ws.add_data_validation(dv)

# Apply to cells A1 to A10
dv.add('A1:A10')

# Save the workbook
wb.save("dropdown_validation.xlsx")

This code creates a dropdown in cells A1 through A10. Users can select from three options. Invalid entries are rejected automatically.

Validating Number Ranges

Number range validation restricts numeric input. It ensures values fall between minimum and maximum limits. This is useful for ages or quantities.

Set type to "whole" or "decimal". Define minimum and maximum values. The error message appears for invalid entries.

 
# Create number range validation
dv = DataValidation(
    type="whole",
    operator="between",
    formula1="1",
    formula2="100",
    showErrorMessage=True,
    errorTitle="Invalid Input",
    error="Please enter a number between 1 and 100."
)

ws.add_data_validation(dv)
dv.add('B1:B10')

wb.save("number_validation.xlsx")

This validates cells B1 to B10. Only whole numbers from 1 to 100 are accepted. Users see a custom error message for invalid inputs.

Date Validation Examples

Date validation ensures proper date entries. It can restrict dates to specific ranges. This prevents future or past date errors.

Set type to "date". Use operator "between" for ranges. Specify start and end dates in formula parameters.

 
# Create date validation
dv = DataValidation(
    type="date",
    operator="between",
    formula1="2024-01-01",
    formula2="2024-12-31",
    showErrorMessage=True,
    error="Date must be in 2024."
)

ws.add_data_validation(dv)
dv.add('C1:C10')

wb.save("date_validation.xlsx")

This restricts dates in column C to 2024. Any date outside this range triggers an error. Excel automatically checks date format validity.

Custom Formula Validation

Custom formulas provide flexible validation rules. They can check complex conditions. Use Excel formulas directly in validation.

Set type to "custom". Put your formula in formula1. The formula must return TRUE for valid entries.

 
# Custom formula validation
dv = DataValidation(
    type="custom",
    formula1="=AND(LEN(D1)>=5, LEN(D1)<=10)",
    showErrorMessage=True,
    error="Text must be 5-10 characters long."
)

ws.add_data_validation(dv)
dv.add('D1:D10')

wb.save("custom_validation.xlsx")

This validates text length in column D. Entries must be between 5 and 10 characters. Custom formulas offer unlimited validation possibilities.

Input and Error Messages

Data validation can show helpful messages. Input messages appear when selecting cells. Error messages show for invalid entries.

Set showInputMessage and showErrorMessage to True. Customize the titles and content. This improves user experience significantly.

 
# Validation with custom messages
dv = DataValidation(
    type="list",
    formula1='"Yes,No"',
    showInputMessage=True,
    inputTitle="Selection",
    inputMessage="Please select Yes or No from the dropdown.",
    showErrorMessage=True,
    errorTitle="Invalid Selection",
    error="You must choose either Yes or No."
)

ws.add_data_validation(dv)
dv.add('E1:E10')

wb.save("messages_validation.xlsx")

This provides clear guidance to users. The input message helps with proper selection. The error message explains correction requirements.

Combining Multiple Validations

Worksheets often need multiple validation rules. You can apply different validations to different ranges. Each validation object handles one rule type.

Create multiple DataValidation objects. Add each to the worksheet. Apply them to different cell ranges as needed.

 
# Multiple validations on one sheet
dropdown_dv = DataValidation(type="list", formula1='"High,Medium,Low"')
number_dv = DataValidation(type="whole", operator="between", formula1="1", formula2="10")

ws.add_data_validation(dropdown_dv)
ws.add_data_validation(number_dv)

dropdown_dv.add('F1:F10')
number_dv.add('G1:G10')

wb.save("multiple_validations.xlsx")

This applies dropdown validation to column F. Number validation applies to column G. You can add as many validations as needed.

Working with Existing Workbooks

You often need to add validation to existing files. Load the workbook instead of creating new. Then apply validation to specific sheets.

Use load_workbook() to open existing files. Select the target worksheet. Add validation rules as shown previously.

 
from openpyxl import load_workbook

# Load existing workbook
wb = load_workbook('existing_file.xlsx')
ws = wb.active

# Add validation to existing sheet
dv = DataValidation(type="list", formula1='"Active,Inactive"')
ws.add_data_validation(dv)
dv.add('H1:H20')

wb.save('updated_file.xlsx')

This modifies an existing Excel file. It adds a status dropdown to column H. Learn more in our Openpyxl Load Workbook Iterate Sheets Guide.

Common Validation Types and Parameters

openpyxl supports various validation types. Each type has specific parameters. Understanding these helps create effective rules.

Common types include "list", "whole", "decimal", "date", "time", and "textLength". The operator parameter defines comparison logic like "between" or "notBetween".

Always test your validations thoroughly. Open the generated Excel file. Verify that the rules work as expected.

Best Practices and Tips

Use descriptive error messages. They help users correct entries. Vague messages cause confusion.

Combine data validation with other openpyxl features. Add Style Cells and Fonts in Excel with Python openpyxl for better visuals. Use Python openpyxl Conditional Formatting Guide for dynamic formatting.

Test validations with both valid and invalid data. Ensure error messages appear correctly. Check that valid entries are accepted without issues.

Conclusion

Python openpyxl data validation automates Excel rule creation. It ensures data quality and consistency. The examples covered dropdowns, numbers, dates, and formulas.

You can combine multiple validation types. Custom messages improve user experience. Apply these techniques to both new and existing workbooks.

Data validation is one of many openpyxl capabilities. Explore our Python Openpyxl Tutorial: Read Write Excel XLSX Files for more Excel automation techniques.