What a SharePoint calculated column is
A calculated column shows the result of a formula built from other columns in the same list or library. The formula can add and subtract, compare values, join text, work out dates or test a condition with IF, and the result updates when the columns it uses change. Microsoft's example is a total price: =[quantity] * [item price].
Use one when the answer can be worked out from the same row: a due date 30 days after a start date, a full name from first and last name, a status label from two numbers.
How to create a calculated column
Microsoft documents this route, which works on a list or a document library:
- Open the list or library. Select the gear icon, then List settings (or Library settings).
- Under Columns, select Create column.
- Enter a Column name.
- Set the type to Calculated (calculation based on other columns).
- In the Formula box, type the expression. Use Insert Column to add a column reference.
- Choose the data type returned by the formula.
- Select OK.
The data type matters. A formula that returns a date needs a Date and Time return type, and one that returns text needs Single line of text. A mismatch shows as a #VALUE! error in the list.
Formula syntax
| Rule | What it means |
|---|---|
| Start with = | Every formula begins with an equals sign |
| Functions | Written as the name, an opening parenthesis, the arguments separated by commas, and a closing parenthesis: =IF(...) |
| Column names | Use the display name. A name with spaces or special characters goes in square brackets, such as [Start Date]. Names are not case-sensitive |
| Text | Text constants go in double quotation marks, up to 255 characters |
| Joining text | The & operator joins values: =[First Name]&" "&[Last Name] |
| Dates | Date constants use DATE(year,month,day) |
| Order | Multiplication runs before addition, so =5+2*3 returns 11 and =(5+2)*3 returns 21. Operators of equal precedence run left to right |
| Nesting | A formula can contain up to eight levels of nested functions |
Calculated column examples
These come from Microsoft's own examples. Each result is the one Microsoft gives for the inputs shown.
Conditional formulas
| Formula | Inputs | Result |
|---|---|---|
=IF([Cost]>[Revenue],"Not OK","OK") | Cost is above Revenue | Not OK |
=[Column1]>[Column2] | 15,000 and 9,000 | Yes |
=IF(AND([Column1]>[Column2],[Column1]<[Column3]),"OK","Not OK") | 15, 9 and 8 | Not OK |
=IF(OR([Column1]>[Column2],[Column1]<[Column3]),"OK","Not OK") | 15, 9 and 8 | OK |
=NOT([Column1]+[Column2]=24) | 15 and 9 | No |
A comparison on its own returns Yes or No. Wrap it in IF to return your own words.
Date and time formulas
| Formula | Inputs | Result |
|---|---|---|
=[Start Date]+30 | Any start date | A date 30 days later, such as an expiration date |
=[Date Completed]-[Start Date] | Two dates on a task | The calendar days between them |
=DATEDIF([Column1],[Column2],"d") | 1 January 1995 and 15 June 1999 | 1626 days |
=DATEDIF([Column1],[Column2],"ym") | The same dates | 5 months, ignoring full years |
=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) | 6/9/2007 and 3 | 9/9/2007, three months later |
=TEXT(WEEKDAY([Column1]),"dddd") | 19 February 2007 | Monday |
Set the return type to Date and Time for the formulas that return a date.
Text formulas
| Formula | Inputs | Result |
|---|---|---|
=[Column1]&" "&[Column2] | Carlos and Carvallo | Carlos Carvallo |
=UPPER([Column1]) | nina Vietzen | NINA VIETZEN |
=PROPER([Column1]) | nina vietzen | Nina Vietzen |
=LEFT([Column1],LEN([Column1])-2) | Vitamin A | Vitamin |
=TRIM([Column1]) | Text with spaces at each end | The same text without them |
="Statement date: "&TEXT([Column2],"d-mmm-yyyy") | 5 June 2007 | Statement date: 5-Jun-2007 |
TEXT formats a value as it is displayed, so TEXT([Column2],"0%") on a column holding 40% returns 40%, not 0.4.
Test whether text contains a value
=ISNUMBER(FIND("BD",[Column1])) returns Yes if "BD" appears in Column1 and No if it does not. FIND returns an error value when the text is not found, which is why ISNUMBER wraps it.
What a calculated column cannot do
| Limit | Detail |
|---|---|
| Other rows | A formula uses values in the current row only |
| Other lists | It cannot reference another list or library, or a lookup field |
| The row ID | It cannot reference the ID of a newly inserted row |
| Column types you can reference | Single line of text, number, currency, date and time, choice, yes/no and calculated |
| TODAY and ME | Microsoft lists them as not supported in calculated columns. They are supported when setting a column's default value |
| NOW and RAND | Not supported in lists or libraries |
| Nesting | Up to eight levels of nested functions |
| Scope | A change to a formula applies only to that list or library |
The TODAY restriction catches most people. A calculated column cannot count days overdue against today's date, because it recalculates when the row changes and has no clock of its own. For a value that must move every day, a scheduled Power Automate flow can write a fresh figure into an ordinary column; SharePoint and Power Automate covers building one.
Common errors
- #VALUE! The formula returned a different type from the return type you chose, or an argument had the wrong type. Match the return type to the result, and use
VALUE()to turn text into a number when you need to. - Column name not recognized. Use the column's display name, and put names with spaces or special characters in square brackets.
- Wrong date result. Subtracting two dates gives a number of days, so set the return type to Number. Adding days to a date gives a date, so set it to Date and Time.
- Formula rejected on save. Check commas between arguments, matching parentheses and quotation marks around text.
Frequently asked questions
What is a calculated column in SharePoint?
A column that shows the result of a formula using other columns in the same list or library, such as =[Start Date]+30 for an expiration date.
How do I add a calculated column in SharePoint?
Open the list, select the gear icon, then List settings. Under Columns, select Create column, name it, choose Calculated as the type, enter the formula and the return data type, and select OK.
Can a SharePoint calculated column use TODAY?
Microsoft lists TODAY as not supported in calculated columns. It can be used to set a column's default value. For a value that changes daily, use a flow that updates a normal column.
Can a calculated column reference another list?
No. A formula can use only columns in the same list or library, and only values from the current row.
Which functions can a calculated column use?
The ones Microsoft lists for SharePoint, which include IF, AND, OR, NOT, DATE, DATEDIF, TEXT, LEFT, RIGHT, UPPER, PROPER, TRIM, ROUND and SUM. NOW and RAND are not supported.
Can I use a calculated column in a document library?
Yes. Microsoft's steps apply to a list or a library, and the formula can use the library's other columns.
Why does my calculated column show #VALUE!?
The formula's result does not match the return type you chose, or a function received the wrong type of value. Change the return type or convert the value first.
How many functions can I nest?
Up to eight levels of nested functions in one formula.
Related reading
- How to create a SharePoint list - the list your columns live in.
- SharePoint views - filter and sort on your new column.
- SharePoint document libraries - columns and metadata on files.
- SharePoint and Power Automate - update values a formula cannot.
- Copy a SharePoint list - formulas travel with a list template.
- SharePoint Online limits - list and column limits.
Mark Smith co-founded SmiKar Software in 2015 and has spent the past decade helping organisations solve Microsoft 365 data management challenges. He works with the SmiKar team to build solutions for SharePoint archiving, storage optimisation, governance and compliance, supporting customers from growing businesses through to Fortune 500 enterprises.
More about SmiKar
