Cohort Analysis in Excel — Free Template & Step-by-Step Guide

Cohort analysis is the fastest way to understand whether your customers are sticking around — and whether newer acquisitions are better than older ones. This tutorial walks through building a full cohort retention grid in pure Excel formulas: no add-ins, no macros, no paid tools. You’ll end up with a heatmap that reveals retention patterns, seasonal cohort effects, and the true long-term value of each acquisition wave.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.Step 1 — Prepare transaction data

Your source data should be a flat transaction log — one row per transaction, not one row per customer. At minimum you need three columns:

You need at least 6 months of data with multiple transactions per customer to see meaningful retention drop-off. Twelve months gives you a full period-0 through period-11 grid. Make sure OrderDate is stored as a proper Excel date (not text) — check by formatting a cell as a number; it should show a 5-digit serial.

Remove any test orders, internal orders, or zero-revenue refunds before proceeding. These will corrupt your acquisition month calculation.

2.Step 2 — Add acquisition month column

In column D, add a header CohortMonth. For each transaction row, we want the month of that customer’s very first purchase. We use MINIFS to find the earliest date for each CustomerID, then format it as YYYY-MM so cohorts are labelled by month:

This formula looks through all rows in column B (OrderDate) where column A (CustomerID) matches the current row’s CustomerID, and returns the minimum (earliest) date. TEXT(..., "YYYY-MM") converts it to a readable month label like 2024-01.

Drag this formula down to cover all rows. Every row for CUST-001 should now show the same CohortMonth — the month they first purchased. If you see different values for the same CustomerID, you have duplicate or inconsistent customer IDs in your data (check for leading spaces, capitalisation differences).

3.Step 3 — Add period number column

In column E, add a header PeriodNumber. This is the number of complete months between the customer’s acquisition month and the transaction’s month. Period 0 means the transaction happened in the acquisition month; Period 1 means one month later, and so on.

Breaking this down: D2&"-01" appends -01 to the YYYY-MM cohort label to get a valid date string like 2024-01-01. DATEVALUE converts that string to an Excel date. DATEDIF(..., B2, "M") counts whole months between the cohort start date and the transaction date.

A purchase on 2024-01-14 by a January cohort customer gives Period 0. The same customer purchasing on 2024-03-02 gives Period 2. Customers who only ever buy once will only have Period 0 rows.

4.Step 4 — Build the cohort pivot table

Select your entire data range including headers and insert a PivotTable (Insert → PivotTable → New Sheet). Configure it as follows:

Rows: CohortMonth — Columns: PeriodNumber — Values: CustomerID, summarised as Count Distinct (or COUNTA if Distinct Count is not available in your Excel version)

The result is a matrix where each row is an acquisition cohort (e.g. 2024-01) and each column is a period (0, 1, 2 … 11). Each cell shows how many unique customers from that cohort made at least one purchase in that period.

The Period 0 column will always be the largest for each row — it represents the full size of the cohort at acquisition. Later periods will be smaller as customers stop repurchasing.

5.Step 5 — Calculate retention rates

Copy your pivot output to a new sheet as Paste Special → Values (so it’s a static range, not a live pivot). Add a header row for Period 0 through Period 11.

In the cell adjacent to your first data cohort row (say the Period 1 cell is in column D, and Period 0 is in column C), enter:

The numerator C5 is the count for this period; the denominator $C5 locks the column to Period 0 while allowing the row to move as you drag down. Copy this formula across all period columns and down all cohort rows. Then format the entire retention grid as a percentage with one decimal place.

The Period 0 column should now show 100% for every cohort. Later periods will show the fraction of the original cohort that returned. A cell showing 24% in Period 3 means 24% of customers who first purchased in that cohort’s month were still making purchases three months later.

6.Step 6 — Apply conditional formatting heatmap

Select the entire retention grid (excluding the Period 0 column, or include it — both work). Go to Home → Conditional Formatting → Colour Scales → Green-White-Red.

Excel will automatically set the minimum (red), midpoint (white), and maximum (green) based on the range. You can customise the midpoint: setting it to 30% for e-commerce data makes the colour transitions more revealing in the typical range.

What to look for in the finished heatmap:

Diagonal banding: If a specific period column shows unexpectedly high retention across multiple cohorts, a campaign or promotional event likely ran in that calendar month.

Cohort rows that are consistently greener: These acquisition periods brought in higher-quality customers. Cross-reference with your marketing spend for those months.

Rapid drop-off in the first 2 periods: Typical for e-commerce. If Period 1 is below 15%, investigate onboarding, post-purchase email sequences, and product quality.

What good cohort retention looks like

Retention benchmarks vary enormously by business model. Use these as a starting point, not a target — your own trend over time is the more useful signal.

For SaaS, if Month 1 retention is below 70%, the product likely has an activation problem — customers are signing up but not finding the “aha moment” before their first bill. For e-commerce, Month 1 below 15% often indicates the acquisition channel is attracting one-time bargain hunters rather than loyal customers.

When to move beyond Excel

The Excel approach works well up to around 50,000 rows. Beyond that, the MINIFS formula becomes noticeably slow on each recalculation, and pivot refreshes can take tens of seconds. You also lose the ability to slice cohorts by channel, product category, or geography without rebuilding the whole grid.

Common pain points at scale: needing to rebuild the entire pivot every time new transactions come in; wanting to filter to a specific acquisition channel without re-doing the formulas; needing cohort LTV (revenue sum) alongside retention counts. Once you have the retention grid, the natural next steps are RFM analysis in Excel to score who is most valuable and a customer churn analysis in Excel to quantify who is leaving.