Cohort Analysis in Excel: Build a Retention Heatmap (2026)

June 13, 2026 · VeloraAI Team
Data Analysis Financial Modeling Excel

A SaaS company can report a healthy 6% monthly churn rate while quietly bleeding out — because aggregate churn hides the truth. Cohort analysis in Excel breaks that illusion. Group customers by the month they signed up, then track each group's retention curve and you see the real story: maybe January's signups churned 22% by month three while April's barely lost 8%. That gap is where product-market fit, pricing changes, and onboarding wins all show up.

This guide walks through the exact workflow analysts use to build a cohort retention heatmap from raw transaction data — formulas, pivot configuration, conditional formatting, and the LTV math that turns the table into a business decision.

What Is a Cohort Analysis in Excel?

A cohort analysis in Excel is a table that groups customers by a shared starting characteristic — usually their first purchase month — and tracks a metric (retention, revenue, orders) for each group across subsequent months. The result is a triangular grid where rows are cohorts, columns are months since acquisition, and cells are the percentage of the original cohort still active.

The triangular shape comes from time: a cohort that signed up in January 2026 has 12 months of history by year-end, while a December cohort only has one. Older cohorts get longer rows; newer cohorts get shorter ones.

ℹ️ Note: Cohort analysis is sometimes called a "retention curve" or "vintage analysis." Banks use the same approach for loan default vintages — the math is identical, only the metric changes.

What Data Do You Need to Build One?

You need three columns of transaction-level data at minimum:

  1. Customer ID — a stable identifier per customer
  2. Transaction Date — the date of each billing event or order
  3. Revenue (optional, but required for net revenue retention)

Most billing systems (Stripe, Chargebee, Recurly, NetSuite) can export this directly. If you only have monthly summary data, you cannot build a true cohort table — you need row-level events.

⚠️ Warning: Make sure dates are stored as real Excel dates, not text. Real dates align right in unformatted cells; text dates align left. Convert with DATEVALUE before proceeding or every formula downstream will silently break.

How Do You Build a Cohort Retention Heatmap in Excel?

To build a cohort retention heatmap in Excel: (1) assign each customer to a cohort based on first transaction month, (2) calculate months-since-signup for every transaction, (3) build a pivot table with cohorts as rows and month-offset as columns counting unique customers, (4) divide each cell by the cohort's Month 0 value to get retention percentages, then (5) apply a color-scale conditional format to visualize decay patterns.

Below is the detailed build, formula by formula.

Step 1: Assign Each Customer to Their Cohort

In a helper column next to your transaction data, calculate the cohort month — the first month that customer ever transacted. Assuming customer IDs are in column A and dates are in column B:

=EOMONTH(MINIFS($B:$B, $A:$A, A2), -1) + 1

MINIFS returns the earliest transaction date for the customer in A2, and EOMONTH(..., -1) + 1 normalizes it to the first day of that month. Now every row knows which cohort it belongs to, regardless of when the row's transaction happened.

Step 2: Calculate Months Since Signup

In the next helper column, compute the offset between the current transaction month and the cohort month:

=DATEDIF(C2, EOMONTH(B2, -1) + 1, "m")

Here C2 is the cohort month from Step 1 and B2 is the current row's transaction date. The result — 0, 1, 2, 3, etc. — is the month offset that will become your column header in the cohort grid.

💡 Pro Tip: If a customer skips a month then comes back, that gap matters. The standard convention is "if they have any transaction in month N, they are retained in month N." Use this for retention. Use revenue summed per month if you're building net revenue retention.

Step 3: Build the Cohort Pivot Table

Select your data range, insert a PivotTable, and configure it:

  • Rows: Cohort Month (the column you built in Step 1)
  • Columns: Months Since Signup (Step 2)
  • Values: Count of Distinct Customer ID

