A Calculated Column derives its value from other columns on the same item, using Excel-like formula syntax — no Power Automate flow or code required. Here are the formulas worth knowing, and the real limitations that decide when a calculated column is the wrong tool.
In this post: Common formulas · Real limitations · Best practices · Pros and cons · Alternatives · Worked example: Days Until Due · When would you actually use this? · Related reading
Common formulas
Status indicator:
=IF([Percent Complete]=1, "Complete", "In Progress")Due date warning:
=IF([Due Date]Date difference in days:
=[End Date]-[Start Date]Concatenate text:
=[First Name] & " " & [Last Name]Priority flag:
=IF([Priority]="High", "Needs immediate attention", "Standard")Month name from a date:
=TEXT([Created],"mmmm")Real limitations
Worth knowing before building around calculated columns, not after: they can't reference lookup columns, person/group fields, or external data -- only other columns on the same item, of the types the formula language supports. They're also not editable in Power Apps forms and don't trigger Power Automate flows directly -- if a change needs to kick off a workflow, the calculated column itself won't do it.
The single most common gotcha: TODAY() does not refresh dynamically inside a calculated column. The value is computed once, when the item is created or last modified -- it doesn't recalculate itself daily just because a day passed. A "days overdue" column built naively with TODAY() will silently show stale numbers until the item is edited again. The common workaround uses a dummy column and view-level formatting instead of relying on the calculated column to stay current -- see this StackExchange thread for the specific technique.
Best practices
- Keep formulas simple -- a calculated column with deeply nested IF statements is hard to maintain and debug later.
- Reference columns by internal name, not display name, in the formula -- consistent with how every other SharePoint API/formula context works.
- Don't rely on TODAY() for anything that needs to stay current -- use the dummy-column workaround, or handle date-relative logic in a view/Power Automate instead.
- Choice fields work more predictably as plain text in formula comparisons than as their underlying choice objects.
Pros and cons
| Pros | Cons |
|---|---|
| No code or flow required | Limited data types -- no lookups or person fields |
| Instantly reflected in list views | TODAY() doesn't recalculate dynamically |
| Improves list readability and filtering | Can't trigger flows or automation |
| Easy for non-developers to set up | Not editable in Power Apps forms |
TODAY() does not refresh dynamically inside a calculated column -- the value is computed once, when the item is created or last modified, not recalculated daily.
Alternatives
| Method | When to use | Tradeoff |
|---|---|---|
| Power Automate | Needs to trigger other actions, or reference lookups/external data | More setup, sometimes needs premium licensing |
| Power Apps formula | Custom form logic | Not reflected in the list view itself |
| JSON column formatting | Visual indicators (color, icons) without a new computed value | Display-only, doesn't compute or store a value |
| SPFx extension | Logic beyond what formulas or JSON can express | Requires real development work |
Worked example: Days Until Due
- Create a new calculated column named
DaysLeft. - Formula:
=[Due Date]-TODAY()-- remembering the caveat above: this won't auto-update daily without the item being re-saved. - Add JSON view formatting on top to color-code the result: negative values red (overdue), under 3 yellow (urgent), 3 or more green (on track).
Reference: Microsoft's official formula syntax guide.
When would you actually use this?
- A list needs a status or label derived from other fields, purely for display -- no automation needed, just a readable computed value.
- You want filterable/sortable "days until due" or "days overdue" data without standing up a Power Automate flow for it.
- You're combining fields (first/last name, address parts) into one display value for a view, without a flow triggering on every edit.
References: Calculated column formula reference, List view JSON formatting samples.
Related reading
- SharePoint List View Formatting Overview -- the JSON formatting layer this post's alternatives and worked example both lean on.
- Understanding SharePoint List Types -- background on the list/library structures calculated columns get added to.
Good for quick, no-code logic -- just know where the real limits are before building something more critical around one. Questions? Comment below.
App Catalog Authentication Automation Backup Compliance Content Type CSS Flows Google Javascript Limitations List Metadata MFA Microsoft Node NodeJs O365 OneDrive Permissions PnP PnPJS Policy PowerApps Power Automate PowerAutomate PowerPlatform PowerShell React ReactJs Rest API Rest Endpoint Security Send an HTTP Request to SharePoint SharePoint SharePoint List SharePoint Modern SharePoint Online SPFX SPO Sync Tags Teams Termstore Versioning