Build a cohort retention table in Excel with Copilot Agent Mode

Posted

Oct 5, 2025 by Josef Waples (DataCamp)

·

Last checked

Oct 16, 2026

·

Method:

How we verify

Evidence: Article or interviewA newsletter, journalist or interview account of what happened.

Outcome

Given a long table of customers by period, Excel's Copilot Agent Mode built a color-coded cohort retention table that DataCamp's reviewer found accurate to the data; it also added a useless chart he had to delete.

Agent

Microsoft Copilot Agent Mode (Excel)

The source didn't share its exact prompt, so we wrote this from what it describes.

Before you start, ask me 1–3 short questions about the details below and wait for my answers. Once I've answered, confirm the plan in one line and run the whole task start to finish.

Goal: Turn my customer activity table into a cohort retention table.

Quick-check questions: Which columns are customer ID, signup date and activity date, and monthly or weekly cohorts?

What I'll give you:
- The table in Excel
- Cohort period (month or week)
- Which sheet holds the data

Rules:
- Group users by signup period, count active users in each later period and divide by period 0.
- Put the result on a new sheet; don't overwrite my data.
- Show the formulas so I can check them.
- Skip charts unless I ask.
- If a customer appears in more than one cohort, use their first signup date.

Output: A triangular retention table with percentages and a short note on the method.

If my answers leave a gap, make a sensible choice, say what you assumed, and keep going.

You'll know it worked when: each row starts at 100% and the counts match the raw data.

Before you start, ask me 1–3 short questions about the details below and wait for my answers. Once I've answered, confirm the plan in one line and run the whole task start to finish.

Goal: Turn my customer activity table into an accurate cohort retention table.

Questions to ask me first:
- Which columns hold customer ID, signup date and activity date?
- Monthly or weekly cohorts?
- Active users or revenue?

Inputs (fill in or confirm):
- The Excel table and sheet name
- Columns for customer ID, signup date, activity date
- Cohort period (month or week)
- Whether to count revenue or active users

Rules:
- Group customers by signup period; count distinct active customers in each later period; divide by period-0 size.
- Put results on a new sheet; never overwrite the source data.
- Show the formulas or pivot used so I can audit them.
- Format as a triangle with percentages and conditional colouring.
- Skip charts unless I ask.

If you get stuck: If dates are stored as text, convert in a helper column and tell me. If a login, CAPTCHA or two-step code appears, stop and ask me to handle it; never guess or store passwords.

Verify before you finish: Check every cohort's period 0 is 100% and that counts in period 0 equal distinct signups.

Output: Retention sheet, method note and a short read of the main trend.

Done when: the table reconciles with the raw data and each row starts at 100%.

Source

https://www.datacamp.com/blog/agent-mode-in-excel