A step-by-step guide to building a customer cohort retention table in Excel: assigning each customer to the period of their first purchase with MINIFS, computing periods since first purchase for every invoice, counting active customers and summing revenue per cohort per period with COUNTIFS and SUMIFS, turning counts into retention percentages, reading the triangle, and the checks that prove no customer was lost on the way. Includes the exact formulas, the pivot-table route, and the point at which the spreadsheet stops being enough.
A cohort table answers one question that no average can: of the customers who started in a given period, how many are still here, and what are they worth now? This guide builds it in Excel from an invoice export.
Sheet Invoices, one row per invoice line: customer in A, invoice date in B, amount in C. Decide the period: this guide uses quarters.
A number that increases by one each quarter. D2:
=YEAR(B2)*4+INT((MONTH(B2)-1)/3)
For annual cohorts use =YEAR(B2).
The period of the first invoice. E2:
=MINIFS(D:D,A:A,A2)
Periods since first purchase. F2:
=D2-E2
Offset zero is the quarter the customer started.
Counting customers needs each customer once per period, not once per invoice line. Flag the first row for each customer and period, G2:
=COUNTIFS(A$2:A2,A2,D$2:D2,D2)=1
On sheet Cohort, cohort keys down column A from A3, offsets 0, 1, 2 and so on across row 2 from B2. Cell B3, filled across and down:
=IF($A3+B$2>MAX(Invoices!$D:$D),"",COUNTIFS(Invoices!$E:$E,$A3,Invoices!$F:$F,B$2,Invoices!$G:$G,TRUE))
The first test leaves cells blank for periods that have not happened yet. That is what makes the table a triangle, and it matters: a zero there would read as total loss.
A readable cohort label in a spare column:
=INT($A3/4)&" Q"&(MOD($A3,4)+1)
On a second block, cell by cell:
=IF(B3="","",B3/$B3)
| Cohort | Size | Q+1 | Q+2 | Q+3 | Q+4 | Q+8 |
|---|---|---|---|---|---|---|
| 2024 Q1 | 80 | 85% | 78% | 74% | 71% | 62% |
| 2024 Q2 | 95 | 83% | 76% | 72% | 70% | 60% |
| 2024 Q3 | 70 | 84% | 77% | 73% | 69% | |
| 2025 Q1 | 110 | 72% | 61% | 55% | ||
| 2025 Q2 | 90 | 70% | 58% |
The 2024 cohorts lose about 15 percent in the first quarter and then flatten. The 2025 cohorts lose nearly 30 percent. Something changed in who was sold to, or how they were onboarded, at the start of 2025. No blended retention figure would show it. The guide to reading a cohort table covers the column, the row and the diagonal.
The same table with SUMIFS and without the first-row flag:
=IF($A3+B$2>MAX(Invoices!$D:$D),"",SUMIFS(Invoices!$C:$C,Invoices!$E:$E,$A3,Invoices!$F:$F,B$2))
Then divide by the offset-zero column as before. Expect revenue retention to sit above customer retention in a healthy base. One caution: the offset-zero quarter is usually a part quarter, because customers start on any day within it, so revenue at offset one often exceeds offset zero. Use offset one as the base for revenue retention, or compare full years.
Every customer is in exactly one cohort, so the offset-zero column sums to the number of customers:
=SUM(Cohort!B3:B200)-SUMPRODUCT(1/COUNTIF(Invoices!A2:A50000,Invoices!A2:A50000))
Zero. On large files replace the SUMPRODUCT with ROWS(UNIQUE(...)). And no cell exceeds its cohort size: a retention above 100 percent of customers means rows are being counted, not customers.
Add columns D, E and F to the data, insert a pivot table with the Data Model ticked, put Cohort in Rows, Offset in Columns, and Customer in Values as Distinct Count. Show Values As, % of Row Total does not work here, since the base is the first column, not the row total; add the percentage block beside the pivot with formulas.
Monthly cohorts in a B2B business. Alternate-month buyers flicker in and out and retention looks terrible.
Zeros in future cells. The newest cohorts appear to collapse.
History that starts mid-stream. If the export begins in 2023, every older customer lands in the first cohort, which becomes huge and unrepresentative. Label it as existing customers and read the cohorts after it.
Renamed customers. One leaves an old cohort and joins a new one. Map names to parents first.
Active means any invoice. A single small purchase keeps a customer retained. Consider a minimum amount per period.
One cohort table for a board pack is a fine spreadsheet job. Cohorts cut by segment, by acquiring rep and by first product, refreshed each quarter, with the broken cohort traced to its accounts, is where it strains; see the four signs a spreadsheet is no longer enough. For the revenue version tied to recurring revenue, see net revenue retention by cohort and the NRR guide for Excel. Covirage builds both tables from the invoice export, with the checks built in and the accounts behind every cell one click away.
Quarterly or annual. Monthly cohorts come from consumer subscriptions, where everyone is billed monthly. A B2B customer who orders every six weeks would look churned in alternate months. The period should be longer than the longest normal gap between orders for most customers; for many distributors that is a quarter, for capital equipment a year.
Both, side by side. Customer retention shows whether accounts stay; revenue retention shows whether they grow. A cohort at 70 percent of customers and 110 percent of revenue has lost its small accounts and grown its large ones, which is a healthy pattern. The reverse, 90 and 60, is a base that stays and shrinks.
Add the data to the Data Model when creating the pivot, then in Value Field Settings choose Distinct Count. Without it, a pivot counts invoice rows, not customers, and retention comes out above 100 percent.