Skip to main content

Calculated Column

The Calculated column computes its own value from other columns in the same list or library using Excel-style formulas, whether that is a due date built from a start date, a status flag driven by an IF statement, or two text fields combined into one. SharePoint evaluates the formula automatically every time an item is created or edited, so the column stays accurate with no manual entry. It is one of the most capable out-of-the-box metadata tools, turning plain lists into smart trackers with no Power Automate and no custom code.
Related Features
Column Validation, Currency Column, Date and Time Column, Default Column Value, Metadata, Number Column, Single Line of Text Column, Yes/No Column

Common Use Cases

  • Deadline math: calculate a due date, review date, or expiration date by adding days to another date column
  • Status flags: IF formulas that label items Overdue, On Track, or Complete based on dates and numbers users already enter
  • Text assembly: combine first and last names, project codes, or locations into one clean display column
  • Fiscal periods: derive the fiscal year or quarter from a standard date column for grouping and reporting
  • Financial totals: multiply quantity by unit price or apply a tax rate in purchasing and budget trackers
  • Smarter views: group, sort, and filter by the calculated result instead of asking users to tag items manually

Benefits

  • Zero manual effort: values compute themselves on every save, so the data stays consistent without retraining anyone
  • Familiar Excel syntax: anyone comfortable writing Excel formulas can build a calculated column in minutes
  • No automation overhead: everyday calculations need no Power Automate flow and no custom code
  • Cleaner data entry: users fill in the raw fields they know, and the math happens behind the scenes
  • Better views: calculated results power grouping, sorting, and filtering that plain columns cannot
  • Works everywhere: the same column type behaves identically on lists and document libraries

How It Works

  • Excel-style formulas: the syntax mirrors Excel, such as =IF([Status]=Done,Complete,Open), with the same operators and nesting
  • Column references: square brackets reference other columns in the same item, like [Start Date]+30
  • Return types: the result displays as a single line of text, number, currency, date and time, or yes/no
  • Calculates on save: the formula runs when an item is created or edited, and changing the formula recalculates every existing item
  • Hidden from forms: the column never appears on new or edit forms, because users cannot type into it

Limits and Nuances

  • No volatile functions: [Today] and [Me] are not supported in calculated column formulas; they do work in column default values and view filters
  • Current item only: a formula reads columns in the same item; it cannot reference other rows, other lists, or other sites
  • Unsupported column types: Person or Group, Hyperlink or Picture, Managed Metadata, and multi-value columns cannot be referenced in a formula
  • Nesting limits: formulas support up to eight levels of nested functions, so very long IF chains belong in a Choice column or a flow instead
  • Cannot be indexed: a calculated column cannot serve as the indexed filter that keeps a large list under the 5,000-item List View Threshold
  • Read-only result: the value cannot be edited directly; to change it, change the columns the formula reads
  • Not every Excel function exists: SharePoint supports a large subset of Excel functions, so test the formula on a small list before rolling it out

Common Questions About the Calculated Column

What is a calculated column in SharePoint?

A calculated column is a list or library column that computes its own value from other columns in the same item, using Excel-style formulas. You pick a return type such as text, number, currency, date, or yes/no, and SharePoint evaluates the formula automatically whenever the item is created or edited. Common examples include due dates, status flags, and combined text fields.

Can I use Today or Me in a calculated column?

No. [Today] and [Me] are volatile functions, and SharePoint does not support them in calculated column formulas. The usual workarounds: use [Today] in a view filter or a column default value, use JSON column formatting for date-aware color cues, or use a scheduled Power Automate flow when you genuinely need a value that refreshes daily.

What Excel functions does SharePoint support in formulas?

A large subset of Excel’s library works: logical functions like IF, AND, and OR, text functions like CONCATENATE, LEFT, and TRIM, date functions like DATEDIF and WEEKDAY, plus the standard math operators. Formulas can nest up to eight levels deep. Not every Excel function made the cut, so test your formula on a small list before rolling it out broadly.

Why can’t I filter a large list by my calculated column?

Because calculated columns cannot be indexed, and only an indexed column can act as the first filter that brings a large list under the 5,000-item List View Threshold. The fix is to filter by the source columns the formula reads, which can be indexed normally, and keep the calculated column for display, grouping, and sorting within the filtered results.

Can a calculated column reference another list?

No. A formula can only read other columns in the same item; it cannot reach other rows, other lists, or other sites. When you need values from another list, use a Lookup column to pull them in, or a Power Automate flow to copy them over. Once the value lives in the item, the calculated column can use it like any other field.

When should I use a calculated column instead of Power Automate?

Use a calculated column when the answer comes from the same item and should update instantly on every edit: date math, status flags, text assembly. Use Power Automate when the logic crosses lists, depends on today’s date, or must send notifications. On LookBook 365 trackers, Greg Zelfond reaches for calculated columns first because they are zero-maintenance and entirely out of the box.