Guides

Cohort Retention Analysis in Excel: Build a Customer Retention Table

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 6 October 2026 · Last updated: 6 October 2026

Zoya runs Zoya Tiffin Service, a fictional tiffin business in Pune. In October 2025 she got 16 new customers. In November, 8 of them ordered again (50.0%). By September 2026, 2 were still ordering (12.5%). Doing the same for every month's new customers and putting the results in one grid is a cohort retention table. It answers a question total sales never can: are the customers you win actually staying?

A cohort is a group of customers who started in the same month. A cohort retention table has one row per cohort and one column per month since the first order (month 0, 1, 2...). Each cell shows what share of that cohort bought again in that month. In Excel you need four helper columns in the orders table (order month, first month, month number, first order in the month) and one SUMIFS formula copied across the grid.

Download Cohort_Retention_Template.xlsx

Excel file, 50 KB. Built for Microsoft 365 Excel on Windows; it also opens in Excel 2021 and 2024. All numbers in this workbook are fictional teaching data, not real company figures.

The Cohort sheet with the sample data (Zoya Tiffin Service is a fictional business).

What the finished table looks like

Here are the first four cohorts of the sample, shown as retention %:

• Read across a row to follow one group of customers as they age.
• Read down a column to compare cohorts at the same age. If month 1 gets better for newer cohorts, something you changed is working.
• Month 0 is always 100%: everyone in a cohort bought in their first month, by definition.

Step 1: Prepare the orders table

1. Paste your orders with four columns: Order ID, Order Date, Customer ID, Amount.
2. Press Ctrl + T and name the table Orders.
3. Make sure Order ID is a number that grows with each new order (1001, 1002...). Step 3 uses it to spot repeat orders in the same month. If your IDs are text, add a column with =ROW() and use that instead.
4. Remove test orders and cancelled orders.

Step 2: Add the four helper columns

Here are the orders of one sample customer, T-204. Notice the 0 in First In Month for the second order inside the same month:

Why the First In Month column? Without it, a customer with three orders in one month would count as three. Retention is about customers, not orders, so each customer must count once per month.

Step 3: Build the count grid

1. On a new sheet, type the first cohort month in A7 (for example 01-10-2025) and =EDATE(A6,1)-style formulas below it: in A8 type =EDATE(A7,1) and fill down for 12 months.
2. Type the month numbers 0 to 11 across row 6, starting in C6. Give them the custom number format "M"0 so they show as M0, M1...
3. In B4 type =MAX(Orders[Order Month]): the last month that has data.
4. In C7 type the formula below and fill it right to N7 and down to row 18.
5. In B7 (New customers) use the same SUMIFS with Month No 0.

=IF(EDATE($A7,C$6)>$B$4,"",
   SUMIFS(Orders[First In Month],Orders[First Month],$A7,Orders[Month No],C$6))

The $ signs matter: $A7 keeps the cohort column fixed when you copy right, C$6 keeps the month row fixed when you copy down. The IF leaves cells blank when that month has not happened yet, which gives the familiar triangle shape. A blank is honest; a 0 would look like everyone left.

Step 4: Turn counts into percentages and colour them

1. Copy the cohort and New customers columns below the count grid (rows 23 to 34 in the template use =A7 and =B7).
2. In C23 type =IF(C7="","",IFERROR(C7/$B7,0)) and fill right and down.
3. Format the block as Percentage with one decimal.
4. Select the block and click Home > Conditional Formatting > Color Scales. Pick the white-to-green scale, or set your own minimum 0 and maximum 60% in Manage Rules.
5. Under the block add an Average row: =IFERROR(AVERAGE(C23:C34),""). AVERAGE skips the blank text cells, so each month averages only the cohorts that have reached it.

The average retention curve of the sample:

How to read the results

• The big drop is between month 0 and month 1 (100% to 40.4%). That first repeat order is the most important moment. Spend your effort there: a welcome offer, a reminder on day 20, a feedback call.
• After month 3 the curve flattens at roughly 20 to 30%. These are the regulars. A flat tail is good news; a curve that keeps falling to zero is a leaking bucket.
• Returning customers bring 58.6% of revenue in the sample. In September 2026, 65.7% of the month's revenue came from customers who started in an earlier month.
• Small cohorts jump around. A cohort of 10 customers moves 10 points when one person changes their mind. Do not celebrate or panic over one cell; look for patterns across several cohorts.

The PivotTable way (Windows)

1. Add the helper columns from Step 2 first.
2. Click inside Orders, choose Insert > PivotTable, and tick Add this data to the Data Model. This is what makes Distinct Count available.
3. Put First Month in Rows, Month No in Columns and Customer ID in Values.
4. Click the Customer ID field, choose Value Field Settings > Distinct Count.
5. For percentages, use Show Values As > % Of, Base field Month No, Base item 0.

The Data Model is not available in Excel for Mac or Excel for the web, so there the formula grid from the template is the way to go. The formula version also updates by itself; the PivotTable needs Data > Refresh All.

Common mistakes

• Counting orders instead of customers (fix: the First In Month column).
• Using the order date instead of the order month, so cohorts split by day.
• Showing zeros for months that have not happened yet. Use the EDATE check to leave them blank.
• Mixing in a half-finished current month. Its numbers look low only because the month is not over; end the table at the last complete month.
• Changing what counts as an active customer in the middle of the year. Pick a rule (at least one order in the month) and keep it.