Calculated columns in SharePoint or Sharepoint Online are powerful tools that allow users to generate and display data based on the values of other columns within a list or library. Formulas, which consist of equations that perform calculations on these values, serve as the framework of calculated columns and normally begin with an equal sign (=).

Adding a calculated column to a SharePoint list or library allows users to perform a variety of functions, such as calculating sales tax, determining expiration dates, or computing the total cost of items source.

These calculations are useful in a wide range of applications and enable more efficient data management and notifications.

When creating a calculated column, users can choose from numerous predefined functions or create custom formulas using standard mathematical operations, such as addition, subtraction, multiplication, and division. This flexibility makes calculated columns a valuable feature for SharePoint users who need to generate dynamic, data-driven content in their lists and libraries.

We cover some further into about Sharepoint in the below video; it’s definitely worth a watch for all of you power apps enthusiasts!

## Understanding Calculated Columns

Calculated columns in SharePoint are a powerful tool for performing automated calculations based on the data within a SharePoint list or library.

In this section, we will explore the concept and purpose of calculated columns, calculated fields, and calculated values, along with their applications within the SharePoint platform.

Before we jump into an example, let’s get the basics out of the way.

### Concept and Purpose

Calculated columns are essentially formulas that perform calculations using data from other columns within the same list or library item.

Utilizing calculated columns can enhance data consistency by ensuring that calculated values are always up-to-date and in sync with the source data on which they depend. In addition, calculated columns can help reduce manual data entry and potential human error by automating calculations.

### Applications in SharePoint

Calculated columns can be implemented in various scenarios within SharePoint to perform tasks such as:

- Displaying a simple calculation based on values from two or more columns within the same item, like the example of calculating the total by multiplying quantity and price.
- Applying conditional formatting or logic, such as using the IF, AND, and OR functions to calculate and display results based on specific conditions or column values
- Validating data entries by comparing the data in one or more columns and ensuring it meets predefined criteria.
- Using date-related functions to calculate deadlines, due dates, or the difference between two date columns.
- Manipulating text, such as retrieving a portion of a text string or combining values from multiple text columns.

Overall, calculated columns in SharePoint offer extensive functionality to enhance the efficiency and consistency of data management within your lists and libraries.

Now let’s create and add a column.

## How to Easily Create a Sharepoint Calculated Column

This is pretty straightforward, we will finish off the article with a visual example to help you understand better.

### Defining Column Settings

To create a calculated column in a SharePoint list or library, first, open the list or library where you want to add the column. Click on “Settings” near the top right corner of your browser window and then select “Create Column” under the Columns section.

When you define the settings for your new calculated column, start by giving it a name and choosing the “Calculated (calculation based on other columns)” radio button. This will allow you to use data from other columns in the list or library as part of your calculation.

Now it is time to enter a column formula.

### Using Column Formulas

After selecting the “Calculated” column type, you’ll be prompted to enter a formula that calculates the value of your new column based on existing columns in the list or library. SharePoint uses a syntax similar to Excel formulas, allowing you to create complex calculations using functions such as IF, AND, and SUM.

To write a formula, begin by typing an equal sign (=) followed by a combination of functions, column names, and mathematical operators. Be sure to enclose column names in square brackets ([ ]) and use double quotes (” “) for text strings (SharePoint Stack Exchange).

Here’s an example formula:

`=IF([Status]="Completed", [Hours]*[Rate], 0)`

This formula calculates the total cost for a project based on its status, hours, and rate. If the project is marked as “Completed”, the cost is calculated by multiplying the hours and rate; otherwise, the cost is zero.

Once you’ve entered your formula, click “OK” to create the calculated column. It will automatically update its value whenever data in the referenced columns changes.

Voila!

Now let’s run through some common functions.

## Common Functions and Formulas

This section covers some common functions and formulas used in SharePoint calculated columns, divided into two subsections: Arithmetic and Logical Functions, and Text and Date Functions.

### Arithmetic and Logical Functions

Arithmetic functions enable you to execute basic mathematical operations, while logical functions help you make decisions based on certain conditions.

Some commonly used arithmetic and logical functions are:

**ADD:**Adds two numbers. Example:`=Column1+Column2`

**SUBTRACT:**Subtracts a number from another. Example:`=Column1-Column2`

**MULTIPLY:**Multiplies two numbers. Example:`=Column1*Column2`

