How To Perform Cohort Analysis in Excel (A Step-By-Step Guide)

What is Cohort Analysis?

Getting your data into Google Sheets

Before anything else, you need transaction-level client data in your sheet. The minimum columns required are: Customer ID (column A), Signup Date, and Purchase Date. Channel, region, and product line are optional but useful if you want to slice the data later by acquisition source or service type.

If you’re pulling this from QuickBooks Online manually, exporting reports, downloading CSVs, and reformatting date columns, you already know how that goes. It takes longer than it should, the headers never match what you expect, and within two months, nobody’s bothering to keep it current.

G-Accon connects Google Sheets directly to your QuickBooks Online account and syncs the data automatically. You connect your QBO account, select the report type, and the data lands in your sheet in the right structure. No exports, no reformatting. When you want fresh data, you click refresh.

Once your raw data is in, it should look something like this:

Before moving forward, check two things. First, make sure your Signup Date and Purchase Date columns are formatted as actual dates, not text that looks like dates. Real dates align right in their cells. Text dates align left and will cause your formulas to fail.

Second, and this one matters more than it sounds- check for customers with multiple different signup dates across their rows. If C001 shows January 10th in one row and February 5th in another, those rows land in different cohorts, which quietly corrupts your retention numbers. Add a helper column, “Clean Signup Date,”  and use:

This enforces one signup date per Customer ID. Every downstream formula should reference this clean column, not the raw one.

How To Perform Cohort Analysis in Excel

Step 1: Group each client by their signup month 

Cohort analysis works at the month level. A client who signed up on January 4th and a client who signed up on January 29th belong to the same cohort; you’re asking the same question about both of them.

In column J, add a header: Cohort Month. In J2, use this formula:

This takes the cleaned signup date and resets it to the first day of that month. Using the Clean Signup Date column, here is what makes the MINIFS cleanup carry through.

Drag the formula down through all your rows.

Every client now has a cohort label. Clients with multiple purchases appear in multiple rows but carry the same Cohort Month, because it’s tied to their signup date, not their purchase date.

Step 2: Calculate how far along each client is

The second column tracks each client’s position in their lifecycle at the time of each transaction. You’re not just asking “did they come back?” but “how many months after joining did they come back?”

In column K, add: Transaction Month. In K2:

Apply the same MMM YYYY custom date format — keep it as a real date, just display it as text. Then, in column L, add: Period Number. In L2:

The IF check prevents negative period numbers from data entry errors. Period 0 = signup month. Period 1 = one month later. Period 2 = two months later.

DATEDIF doesn’t appear in Sheets’ autocomplete suggestions; it’s a legacy function that Google never surfaced in the UI. Type it manually, and it works perfectly.

Step 3: Build the cohort count table

Create a new sheet; call it “Cohort Pivot.” Cohort months run down the rows. Period numbers run across the columns (0, 1, 2, 3…).

Each cell answers: how many unique clients from this cohort were active in this period? A client with three transactions in the same month should count once, not three times. In B2 (January cohort, Period 0):

FILTER pulls the matching Customer IDs, UNIQUE deduplicates them, COUNTA counts what’s left. Write it once in B2, drag across all period columns, and down all cohort rows.