How to Create Column and List Validation in SharePoint

Last Updated on August 26, 2023

The ability for your users to add metadata to the documents and files in a list or library is truly beneficial.

With the use of other features like lookup columns and search filters, you’ll be able to find things a lot faster.

Unfortunately, there’s only so much you can do to ensure that they enter the correct information.

On the bright side, you can at least minimize the errors by setting up validation on the data with Excel-like formulas.

In this article, let’s discuss column and list validation in SharePoint and what you have to do to set them up.

Let’s start!

What is column validation in SharePoint?

In SharePoint, there are two types of validation that you can set up in a list or library.

They are the following:

1. Column Validation
2. List (or Library) Validation

The first one primarily deals with data entered in a given column. It ensures that whatever was entered in that column follows the rule that the admin has created.

For example, let’s say you have a list where users can specify a reminder date for a certain file or document.

To reduce any error in setting up the date, you may want to create a rule to validate the date entered, which must not be today.

Naturally, to do that, you will have to convert the argument into an Excel formula.

How to create column validation in SharePoint?

Creating a column validation is easier than adding more sophisticated ones like calculated columns. It’s just like adding a plain old new column with a few changes.

For this example, let’s refer to our example earlier and create a new column where users can add a reminder date for each file.

On the list or library, click +Add column > Date and time. 

Make sure to name the column first and specify its column type. Since we’ll be working on a date column, we’ll leave the type to its default.

After that, click on More options.

Then, click on Column validation.

You will then see two forms where you can write the formula and the user message.

Since we want to validate the date and disqualify past dates, we can use the following:

=[Reminder Date]>TODAY()

As for the user message, we’ll use:

“The reminder date must be set in the future.”

Once you’re done with it, click on the Save button to finalize the new column.

Now, if a user tries to enter a date that’s earlier than the current date, he or she will not be able to save the changes and he will receive a user message.

How does list validation differ from column validation?

List or library validation is quite similar to column validation. However, the difference is that list validation considers other columns in the list or library.

If you want to set up a validation rule that depends on the metadata found on another column within the list or library, then you must use list validation instead of column validation.

For example, let’s say that you want to make the reminder date at least five days earlier than the due date.

To do that, you need to utilize the dates found in the due date column.

How do you create a list validation rule in SharePoint?

To add a list validation rule, you don’t need to add a new column.

For example, deriving from our example earlier in the column validation, we already have a reminder date column (which doesn’t have a column validation rule in this example).

To start, click on the gear icon at the upper-right corner of the screen and select Library (or List) settings from the menu.

Then, click on Validation settings.

Similar to creating column validation, you will find here two forms — one for the formula and another for the user message.

Since we want to accept dates that are at least five days earlier than the date in the due date, we can use the formula:

=[Reminder Date]<([Due Date]+5)

To add a column to the formula form, all you need to do is click on a column name in the Insert Column box and click the Add to formula link at the bottom of the box.

As for the user message, we’ll use:

“The Reminder Date must at least be five days earlier than the Due Date.”

Don’t forget to hit Save after you’re done.

Now, whenever one of your users enters a reminder date that’s not at least five days before the due date, they will get a user message and the entered information will not be accepted.

Limitations of Column and List Validation

Although column and list validation are powerful methods of reducing user error in entering metadata, there are some limitations that unfortunately have no fix.

One of them is that both column and list validation only works for new entries.

If you already have some files or documents in your list or library before you create the validation rules, all of them (the previous entries) will not be checked or validated by the rule.

Another thing is that when using column validation on a library that syncs to your computer, the rule will make the library read-only.

In other words, you can still edit the contents of your library in the browser.

However, you can’t make any changes to the synchronized library. You will know when you can’t edit your sync library when you see a lock on the folder icon.

As for list validation, remember that the formula that you have set will check out each new document uploaded to the list or library.

The user who uploaded the file needs to fill in the field for the file or document to pass the validation rule.

Then, that user will need to manually check the document so the uploaded file or document will be visible to others.

That’s it! I hope this article will be able to help you lessen the clean-up work on your site list or library.

If you’ve got any other questions regarding SharePoint and other Microsoft 365 products, feel free to leave a comment below or send me a message on my contact page.