Skip to main content

Column Validation

Column validation stops bad data before it lands in your list. You write a simple formula that must evaluate to true for the item to save, plus a friendly message explaining what went wrong. It works at two levels: a single column can validate its own value, and a list-level rule can compare two columns, such as ensuring an end date is not before a start date. Because the check runs the moment someone saves, errors are caught at the source rather than discovered later.
Related Features
Calculated Column, Column Formatting, Default Column Value, Indexed Column

Common Use Cases

  • Required formats: forcing a code or ID to match an exact pattern
  • Value ranges: keeping a number between a sensible minimum and maximum
  • Date logic: ensuring an end date is on or after a start date
  • Non-negative numbers: blocking values below zero where they make no sense
  • Cross-field rules: comparing two columns before allowing a save
  • Length checks: requiring a minimum or exact number of characters

Benefits

  • Cleaner data: errors are blocked at entry rather than fixed later
  • Clear guidance: a custom message tells users exactly how to fix it
  • No code: formulas use the familiar calculated-column syntax
  • Two levels: validate a single column or compare several
  • Immediate: the check runs at save, not in a nightly job
  • Trustworthy lists: reports built on the list can be relied upon

How It Works

  • A formula that returns true: the item saves only when the formula passes
  • Column-level rule: validates the value of its own column
  • List-level rule: references multiple columns in one expression
  • User message: the text shown when validation fails
  • Familiar functions: the same functions used by calculated columns
  • Save-time enforcement: the rule fires every time an item is created or edited

Limits and Nuances

  • No cross-item checks: a rule cannot look at other items in the list
  • No lookups: validation cannot query another list for values
  • Limited column types: multi-line text and a few types are not supported
  • Locale-sensitive: date and number formats follow the site locale
  • Save-only: it runs at save, not while the user is typing
  • One message per rule: a single message covers all failure reasons

Common Questions About Column Validation

What is column validation in SharePoint?

Column validation is a formula that must evaluate to true before an item can be saved, paired with a message that explains any failure. It enforces data quality at the point of entry, so a code matches its required pattern, a number stays in range, or a date falls after another date. The check runs at save time, catching mistakes immediately rather than letting them spread.

How is column validation different from list validation?

Column validation checks a single column against its own value, while list validation can reference several columns in one formula. Use a column rule for something like a non-negative number, and a list rule when you need to compare fields, such as ensuring an end date is on or after a start date. Both use the same formula syntax and both run when the item is saved.

Do I need to write code for validation?

No code is required, but you do write a formula. The syntax matches calculated columns, with familiar functions for text, numbers, and dates. A simple rule can be as short as a single comparison, and SharePoint shows your custom message whenever the formula returns false, so users get clear guidance instead of a generic error.

Which column types support validation?

Common types such as single line of text, number, date and time, currency, and choice support validation. Multiple lines of text and a handful of specialized types do not. The validation section only appears in the settings for columns that support it, which is the quickest way to confirm whether a given column can be validated.

When does the validation rule run?

The rule runs at save, when an item is created or edited. It does not check while the user is still typing, and it does not re-scan existing items already in the list. This means validation prevents new bad data going forward, but it will not retroactively flag rows that were entered before the rule existed.

Is column validation worth setting up?

For any list people rely on, it usually is. Greg Zelfond, the consultant behind LookBook 365, treats validation as cheap insurance: a few minutes spent on a rule prevents hours of cleanup and keeps every report built on the list honest. The trick is a clear message, so users understand the fix rather than feeling blocked by an unexplained error.