SharePoint Calculated Column in a List or Library [Tutorial]

SharePoint Calculated Column in a List or Library [Tutorial]

If you’re a long-time user of SharePoint, then you must have stumbled upon calculated columns in a list or document library.

You must have seen it on some site and you’re probably wondering how to implement it to your as well.

The cool thing here is that the steps you need to take are quite the same, whether you’re using the classic or modern experience of SharePoint.

In this article, I’ll show you what a calculated column in SharePoint can do and how you can implement it yourself in only a few steps.

Let’s get started!

What do you need to know about calculated columns?

Basically, a calculated column is a column with values that are based on another value or field for the same row in the same list or library. It’s the only type of row that will allow you to use another value or field, do calculations on it, and display it as a value within the column.

The way it works is simple — it uses a syntax similar to Excel to calculate new values.

For example, let’s say you have a certain column in your list that represents when a document is due.

With a calculated column, you can create a column that will use that data, the due date, as a value and perform calculations on it. Let’s say you want to create a calculated column that will serve as a reminder to you that a document is due on a specific date.

Specifically, you can calculate a new date based on the due date minus five days, the answer of which will be the content of the calculated column.

Sign up for exclusive updates, tips, and strategies

    How to create a calculated column in SharePoint?

    To create a calculated column, navigate first to a list or library in a certain site that you manage or own.

    Note that the calculated column only works on the site level.

    Once you’re on the list or library, click on +Add Column > More.

    Add Column > More on SharePoint Document Library

    This will bring you to the classic editor in SharePoint. Unfortunately, this feature hasn’t been modernized yet from the classic experience.

    The next step is to give the column a name and choose Calculated as the type of column.

    Create Calculated Column Name

    What to do next is to specify the calculation that has to take place. To make this simple, let’s use the Due Date column and subtract 30 days from it.

    To make this happen, you must click on the column on the right box and click on Add to formula beneath it. Then, enter the rest of the formula.

    Insert Column to Calculated Column Formula

    The next part is to choose the correct format for the calculated column. Since the example is dealing with dates, we need to choose the Date and Time option and specify the format.

    Correct Format for the Calculated Column

    Once you’re done, simply hit OK and see the results:

    Calculated Column Results

    Common Formulas in Lists

    Here’s a good reminder from Microsoft when it comes to calculated columns:

    Calculated fields can only operate on their own row, so you can’t reference a value in another row, or columns contained in another list or library. Lookup fields are not supported in a formula, and the ID of newly inserted row can’t be used as the ID doesn’t exist when the formula is processed.

    In addition, here are the specific types of formulas that you can use:

    1. Conditional formulas
    2. Date and time formulas
    3. Mathematical formulas
    4. Text formulas

    To check the full list of functions, visit Microsoft’s support page on the examples of common formulas in lists.

    If you need any help in regards to SharePoint columns and pages, classic or modern, feel free to leave a comment below or send me a direct message via the contact page.

    About Ryan Clark

    As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage Modern Workplace and Digital Process Automation investments. I am also a Microsoft Most Valued Professional (MVP) for Office Apps & Services.

    Subscribe
    Notify of
    guest
    0 Comments
    Inline Feedbacks
    View all comments
    0
    Would love your thoughts, please comment.x
    ()
    x
    Scroll to Top
    Tweet
    Share
    Share
    Pin