How to Create a Calculated Column in SharePoint
Calculated columns derive values from other columns automatically. Used wisely, they reduce manual entry and create powerful summary fields.
What it is
A Calculated column uses a formula (Excel-style) to compute its value from other columns in the same row. Common uses include automatic review dates (‘Modified date + 365 days’), summary strings (combine document type + department + status into a searchable label), and conditional flags (‘Yes’ if Status equals Approved). The user never types into a Calculated column directly — SharePoint computes the value automatically whenever the underlying data changes.
Calculated columns are powerful because they remove duplicate work. Instead of asking users to manually type the review date when they create a file, a calculated column generates it from the creation date plus a standard offset. Instead of asking users to remember to update a status flag, a calculated column derives it from other fields.
The constraints matter. Calculated columns can’t reference Person or Managed Metadata columns directly (you can work around this with secondary fields). They can’t be used in some workflow scenarios. And complex formulas become brittle and hard to maintain. Use Calculated columns for simple, well-defined derivations — not as a substitute for Power Automate or proper data modelling.
When to use this
- When one column’s value can be derived reliably from others.
- For automatic review dates, expiry dates, summary fields, conditional labels.
- For combining fields into a searchable composite (e.g. ‘CONTRACT-Marketing-2026’).
- Not for complex business logic — use Power Automate for anything beyond simple derivations.
How to do it
- Click + Add column → Calculated.
- Type the formula using existing column names in square brackets, e.g.
=[Modified]+365. - Set the data type for the result (text, number, date, currency).
- Save and test on existing items to confirm the formula works.
- Adjust if results aren’t what you expected.
- Verify the column updates correctly when the source columns change.
Best practices
- Test formulas in Excel first. The syntax is identical, and Excel gives clearer error messages.
- Keep formulas simple. Complex multi-condition formulas become unmaintainable. If it’s complex, use Power Automate.
- Document the formula. In the column description, explain what it does so future admins understand.
- Be aware of the constraints. Person columns and Managed Metadata can’t be referenced directly — plan around this.
Common mistakes
- Complex business logic in a calculated column. Becomes brittle, hard to maintain, easy to break. Use Power Automate.
- Trying to reference Person or Managed Metadata columns directly. Won’t work. Use intermediate columns or different approaches.
- No documentation. Six months later, nobody remembers what the formula does or why.
The File Sanity Kit gives you the Container Method™ — audit, restructure, and future-proof SharePoint without IT admin. The complete methodology, full workbook, and 8-tab Excel planner.
Get the File Sanity Kit — $27 →FAQ
What is a Calculated column in SharePoint?
A Calculated column doesn’t store a user-entered value — it computes its value from a formula based on other columns in the same item. Common uses: Review Date = [Modified] + 365 (yearly review); Days Until Expiry = [Expiry] – Today; Custom ID = [Department] & “-” & [DocType] & “-” & [ID]. The formula syntax is borrowed from Excel.
Can a Calculated column reference Person or Managed Metadata columns?
Not directly — and this is the biggest constraint. Calculated columns can only work with Text, Number, Date, and Yes/No columns. To reference a Person column, you’d need a Power Automate flow that copies the person’s name into a Text column first, then the Calculated column can use that Text. Same workaround for Managed Metadata.
Why isn’t my Calculated column updating?
Calculated columns only recalculate when the item itself is modified — not on a schedule, and not when referenced columns change in other items. If you need a value that updates daily (like ‘Days Until Expiry’), you need a Power Automate flow that touches every item daily, or you need to use a JSON column formatter that shows the calculation in real-time without storing it.
What’s the formula syntax for Calculated columns in SharePoint?
Same as Excel — IF, AND, OR, CONCATENATE, DATE, TEXT, plus standard operators. Column references use square brackets: [Modified], [Status], [Department]. Test the formula in Excel first (same syntax, faster iteration), then paste into SharePoint. SharePoint validates on save and reports the line where it fails.