5 places to use formulas in SharePoint lists and libraries

Remember when you were in school and studied “boring” formulas and probably wondered whether you will ever use them in real life? Well, perhaps you should have smoked less weed back then and listened more to your math teacher, because today, I will explain why formulas are handy in SharePoint. To be precise, I will explain a few ways to use formulas in SharePoint lists and libraries to make them more intelligent and user-friendly.

Option 1: Calculated column

The first and most obvious place where formulas can be handy in SharePoint is the Calculated Column. It is a special type of column that essentially allows you to do a math calculation or data extraction based on a mathematical formula.

For example, you want to calculate an Expiration Date, which is 30 days after the last modified date. I have written a detailed post on this type of column, you can access it here. This post also includes some references for math formulas you can use as well.

Formula:

[Modified]+30

 

Limitations

• Calculated Columns formula can’t reference [TODAY] and [ME] functions
• Calculated Columns that have spaces, need to be enclosed in brackets when used in formulas. Example: [Expiration Date]
• Calculated Columns can only reference columns for that specific row, can’t reference data in other rows

Option 2: Column Validation

The other place you can use formulas in SharePoint lists and libraries is Column Validation. Column Validation allows you to validate whether or not the data entered into a specific field (column) matches the desired range. For example, when the user enters an Expiration date into the column field, it has to be in the future (greater than TODAY). To learn more about Column Validation, check out this post.

Formula

=[Renewal Date] > TODAY()

Limitations

• Can’t reference other columns for this type of validation
• Makes synchronized library read-only
• Only works for new entries
• For a complete list of limitations and their explanation, please reference this article.

Option 3: List/Library Validation

Unlike the Column Validation above, List/Library validation allows you to compare a column to other columns in a list or library. For example, a user enters a Renewal Date for a policy that got to be a date prior to the Expiration Date (another column in a library). To learn more about List/Library Validation, check out this post.

Formula

=[Renewal Date]<[Expiration Date]

Limitations

• Only works for new entries in a list/library
• When a new document is uploaded into the library that has a validation formula set, it checks out a document. The user needs to fill in the field in order for Validation formula to pass (TRUE result) and then manually check in the document so it will be visible to everyone else
• For a complete list of limitations and their explanation, please reference this article.

Option 4: Hide Column based on a conditional formula

A relatively new place to utilize formulas in SharePoint is the ability to hide columns on lists and libraries using a conditional formula. This allows you to make metadata more dynamic for the user, showing or hiding certain fields based on the choices the users make. For example, if the user marks a Policy Document as Inactive from the Status column, the expiration date for that document will be hidden. I explained step-by-step instructions on how to set this up in this article.

Formula

=if([$Status] ==’Active’, ‘true’, ‘false’)

Limitations

• There are quite a few nuances and limitations related to this feature, so I suggest you reference this post to learn about them all.

Option 5: Calculated Default Value on a Column

The last place where you can use formulas in SharePoint is the calculated default value on a column. When you create a new column, you can set some sort of default value for it. It could be a manually typed in value or a value based on the calculation. For example, when a document is uploaded to the library, the default Expiration Date will be calculated as 30 days after today’s date. Obviously, the user can adjust the date as necessary, but the default value will be set based on the formula.

Formula

=TODAY()+30

Limitations

• Can’t reference other columns in a list or library