In modern Excel, set the Value Field Settings to "Distinct Count" to avoid double-counting customers who transacted twice in the same month. You should see a triangular table — that's expected. For a comprehensive guide to PivotTable configuration, calculated items, and slicer-driven dashboards that turn these outputs into a live management report, see our Excel pivot tables for financial reporting guide.

graph LR
    A[Raw Transactions] --> B[Add Cohort Month Column]
    B --> C[Add Months Since Signup]
    C --> D[Pivot: Distinct Customer Count]
    D --> E[Divide by Month 0 = Retention %]
    E --> F[Color Scale Heatmap]

Step 4: Convert Counts to Retention Percentages

The pivot gives you absolute customer counts. To turn it into a retention grid, copy the pivot values to a new range (paste as values to break the pivot link) and divide every cell by the first column of its row:

=B5/$B5

Drag this across and down. Format as percentages. Month 0 will be 100% by definition; every cell to the right shows what fraction of that cohort remained.

Step 5: Apply a Color Scale Heatmap

Select the retention grid (excluding the 100% Month 0 column for better contrast), go to Home → Conditional Formatting → Color Scales, and choose a three-color scale: green for high retention, yellow for middle, red for low. Excel handles the gradient.

Example: A SaaS business with 1,000 January signups sees 720 retained in Month 1, 580 in Month 2, 510 in Month 3. The retention row reads: 100%, 72%, 58%, 51%. That curve is your story — share it with leadership and you've justified the entire onboarding redesign.

What Formulas Should You Use Without a Pivot Table?

If you want a self-recalculating cohort table — useful when source data refreshes — skip the pivot and use COUNTIFS directly. Place cohort months down column A starting at A5, and month offsets across row 4 starting at B4:

=COUNTIFS(transactions[CohortMonth], $A5, transactions[MonthsSinceSignup], B$4)

This counts how many distinct customer-month rows match each (cohort, offset) pair. To get true distinct customer counts without a pivot, use SUMPRODUCT with a helper or COUNTIFS over a pre-deduplicated table.

For retention percentages, layer a second formula table:

=IFERROR(B5/$B5, "")

The IFERROR wraps gracefully around the empty cells on the bottom-right of the triangle.

💡 Pro Tip: Wrap the whole thing in a LET block to name your sub-expressions. A 200-character cohort formula becomes readable, and Excel only computes each piece once. See our LET function guide for the pattern.

How Do You Calculate LTV From a Cohort Table?

Customer lifetime value (LTV) is the area under the retention curve multiplied by average revenue per user. Once you have a cohort retention grid, the calculation is:

LTV = ARPU × SUM(retention curve) × Gross Margin

In Excel, if your retention curve for a single cohort is in B5:M5 and ARPU is in N5:

=SUM(B5:M5) * N5 * 0.75

The 0.75 represents a 75% gross margin — adjust to your actual contribution margin. For a cohort with a retention curve summing to 8.4 customer-months and $200 ARPU, LTV is $200 × 8.4 × 0.75 = $1,260.

Net Revenue Retention vs Customer Retention

Customer retention (also called "logo retention") counts heads. Net revenue retention (NRR) counts dollars and includes upsells. NRR can exceed 100% if expansion revenue outpaces churn — that's the "negative churn" SaaS investors obsess over.

Metric What It Measures Formula Why It Matters
Gross Logo Retention % of customers still active Customers in Month N ÷ Customers in Month 0 Onboarding and product fit
Net Revenue Retention $ from same cohort with upsells Revenue in Month N ÷ Revenue in Month 0 Pricing power, expansion
Gross Revenue Retention $ excluding upsells (Revenue in Month N – Expansion) ÷ Month 0 Revenue True churn impact on revenue
Average Revenue Per User Revenue normalized per customer Total Revenue ÷ Active Customers Pricing trends per cohort
Customer Lifetime Value Total $ a customer is worth ARPU × SUM(retention) × Gross Margin Justifies CAC spend

Best-in-class SaaS shows >120% NRR and >90% gross logo retention. PE-backed B2C subscription businesses often run 60-70% gross retention but win on volume.

