How to Do a Cohort Retention Analysis in Excel

A cohort analysis in Excel groups customers by the month they signed up and tracks what share of each group is still active one, two and three months later. You need three things: a cohort month for each customer, a month number for each activity, and a COUNTIFS grid that turns them into a retention table. This guide builds one step by step, for analysts, marketers and anyone preparing for a data analyst job test.

What is a cohort retention table?#

A cohort is a group of people who started in the same period. A retention table puts one cohort on each row and the months since signup across the columns:

These numbers are an example. Read across a row to see how one cohort fades over time: of the 50 January customers, 60% were active in their second month and 48% in their third. Read down a column to compare cohorts at the same age: February kept 65% in month 1, against January's 60%. The blank cells are months that haven't happened yet, which gives the table its triangle shape.

Decide first what "active" means for your business, such as made a purchase, logged in or renewed. The whole analysis depends on it.

The data you need#

You need two facts per customer: when they signed up and when they were active. The simplest layout is an activity table with one row per customer per active month:

If your raw data is a log of every order or login, a customer can appear many times in one month. COUNTIFS counts rows, not people, so those duplicates would inflate the counts. Reduce the log to one row per customer per month first: add the activity month column below, then use Data > Remove Duplicates on the Customer and Activity month columns.

Step 1: Add cohort and month number columns#

Three helper columns turn raw dates into something you can count.

• EOMONTH(B2,-1) returns the last day of the previous month, and +1 moves to the first day of this month. Every January date becomes 1 Jan 2026, which makes a clean label for grouping. Format the column as mmm yyyy.
• The month number counts whole calendar months between the two month starts. The YEAR and MONTH version works across year ends. =DATEDIF(D2,E2,"m") gives the same result here, because both dates are first-of-month.

The EOMONTH function guide and our article on EDATE and EOMONTH explain the month functions. Month 0 is activity in the signup month itself.

Step 2: Build the cohort grid with COUNTIFS#

Put the cohort months down the side and the month numbers across the top, then count each pair. With cohorts in H3:H5 (1 Jan, 1 Feb and 1 Mar 2026) and month numbers 0, 1 and 2 in J2:L2, type this in J3:

=COUNTIFS($D$2:$D$500,$H3,$F$2:$F$500,J$2)

Fill it across and down. Each cell counts the rows where the cohort matches the row label and the month number matches the column label. The mixed references do the work: $H3 always reads column H, and J$2 always reads row 2.

For the cohort size in I3, count customers by signup date from a customer list with one row per customer:

=COUNTIFS(Customers!$B$2:$B$200,">="&H3,Customers!$B$2:$B$200,"<="&EOMONTH(H3,0))

This counts signups from the first to the last day of the cohort month. If your signup dates include times, use "<"&EOMONTH(H3,0)+1 for the upper limit instead, so late signups on the last day are included. The COUNTIFS function guide and the article on COUNTIFS and SUMIFS cover the criteria syntax.

Step 3: Turn counts into retention percentages#

Divide each count by the cohort size. In a second grid, type this and fill it across and down:

=IF(J3=0,"",J3/$I3)

Format it as a percentage. The $ on column I keeps every cell dividing by its own row's cohort size. The IF leaves a blank instead of 0% for future months, which keeps the triangle readable. If a real month can have zero active customers, use a test on the date instead, such as =IF(EDATE($H3,J$2)>$B$1,"",J3/$I3) with the latest data month in B1.

Add a colour scale from Home > Conditional Formatting > Color Scales so high retention stands out at a glance.

The pivot table way#

A pivot table builds the same grid without COUNTIFS, once the helper columns exist:

1. Select the activity table and choose Insert > PivotTable.
2. Drag Cohort to Rows, Month number to Columns and Customer to Values (it shows Count of Customer).
3. Right-click a value and choose Show Values As > % Of. Set the base field to Month number and the base item to 0.

Each cell now shows the share of that cohort's month 0 count. This uses month-0 activity as the cohort size, which matches only if every customer is active in their signup month.

If your table still has duplicate customer-month rows, tick Add this data to the Data Model when you create the pivot, then set the value field to Distinct Count. This option is in Excel for Windows and isn't available in every version. If you'd rather group the raw dates inside the pivot, see grouping dates by month in a pivot table, and our guide to Show Values As explains the % Of option.

How to read the results and common mistakes#

Look for three patterns: a steep drop from month 0 to month 1 (an onboarding problem), a curve that flattens (a loyal core), and newer cohorts that sit higher than older ones in the same column (an improvement).

Watch for these mistakes:

1. Counting rows instead of customers. Duplicates push retention above 100%. De-duplicate first.
2. Mixing calendar months and months since signup. The columns must be month numbers, not calendar months.
3. Reading small cohorts too closely. A cohort of 8 customers can swing from 50% to 25% because of one person.
4. Counting a partial month. If your data stops on 15 March, March looks worse than it is. Leave the current month out.

Cohort tables are a common case study in analyst interviews. The data analyst Excel test guide lists the related skills.

Practise it#

Try the exercise on this page to count one cell of a cohort table with COUNTIFS, EOMONTH and EDATE. Then practise COUNTIFS and SUMIFS in a live spreadsheet, practise EDATE and EOMONTH for month-based dates, and practise Show Values As in the pivot tables 2 lesson. Each lesson checks your work instantly.