Add Various Data Validation Rules to Excel Using Python

When working with data analysis and report development, inconsistent entry standards and messy formatting in Excel files are common pain points. Manually configuring data validation rules for Excel cells—such as restricting entry formats, value ranges, and text lengths—is not only time-consuming but also prone to omissions and inconsistent rule settings across spreadsheets.

With Python automation, we can batch-configure standardized data validation rules for Excel cells to guarantee accurate, uniform data entry from the source. This tutorial leverages the Spire.XLS for Python library to walk you through implementing five widely used Excel data validation types: integer range, date range, text length, dropdown list, and time range. It runs independently without local Office software, delivering cross-platform performance with a lightweight footprint.

Tool Overview & Environment Setup

Key Advantages of Spire.XLS for Python

Spire.XLS for Python is a professional Python component built for Excel manipulation, featuring the following core strengths:

• Operates standalone, no installation of Microsoft Excel or WPS required;
• Full support for Excel workflows: workbook creation, editing, formatting, data validation, formula calculation, and file conversion;
• Clean, intuitive syntax with a gentle learning curve, ideal for automated reporting, data validation, and batch processing workflows;
• Broad compatibility with mainstream Excel versions (2016 / 2019 / 365) and robust file format support.

Library Installation

Run the following pip command in your terminal to install the dependency with one click:

pip install spire.xls

Full Implementation Code

The script below implements all five core data validation rules (integer range, date range, text length, dropdown list, time range) in one go. It also optimizes cell styles and column widths to output a standardized Excel template with built-in validation logic.

from spire.xls import *
from spire.xls.common import *

# Initialize a workbook
workbook = Workbook()

# Access the first worksheet
sheet = workbook.Worksheets[0]

# Add labels to identify each validation type
sheet.Range["B2"].Text = "Number Validation:"
sheet.Range["B4"].Text = "Date Validation:"
sheet.Range["B6"].Text = "Text Length Validation:"
sheet.Range["B8"].Text = "List Validation:"
sheet.Range["B10"].Text = "Time Validation:"

# 1. Integer range validation (1 to 10)
rangeNumber = sheet.Range["C2"]
rangeNumber.DataValidation.AllowType = CellDataType.Integer
rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between
rangeNumber.DataValidation.Formula1 = "1"
rangeNumber.DataValidation.Formula2 = "10"
rangeNumber.DataValidation.InputMessage = "Enter a number between 1 and 10"
rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent

# 2. Date range validation (all dates in 2022)
rangeDate = sheet.Range["C4"]
rangeDate.DataValidation.AllowType = CellDataType.Date
rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between
rangeDate.DataValidation.Formula1 = "01/01/2022"
rangeDate.DataValidation.Formula2 = "31/12/2022"
rangeDate.DataValidation.InputMessage = "Enter a date between 01/01/2022 and 31/12/2022"
rangeDate.Style.KnownColor = ExcelColors.Gray25Percent

# 3. Text length validation (maximum 5 characters)
rangeTextLength = sheet.Range["C6"]
rangeTextLength.DataValidation.AllowType = CellDataType.TextLength
rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual
rangeTextLength.DataValidation.Formula1 = "5"
rangeTextLength.DataValidation.InputMessage = "Enter text with no more than 5 characters"
rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent

# 4. Dropdown list validation with predefined options
rangeList = sheet.Range["C8"]
rangeList.DataValidation.Values = ["United States", "Canada", "United Kingdom", "Germany"]
rangeList.DataValidation.IsSuppressDropDownArrow = False
rangeList.DataValidation.InputMessage = "Select an option from the dropdown list"
rangeList.Style.KnownColor = ExcelColors.Gray25Percent

# 5. Time range validation (9:00 to 12:00)
rangeTime = sheet.Range["C10"]
rangeTime.DataValidation.AllowType = CellDataType.Time
rangeTime.DataValidation.CompareOperator = ValidationComparisonOperator.Between
rangeTime.DataValidation.Formula1 = "9:00"
rangeTime.DataValidation.Formula2 = "12:00"
rangeTime.DataValidation.InputMessage = "Enter a time between 9:00 and 12:00"
rangeTime.Style.KnownColor = ExcelColors.Gray25Percent

# Auto-adjust width for Column B
sheet.AutoFitColumn(2)
# Set fixed width for Column C to fully display prompt messages
sheet.Columns[2].ColumnWidth = 20

# Export the finished Excel file
workbook.SaveToFile("output/DataValidation.xlsx", ExcelVersion.Version2016)

Line-by-Line Breakdown of Core Logic

Basic Initialization

We create a blank workbook instance via Workbook() and retrieve the default worksheet. Descriptive labels are added to Column B to structure the spreadsheet, helping users quickly identify which validation rule applies to each cell.

Detailed Explanation of the Five Data Validation Rules

1. Integer Range Validation

Cell C2 only accepts integers from 1 to 10. CellDataType.Integer specifies the validation category, while the Between operator sets the numeric bounds. Custom input prompts guide standardized data entry, and a light gray cell fill visually marks cells with active validation rules.

2. Date Range Validation

Cell C4 restricts entries to any date within the calendar year 2022. This rule is widely used for reporting dates, registration timestamps, and statistical cycles to block invalid date inputs.

3. Text Length Validation

Cell C6 caps input text at 5 characters maximum. This works well for short codes, abbreviations, and serial numbers, preventing layout distortion and data analysis errors caused by overly long text entries.

4. Dropdown List Validation

Cell C8 features a dropdown menu with pre-defined country options, with the dropdown arrow visible to users. Entries are limited exclusively to preset selections, eliminating typos and inconsistent formatting—this is one of the most common tools for standardized data collection.

5. Time Range Validation

Cell C10 only accepts times between 09:00 and 12:00. It fits use cases such as attendance tracking, meeting scheduling, and business hour logging to standardize time formats and valid time windows.

Spreadsheet Formatting Optimizations

AutoFitColumn dynamically resizes columns to fit text content, while a fixed ColumnWidth ensures all instructional prompts remain fully visible. These formatting tweaks improve readability and align with standard corporate reporting aesthetics.

Program Output Overview

After executing the script, a file named DataValidation.xlsx will be generated inside the project’s output folder. Opening the file reveals the following features:

1. Cells C2, C4, C6, C8 and C10 are shaded light gray to indicate active validation constraints;
2. Hovering over each cell displays the pre-configured entry guidance message;
3. Excel automatically rejects and flags invalid inputs (e.g., entering 11 in C2 or a 6-character string in C6);
4. Cell C8 includes a clickable dropdown arrow for one-click selection of predefined values, eliminating manual typing.

Extended Business Use Cases

The five validation rules covered here accommodate most office workflows and can be expanded to match custom business requirements:

• Financial Statements : Numeric range checks for monetary values and date filters to exclude anomalous data;
• HR Forms : Dropdown menus for departments and job roles, plus character limits for employee IDs;
• Attendance Tracking : Time window validation for clock-in/clock-out times and date validation for attendance logs;
• Data Submission Templates : Enforce uniform entry standards and cut down manual review workloads.

Conclusion

Automating Excel data validation with Spire.XLS for Python eliminates the major drawbacks of manual rule configuration: low efficiency, inconsistent standards, and human error. The five core validation rules demonstrated above feature simple setup logic and excellent reusability across projects.

Compared to traditional libraries such as openpyxl and xlwt, Spire.XLS delivers more comprehensive support for data validation and complex cell formatting. It runs independently without Excel dependencies, making it ideal for enterprise-level automated reporting and batch data auditing workflows that drastically streamline office and data processing tasks.