How to How to Create Cohort Analysis in Excel in Excel - Practical Guide

Learn to build cohort analysis tables that track user behavior across time periods, essential for understanding retention, engagement, and lifecycle metrics. This advanced technique reveals how different customer groups perform over their lifetime, enabling data-driven retention strategies.

Why This Matters

Cohort analysis identifies retention trends and user behavior patterns critical for SaaS, e-commerce, and mobile app strategies. It separates new vs. established users to reveal true product performance beyond aggregate metrics.

Prerequisites

•Intermediate Excel skills: VLOOKUP, INDEX/MATCH formulas
•Understanding of pivot tables and date functions
•Clean transaction data with user IDs and dates

Step-by-Step Instructions

Prepare raw transaction data

Ensure your dataset contains columns for User ID, Transaction Date, and Revenue. Clean duplicate entries and remove null values using Data > Data Tools > Remove Duplicates.

Create cohort assignment column

Add a column 'Cohort Month' using formula =TEXT(MIN(IF($A$2:$A$100=A2,$B$2:$B$100)),"YYYY-MM") as array formula (Ctrl+Shift+Enter) to assign each user to their first purchase month.

Calculate cohort age in periods

Create 'Cohort Age' column with formula =DATEDIF(DATE(YEAR(D2),MONTH(D2),1),DATE(YEAR(B2),MONTH(B2),1),"M") to count months between cohort start and transaction date.

Build pivot table for cohort matrix

Insert > Pivot Table, place Cohort Month in Rows, Cohort Age in Columns, and sum Revenue as Values to create your cohort retention/revenue matrix.

Format and analyze retention patterns

Apply conditional formatting (Home > Conditional Formatting > Color Scales) to visualize cohort performance, then analyze diagonal trends to spot retention decay.

Alternative Methods

Using pivot tables with calculated fields

Build the pivot table first, then add calculated fields to compute retention percentages directly within the pivot table without manual formulas.

SQL-based approach with helper columns

Use Power Query (Data > Get & Transform > From Table/Range) to group and aggregate data before importing into Excel for faster processing on large datasets.

Retention cohort with user count instead of revenue

Replace Revenue with COUNTA to track number of active users per cohort month, revealing user retention rather than monetary value.

Tips & Tricks

✓Always use the user's first transaction date as the cohort identifier for consistency and accuracy.
✓Format cohort ages as 0, 1, 2, 3+ months for easier interpretation of retention curves.
✓Use relative references in pivot tables to allow easy date range adjustments for rolling cohort analysis.
✓Highlight the diagonal cells to visually track each cohort's progression through its lifecycle.

Pro Tips

★Create a secondary cohort table showing retention rates (divide each cell by Month 0 cohort size) to normalize across cohorts of different sizes.
★Use INDEX/MATCH with IFERROR to handle missing cohort-age combinations, avoiding misleading zero values in your analysis.
★Automate cohort assignment with helper columns using MINIFS function to identify first purchase date dynamically.

Troubleshooting

Pivot table shows #VALUE! errors in cohort age column

Ensure all dates are formatted as Date type, not text. Use Data > Text to Columns and select Date format to convert.

Cohort matrix has many blank cells with no data

This is normal—future cohorts haven't aged sufficiently. Use conditional formatting or pivot table display settings to hide empty cells for clarity.

Retention percentages exceed 100% in later months

Check for duplicate user IDs or multiple transactions per user on the same day; deduplicate data before recalculating cohort sizes.

Pivot table takes too long to refresh with large datasets

Use Power Query to pre-aggregate data by cohort and age before creating the pivot table, reducing source data size significantly.

Related Excel Formulas

DATEDIF MINIFS TEXT INDEX/MATCH VLOOKUP DATE YEAR MONTH

Frequently Asked Questions

What's the difference between a retention cohort and a revenue cohort?

A retention cohort tracks the percentage of users returning each month (user count), while a revenue cohort tracks total spending by cohort over time. Both reveal patterns but answer different business questions about engagement vs. monetization.

Can I create a cohort analysis with weekly or daily data instead of monthly?

Yes, simply replace MONTH logic with WEEK or DAY functions in your cohort assignment formula. Daily cohorts work best for high-frequency events; weekly is ideal for most SaaS and e-commerce applications.

How do I interpret declining diagonal lines in my cohort matrix?

Declining values from left to right indicate declining retention—typical and expected. Steeper drops suggest poor product-market fit or onboarding issues; flat or increasing diagonals suggest strong engagement and upselling.

Should I include the cohort's Month 0 users who never return?

Yes, include all Month 0 users as the denominator for retention rate calculations; excluding them inflates retention percentages and masks churn problems.

What's the best way to compare multiple cohorts with different sizes?

Create a retention rate table by dividing each cohort's values by its Month 0 size, normalizing all cohorts to 100% at inception for fair comparison.