Supported and Unsupported Columns In SharePoint Calculated Column Formula

In this article, we’ll list the Supported and Unsupported Fields in SharePoint Calculated Column Formula by exploring the following:

---

Supported and Unsupported Columns In SharePoint Calculated Column Formula

Before we getting started, you should be first aware of the calculated column formula is only calculated or updated in the following cases:

1. Add New Item.
2. Update Existing item.
3. Update the calculated column itself in the List Setting.

Supported Columns in SharePoint Calculated Column Formula

In this section, we’ll list the allowed and supported columns that you can use in SharePoint Calculated Column Formula and SharePoint List Validation Settings.

By default, the below columns can be used in SharePoint Calculated Column Formula:

• Title.
• Created.
• Modified.

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) | Single Selection.
• 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.
you can’t use other columns from other rows in the same list.

---

Unsupported Columns in SharePoint Calculated Column Formula

In this section, we’ll list the unsupported columns that you can’t use in SharePoint Calculated Column Formula as well as SharePoint List Validation Settings.

Use [ID] in SharePoint Calculated Column Formula

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!

As shown below, the ID Column is not listed in the available columns, it’s not supported!

Note: The ID value will be only calculated when the Calculated field formula is updated in the calculated column field settings again.

Check the available workarounds at: 

1. Using ID Field in SharePoint Calculated Column Formula.

2. Auto Serial Number in SharePoint New Form using JSOM.

---

Use [TODAY] in SharePoint Calculated Column Formula

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.
• You can also use the [Modified] field as an alternative to the current date.

Note: You should be aware of the Today() function will not preserve the first saved [Today] value! it will be recalculated with the current date once you update the item in the next time.

---

Use [ME] in SharePoint Calculated Column Formula

The [ME] is not supported in the Calculated Column, The available workaround is creating a workflow that set the value of the current user to a new single line of a text column, then use the value of the new text column in your calculated field formula.

Check a similar idea to create a workflow at Using Lookup Field in SharePoint Calculated Column Formula.

---

Use Multiple lines of textbox column in SharePoint Calculated Column Formula

Regardless, you are using “Plain text” or “Enhanced rich text”, the “Multiple lines of text” column is not supported in the Calculated Column Formula.

---

Use Choice Column in SharePoint Calculated Column Formula

The choice field with single selection is only supported in the SharePoint Calculated column formula. but the Choice field with Checkboxes (allow multiple selections) is not supported in the SharePoint Calculated field.

Use Lookup Column in SharePoint Calculated Column Formula

Regardless, you are using a “Single value” lookup column or “Multiple values” lookup column, the Lookup column is not supported in the Calculated Column.

Check this workaround Using Lookup Field in SharePoint Calculated Column Formula.

---

Use Person or Group Column in SharePoint Calculated Column Formula

Regardless, you are using a “People Only” or “People and Groups“, the “Person Or Group” column is not supported in the Calculated Column

---

Use Hyperlink or Picture Column in SharePoint Calculated Column Formula

The “Hyperlink or Picture” column is not supported in the Calculated Column.

---

Use Managed Metadata Column in SharePoint Calculated Column Formula

The “Managed Metadata” column is not supported in the Calculated Column.

---

Use HTML Markup in SharePoint Calculated Column Formula

HTML Markup in Calculated Column in SharePoint Online

Adding HTML tags in the 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, the 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.

HTML Markup in Calculated Column in SharePoint 2016 / 2013

The June 2017 PU and subsequent PUs will include a new web application setting that’s called CustomMarkupInCalculatedFieldDisabled. This setting lets an on-premises administrator determine whether execution of custom markup in calculated fields in a given web application is blocked.

Also, you can enable CustomMarkupInCalculatedFieldDisabled via PowerShell as the following:

$web = Get-SPWebApplication http://WebAppURL
$web.CustomMarkupInCalculatedFieldDisabled = $false
$web.Update()

Below 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.

Workaround to use HTML Markup in SharePoint Calculated Column

As a supported workaround you can use JSLink! the below are useful resources to get started:

• 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

Read more at Handling HTML markup in SharePoint calculated fields.

---

Applied To

• SharePoint Online.
• SharePoint 2016.
• SharePoint 2013.
• SharePoint 2010.

Conclusion

In this article, we have listed the supported and unsupported columns in SharePoint Calculated Column Formula.

You might also like to read

• Using ID Field in SharePoint Calculated Column Formula.
• Using Lookup Field in SharePoint Calculated Column Formula.

Have a Question?

If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.