Column Validation
Common Use Cases
- Format enforcement: a ZIP code of exactly five digits, or a code that has to match a fixed length.
- Sensible number ranges: an age between 18 and 100, a percentage between 0 and 100, a rating out of five.
- No negatives or zero: quantities, amounts and hours that only make sense above zero.
- Future or past dates only: a renewal date that must be ahead of today, or a completion date that cannot be.
- Basic email sanity: a value that at least contains an @ before it reaches a mailing list.
- Length rules: a minimum number of characters so a description or justification cannot be one word.
Benefits
- Stops bad data at the door: the rule blocks the save rather than leaving a cleanup job for later.
- No code required: everything is typed into the column settings in the browser.
- Custom user message: your own wording explains what to fix, instead of a generic error.
- Server-side enforcement: the rule holds in the form, in grid view, in a sync client and on import.
- Instant feedback: the user finds out at save time, while the item is still in front of them.
- Cheap insurance: a few minutes on a rule prevents hours of reconciling a report that does not add up.
How It Works
- Boolean result: the formula must return TRUE for the item to save and FALSE to block it.
- Its own column only: the formula can reference the column it is attached to and nothing else.
- Excel-style syntax: the same function library as calculated columns – LEN, AND, OR, ISNUMBER, VALUE, FIND.
- Runs at save: the check fires on create and on edit, never while the user is typing.
- Message on failure: the user message you supply is shown directly under the offending field.
- Existing items untouched: rows created before the rule existed stay as they are until someone edits them.
Settings
- Where it lives: the Column validation section at the bottom of the column settings page.
- Formula box: the field that holds the TRUE/FALSE expression.
- User message box: the text shown when the formula returns FALSE – write it as an instruction, not an error.
- Supported column types: single line of text, number, currency, date and time, and choice.
- Site column scope: a rule set on a site column travels to every list that uses that column.
- Locale sensitivity: dates and separators are interpreted using the site regional settings.
Limits and Nuances
- One column, one rule: a column validation formula cannot look at any other field in the item.
- No other items: it cannot check for duplicates or compare against another row in the list.
- No [Today] or [Me]: the placeholders are not supported, although the TODAY() function is accepted in date comparisons.
- Not every column type: multiple lines of text, person, lookup and managed metadata columns have no validation section.
- One message per rule: you cannot vary the wording by which part of the formula failed.
- Not retroactive: turning on a rule does not flag or fix the items already in the list.
Column Validation Formula Examples
• Restrict a Date to the Past or the Future: =[Renewal Date]>TODAY()
• 10-Digit Phone Number: =LEN([Phone])=10
• Number Within a Range: =AND([Age]>=18,[Age]<=100)
• Limit a Number to a Minimum or Maximum: =[Amount]>0
• 5-Digit ZIP Code: =AND(LEN([Zip])=5,ISNUMBER(VALUE([Zip])))
• Email Must Contain @: =ISNUMBER(FIND("@",[Email]))Common Questions About Column Validation
What is column validation in SharePoint?
Column validation is a rule you attach to a single column: a formula that must evaluate to TRUE before the item can save, paired with a message that appears when it returns FALSE. It keeps data clean at the point of entry – codes that match a pattern, numbers inside a sensible range, dates in the right direction – and it is configured entirely in the browser with no code.
How is column validation different from list validation?
Scope is the only real difference. A column validation formula can reference the column it sits on and nothing else, so it handles format and range rules. A list validation formula sits in the list settings and can reference several columns in the same item, so it handles comparisons such as an end date that must not fall before a start date. Both use the same syntax and both run at save.
Which column types support validation?
Single line of text, number, currency, date and time, and choice columns have a Column validation section. Multiple lines of text, person or group, lookup, managed metadata and calculated columns do not. If the section is missing from the column settings page, the column type does not support it, and the rule has to move to list validation or to a flow instead.
Does validation check the items already in my list?
No. The rule runs when an item is created or edited, so everything already in the list stays exactly as it is until somebody opens and saves it. Turning validation on is a forward-looking control, not a cleanup tool. If the existing rows need fixing, export them, correct them and reimport, or work through the exceptions in grid view.
Why does my validation formula not work with the current date?
The Today and Me placeholders are not supported in validation formulas. The TODAY() function itself is accepted in a date comparison, which is how a future-dates-only rule works, but it is evaluated at the moment of save and never again. A rule can therefore stop somebody entering a past date today, and it will not go back and flag that same item next month.
When should I use validation instead of a flow?
Use validation when the rule concerns the single item being saved and the right answer is to stop the save – a range, a format, a length. Use Power Automate when the rule needs another list, an external system, or an action after the fact such as a notification. Greg Zelfond, the consultant behind LookBook 365, sets these rules while the columns are being designed, because a list that has been collecting bad data for a year is a much more expensive problem than a validation formula.