**DIVIDE:**Divides a number by another. Example:`=Column1/Column2`

**IF:**Performs a conditional test. Example:`=IF(Column1>Column2, "Greater", "Lesser")`

**AND:**Checks if all conditions are met. Example:`=AND(Column1>0,Column2>0)`

**OR:**Checks if any condition is met. Example:`=OR(Column1>0,Column2>0)`

Now, let’s check out some basic text and date functions.

### Text and Date Functions

Text functions allow you to manipulate text strings, while date functions let you handle date and time values. Some common text and date functions in SharePoint calculated columns are:

**CONCATENATE:**Combines two or more strings. Example:`=CONCATENATE(Column1, Column2)`

**LEFT:**Extracts a specified number of characters from the start of a text string. Example:`=LEFT(Column1, 5)`

**RIGHT:**Extracts a specified number of characters from the end of a text string. Example:`=RIGHT(Column1, 5)`

**DATE:**Returns a date based on a day, month, and year. Example:`=DATE(Year(Column1), Month(Column1), Day(Column1)+7)`

**TODAY:**Returns the current date. Example:`=TODAY()`

**DATEDIF:**Calculates the difference between two dates. Example:`=DATEDIF(Column1, Column2, "d")`

These are just a few examples; there are many more functions and formulas available. You can refer to the Microsoft SharePoint Foundation formula guide for more information.

Or, if you are really serious about diving into Sharepoint in Power BI and learning about conditional formulas, document libraries, list settings and more, check out this SharePoint Intro Course; it’s great! It truly is one of the best ways to learn.

## SharePoint Best Practices

Here are a few key things to help you navigate Sharepoint

### Naming Conventions

When working with calculated columns, it’s essential to follow proper naming conventions. This will help maintain a clean, organized, and more accessible SharePoint environment. Some naming conventions to consider for calculated columns include:

- Use descriptive names for columns, indicating the purpose or calculation being performed. For example, instead of using “Col1,” use “TotalSales” for a calculated column that sums up the sales figures.
- Avoid using special characters or spaces in column names, as they may cause compatibility issues with some SharePoint features.
- Consider using camelCase or snake_case for column names, for better readability and consistency with development practices.

### Error Handling

Calculation errors can occur, and they do, believe me! Ensuring proper error handling can result in fewer errors and more reliable performance. Some best practices for error handling in calculated columns include:

- Use the ISERROR function to check for calculations that return errors. This allows you to display an informative message or default value instead of an error. For example:
`=IF(ISERROR([Column1]/[Column2]), "Error Message", [Column1]/[Column2])`

- Make sure to handle division by zero errors, as this is a common calculation error. Use an IF or IFERROR function to avoid a #DIV/0! error when dividing by zero.
- Be mindful of the data types used in your calculations. Mismatched data types can cause calculation errors. Ensure that you use appropriate data types and convert data if necessary before performing calculations.

Following these best practices for naming conventions and error handling will lead to a more efficient, robust, and maintainable SharePoint environment.

Sure, there are a few limitations, they are…

## Limitations and Alternatives

### SharePoint Calculated Column Limitations

Some limitations include:

- Calculated columns are not available for use with multiple lines of text or person or group fields.
- You cannot use lookup fields, managed metadata fields, or external data columns in calculated column formulas.
- Lastly, some functions are not supported, such as networked days and today’s date functions.

Now you have got a feel for everything, let’s run through some examples.

## A Step-by-Step Guide to Calculated Columns In Sharepoint/Sharepoint Online

One of the main advantages of using calculated columns is that the data you need can be set automatically. And it is not limited to simple computation, because it can also do complicated calculations.

For these examples, we’ll focus on making calculated columns with a simple formula. But at the end of the blog, we’ll give you resources on where you can know more formulas that you can use for your calculated columns.

## Adding A Column In A SharePoint List

Before we can proceed, let’s start first by adding another column that will be based on the calculated column. So, click **Add column**.

Next, choose **Single line of text**.

It will then display the **Create a column** panel.

We’re going to type “**Sign Up Date**” on the **Name** field.

The default value will be nothing.

Then, click **Save**.

Next is to refresh the page, and you’ll see the added column on your SharePoint List.

Then we’ll click **Edit in grid view**.

Next, we’ll go to the Excel file from the resources provided in this blog.

