Creating Calculated Columns in a SharePoint List Guide
SharePoint Online
Oct 10, 2023 10:30 AM

Creating Calculated Columns in a SharePoint List Guide

by HubSite 365 about Pragmatic Works
External YouTube Channel
Pro User

SharePoint OnlineLearning Selection

Maximize SharePoint functionality with Calculated Columns! Create custom columns based on existing values for streamlined data management.

The content covered in the video from Pragmatic Works centers on the utility of calculated columns in SharePoint lists, elaborating on how one can establish custom columns based on existing ones. The presenter's preferred feature in SharePoint is Metadata, on which he has written several articles discussing the various metadata columns that can be created (choice, term store, text, etc.) except for calculated columns. He takes this opportunity to talk about calculated columns and elucidates their creation process.

 

A calculated column is a specific column type that you can generate on a list or a library. This kind of column is instrumental because it allows the value of the field to depend on an alternative value/field for the corresponding row in the same list. The author provides an instance: if you've already designed an Expiration Date column (date field) on the document’s library, you can create a calculated column named Reminder Date that is 30 days before the Expiration Date. This will compute a new date based on the formula subtracting 30 days from the Expiration Date.

The operation of a calculated column is fairly straightforward; it uses an Excel-style syntax to determine the new values. Simple calculation formulas can be utilized, such as the expiration date example, as well as more complicated ones.

You can create calculated columns on any list or library or at the Site Level, before incorporating them into a list or library. For practical demonstration, the author guides you through the process of creating a calculated column in a SharePoint list or library (locally), excluding the Site Level.

SharePoint List

When you get to the document library or SharePoint List, you'll be tasked with clicking +Add Column > More. The metric for formulating a calculated column is setting the formula (calculation). The author then runs you through how to achieve this by establishing a simple calculation of the Expiration Date depending on the Last Modified Date. Along with this, you are required to select the suitable format for the Output (Calculated) column based on what you're computing. With that done, your calculated column is created. Although calculated columns bring innovative solutions to SharePoint lists and libraries, they come with their share of limitations. One challenge is with formulas referencing [TODAY] and [ME] functions, they can't function inside calculated columns.

Additionally, calculated columns that have spaces need to be enclosed in brackets when implemented in formulas. Lastly, calculated columns can only recur to columns for that particular row, devoid of the capability to reference data in other rows. Regardless, calculated columns play a pivotal role in SharePoint lists or library, especially for more advanced users who understand Excel formulas and syntax.

 

Read the full article How to Create Calculated Columns on a SharePoint List

Create Calculated Columns 

Looking to enhance your SharePoint list by generating custom columns from those that are previously existing? The answer is Calculated Columns—a potent feature in SharePoint. This tool enables you to set up elementary custom columns that rely on SharePoint List Settings. Below, you'll find a detailed guide on implementing this feature.

Metadata exists as a standout feature in SharePoint. It allows users to create different types of metadata columns (including choice, term store, and text). However, this guide will focus on the Calculated Column—a unique kind of column for SharePoint lists or libraries—and how to implement it.

What exactly is a Calculated Column?

It's a distinctive sort of column you can establish on a list or library, which takes its value from another value of the same row in the same list or library. For instance, if you've established an Expiration Date column on the library of documents, you can generate a Calculated Column titled Reminder Date to calculate a new date based on the Expiration Date minus 30 days. Thus, it provides a handy tool for staying ahead of deadlines.

Functionality-wise,  Excel-like formulas to determine new values. These could be simple calculations, like the one mentioned above, or more intricate ones. More formula examples can be found in the Additional Reading section towards the bottom of this guide.

 

Columns can be created on any list or library or at the Site Level. Moreover, they can then be added to the list or library. This tutorial will elaborate on how to create a local Calculated Column in a SharePoint list or library and not at the Site Level

To create a Column in a SharePoint List or library, first navigate to the document library or SharePoint List. You then click the '+ Add Column' button followed by 'More' to access the backend using the classic editor, as Calculated Column is not yet modernized. Next, give your column a name and select the Calculated radio button, which indicates that the calculation is based on other columns.

After this, specify the formula for the calculation. In this instance, you'll be creating a simple calculation for the Expiration Date based on the Last Modified Date, adding 30 days to the latter. Existing fields can be used to enter the formula, after which the formatted Output for the Calculated column is selected. In this example, the format being calculated is a date field; thus, it's a Date Type. Finally, click 'OK' to finish creating your calculated column

 

More links on about How to Create Calculated Columns on a SharePoint List

Calculate data in lists or libraries
Make a calculated column available to other lists or libraries · Open the SharePoint site. · In the navigation pane on the left, select Site contents. · On the ...
 

Keywords

SharePoint list, Create Calculated Columns, SharePoint Calculated Columns, SharePoint Column Creation, SharePoint list customization, SharePoint tools, SharePoint list columns, SharePoint configuration, Calculated Columns SharePoint, SharePoint management.