How to Configure and Use Validation Settings in SharePoint

Introduction

Validation in SharePoint ensures that data entered by users meets specific rules before it is saved in a list or library. It can prevent errors, enforce consistency, and make reporting more accurate.

1. Column Validation

Purpose:

Ensures data entered in a single column is correct according to a rule.
Validation is triggered when a user tries to save a list item.

Steps to Configure Column Validation:

Open the SharePoint list.
Go to Settings (⚙) → List settings.
Scroll to the Columns section and click the column you want to validate.
Scroll down to Column Validation.
Enter:
- Formula – defines the validation rule.
- User Message – message shown when validation fails.
Click OK.

Example 1: Employee Age Validation

Scenario: You have a column “Age” in a list of employees. You want to ensure only people between 18 and 65 can be entered.

Formula:

=AND([Age]>=18,[Age]<=65)

User Message:
"Age must be between 18 and 65."

✅ If a user enters 17, SharePoint will block it and display the message.

Example 2: Employee ID Length

Scenario: Your EmployeeID column must have exactly 5 characters.

Formula:

=LEN([EmployeeID])=5

User Message:
"Employee ID must be exactly 5 characters long."

2. List Validation

Purpose:

Validates data across multiple columns in a list.
Useful for enforcing rules like Start Date < End Date or conditional requirements.

Steps to Configure List Validation:

Open the SharePoint list.
Go to Settings (⚙) → List settings.
Under General Settings, click Validation settings.
Enter:
- Formula – rule for validation.
- User Message – message when the rule is violated.
Click OK.

Example 1: Conditional Requirement

Scenario: You have a Status column (choices: Pending, Approved, Rejected) and a Comments column. You want Comments to be required only if Status is Rejected.

Formula:

=IF([Status]="Rejected",LEN([Comments])>0,TRUE)

User Message: "You must provide comments if the status is Rejected."

✅ This is a real-world workflow example often used in approvals.

Example 2: Start Date vs End Date

Scenario: You have a list of projects with columns Start Date and End Date. You want to ensure the End Date is always after Start Date.

Formula:

=[End Date] > [Start Date]

User Message:
"End Date must be later than Start Date."

✅ SharePoint will block any entry where the End Date is before the Start Date.

3. Common Functions in Validation Formulas

Function	Purpose	Example
AND(condition1, condition2)	All conditions must be true	=AND([Age]>=18,[Age]<=65)
OR(condition1, condition2)	At least one condition must be true	=OR([Department]="HR",[Department]="IT")
LEN([Column])	Count characters in a text field	=LEN([EmployeeID])=5
ISNUMBER([Column])	Checks if value is numeric	=ISNUMBER([Salary])
IF(condition, value_if_true, value_if_false)	Conditional logic	=IF([Status]="Rejected",LEN([Comments])>0,TRUE)

4. Best Practices

Keep formulas simple – complex formulas can be hard to troubleshoot.
Clear user messages – explain exactly what the user needs to fix.
Test before use – use sample data to check validation rules.
Use list validation for multi-column rules, column validation for single-column rules.
Remember: Validation works in SharePoint forms, not automatically in Excel imports or via API—additional checks may be needed.

Summary Table: Column vs List Validation

Feature	Column Validation	List Validation
Scope	Single column	Entire list
Formula references	Only that column	Multiple columns
Example	=LEN([EmployeeID])=5	=[End Date]>[Start Date]
User message	Column-specific	List-specific

Conclusion

SharePoint validation ensures data entered in lists is accurate and follows business rules. Column validation checks individual fields, while list validation handles rules across multiple columns. Proper use reduces errors, enforces consistency, and improves data quality.