How to Use Copilot in Excel for Cohort Retention Analysis

Official documentation checked October 14, 2026.

Copilot can help you build a cohort retention analysis in Excel, but the result is only useful if the cohort, activity, and denominator are defined correctly. Counting every event as another retained user can inflate retention beyond 100%.

The workflow below measures calendar-month user retention: unique acquired users who perform the qualifying activity in a given month, divided by all users acquired in their cohort. Work in a copy of the source data and check a small example before applying Copilot’s changes to a larger workbook.

Understanding Cohort Retention Analysis and Prerequisites

Keep two datasets: Users, with one row per UserID and its AcquisitionDate, and UserEvents, with UserID, AcquisitionDate, and ActivityDate for each qualifying event. The acquisition date must be consistent across that user’s events. Users with no later activity must remain in Users; otherwise your denominator excludes people who did not return.

Define the activity, time zone, and data cutoff. For this example, signup counts as qualifying activity and the extract includes complete months through February 2026. January is a cohort; February is Period 1 for that cohort. March is unobserved and should be blank, not zero.

Use actual Excel dates and clear headers. Tables are useful for repeatable references: select the range, use Insert > Table, and name each table in Table Design. Microsoft’s structured reference guide explains table and column naming. A table is not a guarantee of correct analysis, nor should Copilot be described as universally unable to work with ranges.

Open the Copilot experience available in your Excel account. Microsoft’s getting-started guide covers current access and capabilities. The UI and direct editing available can vary by account and rollout.

Steps to Create a Cohort Retention Table With Copilot

1. Define the task before requesting a table. For example: “Use Users as the unique acquisition roster and UserEvents as activity. Build calendar-month retention, counting each UserID once per cohort and period. Use all acquired users as the denominator. Data is complete through February 2026; leave later periods blank. Explain the formulas and create the analysis on a new sheet.” This is a suggested prompt, not a guaranteed one-command result.
2. Create a true cohort-month date. Add CohortMonth to Users and UserEvents using the AcquisitionDate column. Enter =DATE(YEAR([@AcquisitionDate]),MONTH([@AcquisitionDate]),1) and format the result as mmm-yy. Keeping a date, rather than a text month label, supports chronological sorting. Microsoft’s DATE documentation explains creating dates from year/month/day.
3. Create the activity month and calendar period. In UserEvents, use =DATE(YEAR([@ActivityDate]),MONTH([@ActivityDate]),1) for ActivityMonth. For Period, use =12*(YEAR([@ActivityMonth])-YEAR([@CohortMonth]))+MONTH([@ActivityMonth])-MONTH([@CohortMonth]). Flag negative periods, missing dates, and unknown UserIDs for investigation.
4. Build one user-period record. Create a separate working table named UserMonths containing only UserID, CohortMonth, and Period from valid, in-scope UserEvents. On that copy, use Data > Remove Duplicates with all three columns selected. Keep the original events. Microsoft’s duplicate-removal guide explains why a copy is important: the operation deletes duplicate rows.
5. Calculate the counts. On the report sheet put the cohort-month date in H2, CohortSize in I2, and period numbers 0, 1, 2 in J1, K1, L1. Use =COUNTIFS(Users[CohortMonth],$H2) in I2. In J2 use =COUNTIFS(UserMonths[CohortMonth],$H2,UserMonths[Period],J$1), then copy across and down for observed periods. COUNTIFS counts matching rows; uniqueness is supplied by UserMonths.
6. Calculate and chart retention. In a separate percentage matrix, divide each observed count by its cohort size; for the count in J2, use =IF($I2=0,"",J2/$I2). Format percentages and keep unobserved future periods blank. Ask Copilot to chart the verified percentage matrix with period on the horizontal axis and one line per cohort. If it cannot create the chart in your experience, select the matrix and use Insert > Line chart yourself.

Common Mistakes and Limitations With Cohort Analysis

Calendar months versus elapsed months: a January 31 signup active on February 1 belongs in Period 1 here. Microsoft’s DATEDIF reference defines "M" as complete elapsed months, which is a different metric. Do not use the original raw-date DATEDIF formula for this calendar-month analysis.

Check a small fixture: A and B join in January; C joins in February. A has January 31 and two February events, B only a January event, and C two February events. Users contains A, B, and C once each. UserMonths collapses the repeated same-month activity.

Period 0 is 100% in this fixture because signup qualifies as activity. If your activity definition is later purchase or engagement, Period 0 need not be 100%. Do not change the denominator to force the result.

A PivotTable can also count UserMonths after deduplication. % of Row Total answers a different question: it divides by the row’s period-count total, not the acquired cohort size. Retention above 100% suggests duplicate user-period rows, inconsistent acquisition assignments, or the wrong denominator.

Copilot-Assisted and Manual Methods Compared

Copilot can propose helpers, calculations, and a chart; you remain responsible for the definition and verification. Microsoft’s Excel tips recommend naming the relevant columns/ranges and reviewing changes. The manual formulas above give you a checkable fallback without depending on a fixed generated sheet or dropdown.

Validate duplicate events, inactive users, month-end boundaries, and future periods before sharing the chart. Save the cohort definition, extract cutoff, source tables, and checked formulas alongside the report so another person can reproduce the result.