How to Create a Calculated Column in SharePoint?

Calculated columns in SharePoint allow you to generate values automatically based on other columns in your list. As a SharePoint developer, I have created calculated columns in SharePoint lists and libraries many times when we want to display column values based on formulas.

The formulas for SharePoint calculated columns are based on Microsoft Excel functions and their syntax.

In this tutorial, I’ll show you how to create a calculated column in a SharePoint list with different types of examples.

What is a Calculated Column in SharePoint?

A calculated column performs operations on other columns in your SharePoint list using formulas and functions, similar to Excel. These calculations update automatically whenever the source data changes.

We can create a calculated column in either a SharePoint list or library, allowing you to display calculated values automatically based on the formulas.

You can implement the following types of formulas in a SharePoint calculated column.

• Conditional formulas
• Date & time formulas
• Mathematical formulas
• Text formulas

You can reference columns with the following data types in your formulas:

• Single line of text
• Number
• Currency
• Date and time
• Choice
• Yes/No
• Other calculated columns

Here are also some limitations you should be aware of regarding the calculated columns in SharePoint.

• Calculated columns only update when items are saved or modified
• They cannot reference data from other lists directly
• Some complex formulas may have character limitations
• Person fields cannot be directly referenced in certain contexts

To show an example, I will take a SharePoint list named “Task Tracker” which has the following fields:

• Task Name – Single line of text
• Task Status – Choice
• Start Date – Date & time

Now, we can create a calculated column, i.e., Due Date, based on Start Date. Ex: Start Date + 7 days = Due Date. When a new item is created with a start date, the due date will be auto-populated, and its value will be the addition of 7 days to that item’s start date.

Ex: Start Date + 7 days = Due Date

Check out Add Leading Zeros to SharePoint List Column using Calculated Column

Create a Calculated Column in SharePoint List

Let’s start with a basic example of a calculated column in a SharePoint list.

Suppose you have a SharePoint list with a couple of columns like Employee Name, Designation & Joining Date as shown below:

Now, if you want to show the Probation period valid date for each employee (Where the probation period will be for 90 days from the joining date).

This is where the calculated column comes into play!

Let me show you how to add a calculated column to a SharePoint list:

1. Open your SharePoint list or library, where you want to add a calculated column. Click on + Add column -> Scroll down to select See all column types & click on Next button.

2. It will navigate to the Create Column page. Provide a column name, select the data type as Calculated (calculation based on other columns).

3. Next, scroll down to the Formula box. Here, I want to calculate the probation period’s valid date based on the Joining date [Joining date +90 days].

=[Joining Date]+90

You can select the column from the Insert column box and click on Add to formula. Then, click the OK button. Specify the formula and choose the data type returned from this formula: Date and Time.

4. Then, the calculated column is created by displaying values automatically. You can have a reference at the screenshot below:

Check out SharePoint Calculated Column Date Examples

SharePoint Calculated Column Examples

I always prefer to understand concepts using real-world examples. Now, let me show you some real-world examples of how you can use a SharePoint calculated column.

Example 1: SharePoint Calculated Column If Condition [Conditional Formula]

Suppose, let’s take a SharePoint list named ‘Budget List‘ with a few columns.

I wanted to create a calculated column where, if Actual Amount >Allocated Amount, display the budget review as “Not sufficient“.

By this time, you already know how to create a calculated column in a SharePoint list/library. Follow the steps mentioned above.

1. On the Create column window, enter the column name, select the type of column as
   Calculated (calculation based on other columns).

2. Next, scroll down the page to the Additional Column Settings section, and provide the formula in the Formula box. Select the data type returned from this formula is: Single line of text.

Formula: =IF([Actual Amount]>[Allocated Amount],"Not Sufficient","Sufficient")

3. Now, click the OK button at the bottom of the page.
4. That’s it, the calculated column has been created in the SharePoint list. Have a look at the reference image:

Example 2: SharePoint Calculated Column: Add Months to Date [Date and time formulas]

Let’s take the same SharePoint list used in the above example, but with different columns and data types as shown in the table below:

Let me create a calculated column that adds months to the date column. For example, Allocated Date + Duration in Months = Budget Expiration Date [calculated column].

1. To create a calculated column, enter the column name and select the type of information in this column: Calculated (calculation based on other columns).

2. Provide the below formula in the Formula dialogue box. Replace your SharePoint column names. Choose the return data type as Date and Time.

=(DATE(YEAR([Allocated Date]),MONTH([Allocated Date])+[Duration in Months],DAY([Allocated Date])))

3. Once you click the Ok button, the calculated column will be created. Refer to the image below:

Example 3: Calculate the Profit% of Two Numbers in SharePoint using Calculate Column [Mathematical formulas]

Below, I have taken a table that shows column names and data types for an Event Budget list.

I want to calculate the net profit percentage between [Total Revenue & Total Expenses].

1. In the SharePoint Create column page, enter a column name and select the data type as Calculated(calculation based on other columns).

2. In the Formula box, paste the formula given in the code below.

=([Total Revenue]-[Total Expenses])/ABS([Total Expenses])

Replace the SharePoint list columns with yours.

3. Now, you can see the SharePoint calculated column has been created.

Example 4: Combine Two Columns by SharePoint Calculated Column [Text formulas]

In the last example, I will show how to combine two SharePoint columns using a calculated column in SharePoint.

For this, I will take a SharePoint list of [Employee Details] with columns like Employee First Name and Employee Last Name.

I want to combine the employee’s first name and last name to give the employee a full name.

Example: Employee First Name + Employee Last Name => Employee Full Name.

1. While creating a column, provide a name for the column and select the type as Calculated(calculation based on other columns).

2. Insert the formula provided in the code below into the Formula box and choose the data type returned from this formula as: Single line of text.

3. Once you click on OK, the calculated column will be created.

Output:

Example 5: Multiply Two Fields using a SharePoint Calculated Column

Suppose you have a SharePoint list with the following columns as shown below. Here, I wanted to multiply the two columns (Quantity, Unit Price) to calculate the Total Amount (Calculated column).

Here are the steps to multiply columns in a SharePoint calculated column:

1. Open your SharePoint list/library where you want to add a calculated column that will multiply two columns.
2. Click on the + Add column, and select See all column types. Then, click on the Next button as shown below.

3. Then, it will redirect to the “Create column” page, where you can add columns to the SharePoint list. Provide column name, select type as Calculated(calculation based on other columns).

4. On the Additional Column Settings section, enter the formula below in the Formula box. After that, I have selected the data type returned from this formula to “Currency“. You can choose according to your requirement.
5. Then, scroll down the page and click the Save button at the bottom of the page.

=Quantity*[Unit Price]

Replace your column names in the formula.

6. Now, the calculated column has been created. You can see the newly created column on your SharePoint list. Have a look at the reference image below.

This way, you can multiply two fields in SharePoint calculated columns.

Conclusion

In this tutorial, we covered what a calculated column is in SharePoint, how to create one, and explored several practical examples—including using conditional formulas, date calculations, mathematical expressions, and text concatenation.

I hope you find this tutorial useful.

You may also like the following tutorials:

• Create a Calculated Column Using LEN Function in SharePoint Online
• SharePoint List Calculated Column Concatenate() Function

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.