Loading tutorials…
Loading tutorials…
Most Airtable bases use 10% of the formula engine. The other 90% is what separates a 'fancy spreadsheet' from a real ops system. This walks the patterns that matter — and the formulas that should never have been written.
Who this is forOperators who built a base with simple fields and are now stuck doing math manually outside Airtable. If you ever export to Excel to calculate something, you needed a formula or rollup. This tutorial closes the gap.
What you'll need
Step 1
Every formula has a return type (number, text, date, boolean, array). Mismatches between formula type and field usage are 60% of all formula bugs.
Open a table → "+ Add field" → "Formula." The formula editor appears.
Formulas return one of: Number, Text, Date, Boolean, Array. Airtable infers the type from the formula content.
A formula like IF({Score} > 5, "High", "Low") returns Text — fine for display, useless for math.
A formula like {Amount} * 0.85 returns Number — formats as currency or percent based on the "Format" dropdown in the formula field config.
A formula like DATETIME_PARSE({Date String}) returns Date — usable in Calendar views, filterable as a date.
Always check the inferred return type after writing a formula. Mismatched type = downstream bugs in views, sorts, and rollups.
Step 2
Conditional logic is the single most useful formula category. IF for binary, SWITCH for multi-way, AND/OR for combined conditions.
IF basic: IF({Score} > 80, "A", IF({Score} > 60, "B", "C")). Nested IFs work but get unreadable past 3 levels.
SWITCH cleaner: SWITCH({Stage}, "Lead", "🟡", "Qualified", "🟠", "Won", "🟢", "Lost", "🔴", "❓"). One line, all stages, default for unknown values.
AND/OR combine: IF(AND({Status} = "Active", {Owner Filled} = TRUE()), "Ready", "Needs Owner"). Use AND for "all must be true," OR for "any must be true."
NOT for negation: IF(NOT({Status} = "Archived"), {Score}, BLANK()). Equivalent to {Status} != "Archived".
Common pattern — "stale" detection: IF(DATETIME_DIFF(TODAY(), {Last Touch}, "days") > 30, "Stale", "Fresh"). Combined with conditional row coloring in views.
Step 3
Dates are where most formula bugs live. DATETIME_DIFF, DATEADD, DATETIME_FORMAT, IS_AFTER are the workhorses.
DATETIME_DIFF: DATETIME_DIFF({Due Date}, TODAY(), "days") returns positive if Due is in future, negative if past. Use for "days to deadline" and "days overdue."
DATEADD: DATEADD({Created Date}, 14, "days") returns the date 14 days after creation. Use for SLA tracking, follow-up reminders.
DATETIME_FORMAT: DATETIME_FORMAT({Created Date}, "MMM D, YYYY") returns "May 26, 2026" — readable display format.
IS_AFTER / IS_BEFORE: IS_AFTER({Due Date}, TODAY()) returns TRUE/FALSE. Useful inside IFs.
WEEKDAY: WEEKDAY({Date}, "Monday") returns 0-6, useful for week-based grouping.
Common bug: DATETIME_DIFF without specifying unit returns milliseconds. ALWAYS include the unit ("days," "hours," "minutes").
Step 4
Rollups summarize a field across all linked records. Count, sum, average, min, max, ARRAYUNIQUE, COUNTIF — the aggregation engine.
Add a Rollup field: "+ Add field" → "Rollup" → choose a Linked record field on this table → choose a field from the linked table → choose an aggregation function.
SUM(values) — total of numeric values across all linked records. Common: Companies → Deals.Amount → SUM = total pipeline per company.
COUNTIF(values, condition) — count linked records matching a condition. Common: Companies → Deals → COUNTIF(values, {Stage} = "Won") = won deals per company.
ARRAYUNIQUE(values) — distinct values. Common: Campaigns → Content.Owner → ARRAYUNIQUE = list of all writers on a campaign.
MAX(values) — most recent date. Common: Companies → Activities.Date → MAX = last activity per company.
AVERAGE(values) — for numerical fields. Common: Companies → Deals.Amount → AVERAGE = average deal size.
Rollups are read-only; you cannot manually override them. That is the point.
Step 5
Lookups grab a field value from a single linked record (or array of values from multiple). Use to display parent context inline.
Add a Lookup: "+ Add field" → "Lookup" → choose the Linked record field → choose the field from the linked table to pull.
Common: on Content, add Lookup "Campaign Quarter" pulling Campaign.Quarter. Now Grid views show the quarter without opening the Campaign row.
Lookups vs Rollups: Lookup pulls a value; Rollup aggregates values. If the Linked record allows multiple, Lookup returns an array of values.
Lookups are read-only and update automatically when the source field changes.
Common pattern: on Deals, Lookup Company.Industry and Company.Size. Deal Grid views can group/filter by these without manual entry.
Avoid 10+ Lookups on one table — each adds a small load cost. Pull only what the views actually display.
Step 6
Text manipulation is the unsexy but constant work. Concatenate names, normalize email casing, extract URL slugs.
CONCATENATE / "&" operator: {First Name} & " " & {Last Name} or CONCATENATE({First Name}, " ", {Last Name}). Build full names, formatted strings, URL paths.
LEN: LEN({Title}) returns character count. Use for "is title too short" warnings: IF(LEN({Title}) < 30, "⚠️ Too short", "OK").
LOWER / UPPER / PROPER: LOWER({Email}) normalizes casing — useful before deduplication.
SUBSTITUTE: SUBSTITUTE({Phone}, " ", "") strips spaces. Chain to clean up messy inputs.
REGEX_MATCH / REGEX_EXTRACT / REGEX_REPLACE: full regex support. Common: REGEX_EXTRACT({URL}, "https?://([^/]+)") extracts the domain.
TRIM: TRIM({Notes}) strips leading/trailing whitespace. Add to user-input text before reporting on it.
Step 7
Formulas accumulate. Some break when fields rename. Some are duplicated across 5 tables. Audit quarterly.
Run a base health audit: open each table → look at every Formula, Rollup, Lookup, Count field.
For each: (a) is it returning expected values on sample records? (b) is the field referenced in any view, automation, interface, or rollup? (c) could it be simplified?
Common cleanup wins: replace nested IFs with SWITCH (readability), consolidate 3 lookups into one CONCATENATE-d formula (performance), delete formulas no view references (decluttering).
Document complex formulas in a Long text field on a "Formula Documentation" table — future-you needs to know WHY this formula exists, not just what it does.
Test changes on a duplicate base before changing live — a formula referenced by 4 rollups + 3 automations is risky to edit.
Common mistakes
Manually typing values that could be calculated
What goes wrong: Team manually enters 'Days to Deadline,' 'Quarter,' 'Campaign Owner' as text. Values drift within a week. Reports show stale data. A Q2 campaign accidentally lands in Q1 reporting and you misallocate $20-50K of budget.
How to avoid: Any value derivable from other fields is a Formula (Quarter from Publish Date), Lookup (Campaign Owner from linked Campaign), or Rollup. Manual entry is for inputs only.
Nested IFs five levels deep
What goes wrong: Formula like IF(A, IF(B, IF(C, IF(D, IF(E, ...) ...) ...) ...) ...). Unreadable. New hire spends 30 min decoding it. Bug fixes cause cascading errors. ~$300-600/quarter of debugging time.
How to avoid: Use SWITCH for multi-way conditions. For complex logic, split into multiple formula fields (intermediate values) rather than one giant formula.
Rollup without the right aggregation function
What goes wrong: Rollup of Deals.Amount with aggregation 'COUNT' instead of 'SUM' — reports show 'how many deals' not 'how much pipeline.' Forecasts are off by 10-100x. Decisions made on bad data cost $50-200K/quarter.
How to avoid: Always double-check the aggregation function. SUM for currency totals, COUNT for record counts, MAX for "most recent," ARRAYUNIQUE for distinct values. Test on sample records.
DATETIME_DIFF without a unit specifier
What goes wrong: DATETIME_DIFF({Due}, TODAY()) without 'days' returns milliseconds. Number field shows '1209600000' instead of '14.' Sorting and conditional formatting break. The team gives up on the field and stops trusting date math.
How to avoid: Always include the unit: "days", "hours", "minutes", "weeks", "months", "years". Same for DATEADD.
Lookups that should be Rollups
What goes wrong: Lookup on Companies → Deals.Amount returns an array of amounts ('$5,000, $12,000, $3,500'). Cannot sort by it, cannot sum it in views. The team copies values into Excel to total them. ~$200-400/mo of wasted analyst time.
How to avoid: For aggregations (sum, count, max), use Rollup. For pulling a single related value, use Lookup. Lookups on one-to-many relationships return arrays — usually not what you want.
Hardcoded values inside formulas
What goes wrong: Formula like IF({Score} > 80, ...) with the threshold 80 hardcoded. When the business changes the threshold to 75, you have to edit every formula that references it. Easy to miss one. ~$500-1,000/quarter of inconsistency costs.
How to avoid: Use a single-row "Settings" table with a Score Threshold field. Lookup that field in every formula that needs it. Change in one place, propagates everywhere.
Recap
Done — what's next
How to set up an Airtable base for marketing without rebuilding it in month two
Read the next tutorial
Hand it off
Formulas, rollups, and lookups are where Airtable goes from spreadsheet to ops system. A specialist will audit your base, refactor messy formulas, fix subtle bugs, and document complex logic — typically $200-400 for a one-shot audit. Ongoing maintenance for bases with 50+ formula fields runs $400-1,200/mo at $14-16/hr.
See specialist rates
Formula: calculation purely from fields on the same record (Days to Deadline = Due - Today). Lookup: pulling a value from a linked record (Campaign Owner from linked Campaign). Rollup: aggregating values across multiple linked records (Sum of Deals per Company). Use the simplest tool for the job — formulas first, then lookups, then rollups.
Airtable detects and blocks true circular references (A depends on B which depends on A). If you hit one, you usually have a data model problem — the relationship should be one-directional. Restructure so the dependency flows one way (e.g., Deals depend on Companies, not the other way around).
Three usual causes: (1) the field being rolled up is empty on all linked records — there is nothing to aggregate. (2) The aggregation function does not match the field type (SUM on a text field returns NaN). (3) The linked record link is broken (renamed or deleted). Open the rollup config and test the aggregation function against the actual field type.
Not directly — formulas only see the current record. For cross-row math (rank, percentile, running total), use Rollups (aggregating linked records) or a script automation. Or pull data to a script-block automation, do the math, write back to the record.
Yes. A formula that depends on 5 lookups that each depend on 3 rollups creates a calculation tree that recomputes on every edit. Bases with 50K+ records and complex formula chains can become sluggish (5-15 second save delays). Solutions: simplify formula chains, cache intermediate values in regular fields updated by automations, or split into multiple bases linked via Sync.
Airtable
Airtable is fast to spin up and easy to wire wrong. Most marketing bases hit a wall at 3,000 records or 10 linked tables because the schema was built around the first idea, not the second year of data. This walks the base structure that holds up.
Airtable
Airtable as a CRM is great for teams under 5 reps with simple sales motions — and terrible past that point. This walks the right structure for the right team, and tells you bluntly when you should be on HubSpot or Pipedrive instead.
Airtable
Airtable feels fast until your base hits a wall — usually around 10K records on Team or 50K on Business. The wall is rarely the record count itself; it is what you built on top of it. This walks the diagnostic sequence specialists run.
Airtable
DIY Airtable is a great idea — until it isn't. This is the honest framework: when the cost of self-managing a base exceeds the cost of hiring help, and how to tell which side you're on.