What Common Mistakes Should You Avoid?

Cohort tables are easy to build wrong. The five mistakes I see most often in analyst work:

  1. Counting non-distinct customers. A customer who pays twice in March counts twice if you use COUNT instead of DISTINCT COUNT. Always deduplicate first.
  2. Mixing trial and paid users. Cohort by first paid date, not signup date, unless you specifically want trial-to-paid conversion.
  3. Including the current month. The current month is incomplete and drags the curve down artificially. Cut the table off at the last fully-closed month.
  4. Ignoring billing frequency. Annual subscribers won't have a transaction in months 2-12 even if they're retained. Use a "last seen" rule or pre-fill expected months for annual cohorts.
  5. No timezone normalization. A transaction at 11pm Pacific on Jan 31 is Feb 1 UTC. Pick a timezone and stick to it before bucketing into months.

⚠️ Warning: Pivot tables in Excel are sensitive to source data changes. If you refresh transactions and a customer's earliest date shifts (e.g., a refund moves their start date), the cohort assignment changes retroactively and the table looks different. Lock cohort assignments to a snapshot for board reporting.

Frequently Asked Questions

How many months of data do I need for cohort analysis to be meaningful?

You need at minimum six months for a SaaS business and 12 months for any annual subscription product. Anything shorter and the retention curve hasn't stabilized — early-month retention is dominated by trial-to-paid conversion noise, not genuine product retention. For LTV calculations, 18-24 months of cohort history makes the curve-fitting much more reliable.

What's the difference between a cohort and a segment?

A cohort is defined by when something happened — usually first transaction date. A segment is defined by what a customer is — industry, plan tier, geography, persona. The two are complementary: you can cohort by signup month and then segment each cohort by plan tier to see if enterprise customers retain better than self-serve.

Can Excel handle cohort analysis on millions of rows?

Standard Excel slows down past 100,000 rows of source data when you're using volatile pivot recalculation. For larger datasets, load the transaction data into Power Query, perform the cohort and month-offset assignments there, and load the result to a pivot. Power Query handles tens of millions of rows efficiently and only refreshes when you ask it to. See our Power Query for financial reporting walkthrough.

How do I extrapolate a retention curve to estimate long-term LTV?

Fit a curve to the historical retention data — common shapes are exponential decay (y = a·e^(-bx)), power law (y = a·x^(-b)), or a sBG (shifted beta-geometric) model. Use Excel's LINEST on the log-transformed series for exponential fits, or solve via Solver minimizing squared residuals. Then project the curve out 36-60 months and sum to get terminal LTV.

Should I cohort by acquisition channel?

Yes — but only after your dataset is big enough. Cohorting by month and channel quickly creates tiny cells that aren't statistically meaningful. A rule of thumb: each cohort cell should have at least 30 customers for the retention rate to be reliable. If you have 200 monthly signups across five channels, the channel-month cohorts are too noisy. Aggregate to quarterly cohorts by channel instead.

Putting It Into Practice

Cohort analysis in Excel is the highest-leverage retention diagnostic an FP&A or RevOps analyst can build. It takes raw billing data and turns it into a pattern you can act on — onboarding fixes, pricing tests, expansion playbooks — all anchored to whether the new tactic moved the cohort curve relative to the baseline. For the broader financial context these cohort metrics feed into — MRR waterfall, ARR bridge, and 5-year operating model — see our SaaS startup financial model guide.

The mechanical part of the build is straightforward once you've done it once. The harder part is keeping the table fresh, reproducible, and trusted across the org. That's where modern AI tooling earns its keep: VeloraAI lets you describe the cohort logic in plain English — "build a monthly retention cohort from this transaction table, distinct count by customer, output retention percentages with a color scale" — and it generates the formulas, pivot, and formatting directly in your workbook.

Build the table once this week. Refresh it monthly. Watch which cohorts diverge, ask why, and let the data drive the conversation with product and sales.