In SharePoint, Calculated column is another special type of column (metadata). it will calculate values based on other columns for the same row in the same SharePoint list or library. Calculated Column uses Excel-like syntax to calculate the new values.You can create Calculated Columns on any list or library or at the site level(and then add it to the list or library).
Columns that can be referenced in Calculated Column Formula-
- Single line of text
- Date & Time
Calculated Column Return Types:
- Single line of text (String)
- Number ( you can set decimal place and also display number as percentage like 50%)
- Currency (You can select currency format & set number of decimal place)
- Date and Time ( You can set return type as Date only or Date with time only)
- Yes/No (Boolean return type)
Calculated Column Limitations:
- Lookup fields, Multiple line of text field & Person field are not supported.
- ID of newly inserted row can’t be used as the ID doesn’t exist when the formula is processed.
- Calculated Columns formula can’t reference [TODAY] and [ME] functions but can be used as columns referenced by a calculated column.
- Calculated Columns that have spaces, need to be enclosed in brackets. Example: [Due Date]
- Calculated Columns can only reference columns for that specific row, can not interact with another row data.
- Calculated columns formulas are compatible to Excel formulas.
I will cover “how you will create Calculated column in SharePoint List/Library?” in the next article.