In this article, I will list
The Supported and Unsupported Fields in SharePoint Calculated Column Formula
The Calculated Column In SharePoint
The Supported/allowed fields in SharePoint Calculated Column Formula and List Validation Settings.
By default, the below columns can be used in SharePoint Calculated Column Formula:
Also, the columns with the below data type are supported in SharePoint Calculated Column Formula:
- A single line of text.
- Choice (menu to choose from).
- Number (1, 1.0, 100).
- Currency ($, ¥, €).
- Date and Time.
- Yes/No (checkbox).
- Calculated (calculation based on other columns).
- Task Outcome.
- External Data.
- Content-Type Columns.
Note: The List Calculated Column scope is the columns in the same row of the same list, you can’t use other columns from other lists / sites.
The Unsupported fields in SharePoint Calculated Column Formula and List Validation Settings.
Although the SharePoint calculated column formula will accept the ID field without any error, however, it will not be calculated on item added or changed!
Note: The ID value will only be calculated when the Calculated field formula is updated again in the calculated column field settings.
Check this workaround: Using ID Field in SharePoint Calculated Column Formula.
As shown below, the ID Column is not listed in the available columns, it’s not supported!
The [Today] is not supported in the Calculated Column. however, to get the current date you can use:
- TODAY(), NOW() as a function that will be returned as DateTime.
- [Modified] field.
Note: You should be aware of the Today() value will not be preserved the first saved [Today] value! it will be recalculated again to get the current date for each Edit Item.
The [ME] is not supported in the Calculated Column.
Multiple lines of text
Whatever it’s “Plain text” or “Enhanced rich text”, the “Multiple lines of text” field is not supported in the Calculated Column.
Choice field with Checkboxes (allow multiple selections)
Only choice field with Drop-Down Menu & Radio Buttons are allowed in the SharePoint Calculated field.
The Choice field with Checkboxes (allow multiple selections) is not allowed in the SharePoint Calculated field.
Lookup (information already on this site)
Whatever it’s a “Single value” or “Multiple values“, the “Lookup” field is not supported in the Calculated Column.
Check this workaround: Using Lookup Field in SharePoint Calculated Column Formula.
Person or Group
Whatever it’s a “People Only” or “People and Groups“, the “Person Or Group” field is not supported in the Calculated Column
Hyperlink or Picture
The “Hyperlink or Picture” field is not supported in the Calculated Column.
The “Managed Metadata” field is not supported in the Calculated Column.
For SharePoint Online, Adding HTML tags in SharePoint calculated column has been blocked on June 13, 2017. By the way, Administrators can request an extension through September 10, 2017, at the latest. During this extension, execution of custom markup in calculated fields will not be blocked. This request can be submitted through Microsoft Support. However, beginning September 10, 2017, all unsupported markup will be ignored.
For SharePoint 2016/2013, The June 2017 PU and subsequent PUs will include a new web application setting that’s called
This setting lets an on-premises administrator determine whether execution of custom markup in calculated fields in a given web application is blocked.
The following is the default behavior for existing and new web applications at the time of the update installation.
- Newly created web app after the update is installed: Block execution of custom HTML markup in calculated fields.
- Existing web app after the update is installed: Allow execution of custom HTML markup in calculated fields.
The following are some useful links to JSLink as a supported workaround:
- Client-side rendering (JS Link) code samples
- JSLink Client Side Rendering (CSR) Samples
- Client Side Rendering
- Custom field type in the SharePoint add-in model
Also, For SharePoint 2013 / 2016 On-Prem, you can enable
CustomMarkupInCalculatedFieldDisabled via PowerShell as the following
$web = Get-SPWebApplication http://WebAppURL $web.CustomMarkupInCalculatedFieldDisabled = $false $web.Update()
Keep in mind
The calculated column formula is only calculated/updated in the following cases:
- Add New Item.
- Update Existing item.
- Update the calculated column itself in the List Setting!
- SharePoint Online.
- SharePoint 2016.
- SharePoint 2013.
- SharePoint 2010.
In this article, I have listed The allowed and unallowed Fields in SharePoint Calculated Column Formula.
- Using ID Field in SharePoint Calculated Column Formula.
- Using Lookup Field in SharePoint Calculated Column Formula.