From there, we’re going to **copy** all the data in column **H**.

Then, go back to our SharePoint List, and select the top cell on the **Sign Up Date** column.

Next, **paste** the data from the Excel file we copied.

Finally, click **Exit grid view** to save.

## Changing The Column Type To Date And Time In SharePoint

Click the **Sign Up Date** column. Take note that you might need to scroll to the right to see it since we have a lot of columns.

Then, select **Column settings**.

And click **Edit**.

The **Edit column **panel will then appear where you can edit the column.

From there, click the **Type **dropdown menu.

Then, choose **Date and time**.

Next, we’ll set the **Friendly format **to **Yes**.

Finally, click **Save.**

In the prompt that will appear, click **Save**.

This is a very user-friendly format, as you’ll see some data indicating that it was 4 days ago instead of displaying the date format itself.

## Adding Calculated Columns In SharePoint

First, click the **Add column**.

Next, click **More…**

This will take us to the **Settings** then **Create Column**. As you can see, adding a calculated column can only be done in the classical view and not in the modern view.

We’ll type in a full name for this column. In this example, we’ll use “**Reward Period End**”. After the customers have signed up, they’ll have (number of days) 90 days to get discounts or other offers you could think of.

Next, we’ll set the type as **Calculated (calculation based on other columns)**.

For the **Description** in the below screenshot, we’ll not put anything for now.

In the **Formula **text box, we want it to be equal to their sign-up date plus 90 days. Additionally, we want it automated. So on the **Insert Column **options, we’ll choose **Sign Up Date**.

Then, in the **Formula** field, you’ll notice that it has “**[Sign Up Date]**”. So, we’ll add “**+90**” next to it.

Next, below the **Formula **text box, you’ll see options for **The data type returned from this formula is:** We’ll choose **Date and time, **then, **Date Only**.

Finally, click **OK**.

And on the right side of the page of our SharePoint list, we’ll see the **Sign Up Date** (start date) and **Reward Period End** columns. The **Reward Period End **column shows 90 days after their **Sign Up Date**.

## Editing Calculated Columns In SharePoint

Keep in mind that we can no longer edit the **Reward Period End** column directly here in the modern view because we’ve created it on the classical view. Instead, we can click the **gear icon** on the top right.

Next, click **List settings**.

We’ll be redirected to the **Settings** page.

Scroll all the way down, and we’ll see all our different columns under the **Columns **section.

Next, we’ll click **Reward Period End****.**

We’ll be redirected to the page where we can set the column up once again.

In this example, we’ll change the formula to “**=[Sign Up Date]+120**”, instead of “**=[Sign Up Date]+90**”.

Finally, click **OK**.

You’ll be redirected to the previous page.

Click **Customers** to return to our SharePoint online list.

You’ll see that the **Reward Period End** column has changed since we updated it to **120**.

Keep in mind that the only way to edit a calculated field/ calculated column is through this way. It’s because we can only create a calculated column in the classical view settings. But of course, you can change the **Sign Up Date** column.

Checkout the below tipe to become a confident Sharepoint designer!

## Tips For Great Function Resources

The best thing about calculated columns is that you don’t need to stick to basic formulas. In fact, you can use a lot of formulas to make your SharePoint list more functional and convenient to use.

If you’re unfamiliar with the formulas you can use, you can find them here.

On this site, you’ll find plenty of formulas that you can use in your calculated columns.

Also, you’d be able to see functions that you can use on your SharePoint lists.

One example is the **ABS **function.

Aside from this list, you can also rely on Google and Stack Overflow. When you search for formulas on the internet, type in “**for Excel**” instead of “**for SharePoint**” because SharePoint list is very much similar to Excel.

This means even conditional formatting will work on your calculated columns.

******* Related Links *********SharePoint List Item: Editing Single And Multiple Items****SharePoint Lists: An Overview**

Adding Metadata In SharePoint Using Columns

## The Final Word

To sum it all up, we’ve learned how to create calculated columns in SharePoint by following the guide above.

The formula we used might be a basic one, but you’ll improve along the way, especially when you use the given resources where you can get formulas for your calculated column.

Keep in mind that a calculated column is not limited to using dates. You can also use it for certain formulas. For example, if a user types in a certain city or office, the zip code will be automated. This means there are a lot of ways that calculated columns can make your SharePoint list more manageable and convenient.

All the best,

Henry