How to Do Cohort Analysis in Excel

Building a cohort analysis in Excel is one of the most powerful ways to truly understand customer behavior, moving beyond simple metrics that often hide the real story. Instead of looking at an average churn rate, you can see exactly how different groups of customers stick around over time. This tutorial will walk you through, step-by-step, how to transform raw sales or usage data into a clear, actionable cohort chart using Excel pivot tables.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly Is Cohort Analysis?

Before jumping into Excel, let's quickly define what we're building. A cohort is a group of people who share a common characteristic over a certain period. In business analytics, this characteristic is usually tied to an acquisition event.

Common examples include:

• Acquisition Cohorts: Users who signed up for your app in the same month (e.g., the "January 2024 cohort").
• Behavioral Cohorts: Customers who made their first purchase during a specific event, like a Black Friday sale.

Cohort analysis tracks these groups over time to see how their behavior evolves. The most common use case is customer retention analysis, where you measure what percentage of a cohort comes back to make another purchase, use your app, or remain subscribed in the weeks or months after they first joined.

Why Aggregate Metrics Aren't Enough

You might be tracking metrics like overall Lifetime Value (LTV), Customer Acquisition Cost (CAC), and churn rate. While important, these blended numbers can be misleading. They mix together the behavior of brand-new users with that of customers who have been with you for years, obscuring critical trends.

Imagine your overall monthly churn rate is a steady 5%. On the surface, it seems stable. But a cohort analysis might reveal a different story:

• Your cohorts from last year are churning at 10% per month.
• Your cohorts from the last three months are only churning at 2% per month.

The blended 5% average completely hides the fact that something you've recently done — perhaps a better onboarding process or a new product feature — is making your new customers dramatically stickier. Without a cohort chart, you'd never see this positive momentum and wouldn't know to double down on what’s working.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Gather and Prepare Your Data

First, you need the right raw data. The goal is to get a simple table of transactions that includes at least a unique customer identifier and the date of each transaction. Most e-commerce platforms (like Shopify) or CRMs (like Salesforce) let you export this data as a CSV file.

Your raw data might look something like this. Let's imagine this is an export from an orders database. For this exercise to work best, format your data as an official Excel Table by selecting any cell and pressing Ctrl + T (or Cmd + T on Mac). This makes formulas easier to manage and auto-fill.

Example Data (Orders Table):

In this table, the key columns we'll use are CustomerID and OrderDate.

Step 2: Calculate the Cohort Month for Each Customer

Our first task is to assign each customer to a cohort. We’ll define a cohort as the month of a customer's first purchase. To do this, we need to find the earliest OrderDate associated with each unique CustomerID.

Add a new column to your table called FirstPurchaseDate.

We can find this using the MINIFS function, which is perfect for this task. It finds the minimum value in a range based on a specific criterion.

Formula for First Purchase Date

In the first cell of your new FirstPurchaseDate column, enter this formula (assuming your table is named Table1):

=MINIFS([OrderDate], [CustomerID], [@CustomerID])

Here's what it does:

• MINIFS([OrderDate], ...), [CustomerID], [@CustomerID]): Find the minimum OrderDate for the current CustomerID.

Now, we want to group cohorts by month, so we'll add another column called CohortMonth. This column will take the FirstPurchaseDate and round it down to the first day of that month.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Formula for Cohort Month

In a new column named CohortMonth, use this formula:

=EOMONTH([@FirstPurchaseDate],-1)+1

• EOMONTH([@FirstPurchaseDate],-1): Finds the last day of the previous month.
• +1: Adds one day to get the first day of the month.

After formatting this column as a date (e.g., "MMM-YYYY"), your table now looks like:

Notice how Customer 101's cohort is always Jan-2024, regardless of subsequent purchases.

Step 3: Calculate the "Order Period" Number

Next, we need to determine how much time has passed between a customer's cohort month and each of their individual orders. We'll label these as "Month 0" (the month they joined), "Month 1" (the next month), and so on.

Add a new column called MonthNumber to calculate this.

Formula for Month Number

=(YEAR([@OrderDate]) - YEAR([@CohortMonth])) * 12 + MONTH([@OrderDate]) - MONTH([@CohortMonth])

This formula counts the full months elapsed between the cohort month and the order date.

• (YEAR([@OrderDate]) - YEAR([@CohortMonth])) * 12: Difference in full years converted to months.
• + MONTH([@OrderDate]) - MONTH([@CohortMonth]): Adds the difference in months within the years.

Your table is now fully prepared for the pivot table!

Step 4: Build the Pivot Table

This is where everything comes together. The pivot table will crunch all our data and structure it into a cohort matrix.

1. Click anywhere inside your data table.
2. Go to the Insert tab on the Ribbon and click PivotTable. Excel will automatically select your table and create a new worksheet.
3. In the PivotTable Fields pane on the right, drag and drop the fields like this:
4. By default, the Values field is probably set to "Count of CustomerID." We need to change this. Click on "Count of CustomerID" in the Values area, go to Value Field Settings, and change the calculation to Distinct Count. This counts the number of unique customers from each cohort active in each month period.

You should now have a pivot table similar to:

This is your cohort analysis! In January, 150 unique customers made their first purchase. In Month 1 (February), 85 of those same customers came back and made another purchase.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 5: Convert to a Percentage-Based Retention Table

The absolute numbers are good, but percentages are much easier to compare across cohorts of different sizes. We need to calculate each month's active user count as a percentage of the initial cohort size (the total in Month 0).

While you can sometimes do this within a pivot table using calculated fields, it's often simpler and more flexible to create a separate little reporting table next to it.

1. Copy and paste your raw pivot table as values to a new area on the sheet. This decouples it from the pivot table for custom formulas.
2. In the first cell (for Jan-2024, Month 0), the value will always be 100%.
3. For the next cell (Jan-2024, Month 1), create a formula that divides the value for Month 1 by the value for the cohort's Month 0. For example, if your Month 0 count for Jan-2024 is in cell B5 and your Month 1 count is in C5, the formula would be =C5/$B5. Make sure to lock the column $ so that as you drag the formula across, it always references the Month 0 value in column B.
4. Drag this formula across all the columns and down for all rows.
5. Format these cells as percentages.

Your finished table will now show retention percentages:

Step 6: Add Conditional Formatting for a Heatmap

The last step is to make your table instantly readable with color. A heatmap is perfect for this.

1. Select all the percentage cells in your retention table.
2. Go to the Home tab → Conditional Formatting → Color Scales.
3. Choose a scale, like the "Green - Yellow - Red" option. By default, it highlights higher numbers green and lower numbers red, indicating higher retention.

You now have a visually compelling cohort analysis chart right in Excel, ready to be analyzed and shared.

Final Thoughts

Creating a cohort analysis in Excel is a fantastic skill that unlocks deep insight into your customer retention and business health, showing you exactly how user behavior changes over time. While the process requires careful data preparation and pivot table management, the resulting heatmap gives you an at-a-glance view of performance that blended metrics could never provide.

We know that manually exporting CSVs, managing complex formulas, and repeatedly refreshing pivot tables is exactly the kind of tedious reporting work that can consume an entire afternoon. We created Graphed to eliminate this friction entirely. Instead of following these steps, you connect your data sources like Shopify, Google Analytics, or HubSpot once. Then, you can simply ask in plain language, "Show me a customer retention cohort analysis for the last year," and Graphed instantly builds a live, interactive dashboard for you that updates automatically — no formulas required.