How to create a Calculated Column in a SharePoint list or library
Metadata is, by far, my favorite feature in SharePoint. In the past, I wrote numerous posts on different types of metadata columns you can create (choice, term store, text, etc.) One column I have not written about yet is the Calculated Column. So let me fix that injustice and explain to you what it is and how to create a Calculated Column in a SharePoint list or library.
What is a Calculated column?
Calculated Column is a special type of column you can create on a list or library that will allow for the value of the field to be based on another value/field for the same row in the same list or library. For example, If you have created an Expiration Date column (date field) on the library of documents, you can create a Calculated column called Reminder Date that is 30 days prior to the Expiration Date that will calculate a new date based on Expiration Date minus 30 days formula.
How Does Calculated column work
Calculated Column uses Excel-like syntax to calculate the new values. You can use simple calculation formulas along the lines of the expiration date example I mentioned above as well as more sophisticated calculations. Reference Additional Reading section at the bottom of this post for the examples of the formulas.
Where you can create a Calculated Column
You can create Calculated Columns on any list or library or at the Site Level (and then add it to the list or library).
How to create a Calculated Column in a SharePoint list or library
- For this example, I will explain how to create a Calculated Column in a SharePoint list or library (locally), not Site Level. Navigate to a document library or SharePoint List, then click on + Add Column > More (the reason we need to click More is that Calculated column has not been “modernized” from classic experience and we need to create it in the backend using the classic editor as we did for many years in SharePoint)
- Give your column a name and choose Calculated (calculation based on other columns) radio button.
- Next, we need to specify the formula (calculation). For this use case, I will use a simple calculation of the Expiration Date based on the Last Modified Date. Expiration Date will be equal to Modified Date + 30 days. You can either type in the formula manually, or you some of the fields (existing columns you can use). In my case, I click on Modified, then Add to formula (which adds the field to the left pane). Then I just type in the remainder of formula “+30“
- Next, at the bottom of the page, we need to choose the appropriate format for the Output (Calculated) column. In my case, we are calculating a date field, so it is a Date Type. If you were to calculate a dollar value based on some other columns you had, then you would choose Currency. Once the appropriate column type is chosen, click OK.
- And your calculated column is created!
Calculated Column Limitations
Below is a brief list of major limitations of Calculated Columns. For a thorough list of all, please reference this excellent article from Mike Smith.
- Calculated Columns formula can’t reference [TODAY] and [ME] functions. You will receive an error message: “Calculated columns cannot contain volatile functions like Today and Me.”
- Calculated Columns that have spaces, need to be enclosed in brackets when used in formulas. Example: [Expiration Date]
- Calculated Columns can only reference columns for that specific row, can’t reference data in other rows
Additional Reading
Though my Jewish Mom always tells me that I am smart, I am by no means an Excel expert, so while I introduced you to the concept of the Calculated Column in a SharePoint list or library, I am not as smart as she thinks, especially when it comes to Excel formulas/syntax. So below, I would like to provide a few resources that I found helpful when building formulas on Calculated Columns in SharePoint.
- A really handy post with examples from Microsoft
- Mike Smith’s Book on SharePoint Calculated Columns and Validation Formulas (book available for purchase on Amazon.com)
Example of a “more advanced” formula in a Calculated Column
Share Article
