SharePoint Online: Add Calculated Column to List using PowerShell

Requirement: Add Calculated Column to SharePoint Online List

How to Add Calculated Column to SharePoint Online List?

Calculated columns in SharePoint Online is used to manipulate other columns in the list item. Instead of entered by a user, these field values are automatically calculated based on the formula. Calculated fields will not appear on the New Form or Edit forms but visible on View Forms and in SharePoint views. To add a calculated column to the SharePoint Online list,

  • Browse to your SharePoint Online site and navigate to the target list in which you want to create a calculated column.
  • Under the List tab, click on the “Create Column” button in the ribbon.
  • Provide the Name to your new column, specify the type as “Calculated (calculation based on other columns)”
    Add calculated Column to SharePoint Online List using PowerShell
  • Enter the Formula for your calculated column in the “Formula” field and specify the return type of the data. 
  • Scroll down and set other parameters such as decimal places and format of the data and click on “OK” to create a calculated field in the SharePoint Online list.

PowerShell to Create Calculated Column to List in SharePoint Online:

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

#Custom function to add column to list
Function Add-CalculatedColumnToList()
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description=[string]::Empty,
        [Parameter(Mandatory=$true)] [string] $FieldsReferenced,
        [Parameter(Mandatory=$true)] [string] $Formula,
        [Parameter(Mandatory=$true)] [string] $ResultType

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)

        #Check if the column exists in list already
        $Fields = $List.Fields
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
            Write-host "Column $Name already exists in the List!" -f Yellow
            #Frame FieldRef Field
            $FieldRefs = $FieldsReferenced.Split(",")
            foreach ($Ref in $FieldRefs)
                $FieldRefXML = $FieldRefXML + "<FieldRef Name='$Ref' />"

            #Create Column in the list
            $FieldSchema = "<Field Type='Calculated' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' ResultType='$ResultType' ReadOnly='TRUE'><Formula>$Formula</Formula><FieldRefs>$FieldRefXML</FieldRefs></Field>"
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message

#Set parameter values
$DisplayName="Total Investment Buffer"
$Description="5 Percentage in Total Investment"
$Formula = "=([Total Investment]*5)/100"
$FieldsReferenced="[Total Investment]" #Fields Names participating in Formula. Should be Comma (,) Separated

#Call the function to add column to list
Add-CalculatedColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Formula $Formula -ResultType $ResultType -FieldsReferenced $FieldsReferenced

PnP PowerShell to Add a Calculated Column to List in SharePoint Online

$SiteURL = ""
$ListName= "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Add a calculated column to list
Add-PnPField -List $ListName -Type Calculated -InternalName "Buffer" -DisplayName "Buffer" -Formula "=[Budget]*0.10"

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply