Calculated Columns In SharePoint | An Overview

2 comments

In this blog, we’re going to discuss calculated columns in SharePoint. It’s a great feature that you can add to your SharePoint lists. You don’t need to do manual calculations because it can do that for you.

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 this blog, 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.

calculated columns in sharepoint

Next, choose Single line of text.

calculated columns in sharepoint

It will then display the Create a column panel.

calculated columns in sharepoint

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

calculated columns in sharepoint

The default value will be nothing.

calculated columns in sharepoint

Then, click Save.

calculated columns in sharepoint

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

calculated columns in sharepoint

Then we’ll click Edit in grid view.

calculated columns in sharepoint

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

calculated columns in sharepoint

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

calculated columns in sharepoint

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

calculated columns in sharepoint

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

calculated columns in sharepoint

Finally, click Exit grid view to save.

calculated columns in sharepoint

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.

calculated columns in sharepoint

Then, select Column settings.

calculated columns in sharepoint

And click Edit.

calculated columns in sharepoint

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

calculated columns in sharepoint

From there, click the Type drop-down menu.

calculated columns in sharepoint

Then, choose Date and time.

calculated columns in sharepoint

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

calculated columns in sharepoint

Finally, click Save.

calculated columns in sharepoint

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

calculated columns in sharepoint

Adding Calculated Columns In SharePoint

First, click the Add column.

calculated columns in sharepoint

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 name for this column. In this example, we’ll use “Reward Period End”. After the customers have signed up, they’ll have 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, 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 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 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 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.

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.

Conclusion

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

***** Related Links *****
SharePoint List Item: Editing Single And Multiple Items
SharePoint Lists: An Overview
Adding Metadata In SharePoint Using Columns

***** Related Course Modules *****
SharePoint Introduction For Power BI Users
Power Apps Masterclass
SharePoint Advanced Concepts: Lists, Permissions, Social

***** Related Forum Post Links *****
Extract Values From A SharePoint List And Combine Them Into One Column
Sharepoint List Does Not Show Column Names Value

Get Correct Titles From Sharepoint
For more Sharepoint columns queries to review see here….

2 comments on “Calculated Columns In SharePoint | An Overview”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.