Excel for Product Managers: Metrics, Funnels and Cohorts

Excel for product managers comes down to a few repeatable jobs: tracking how a metric changes, measuring a funnel, following cohorts over time and splitting revenue by plan. You don't need macros or advanced modelling for any of them. This guide is for product managers and PM candidates, and it shows each task with small example data and the exact formulas, so you can rebuild them on your own exports or in a case-study interview.

Which Excel skills do product managers use?#

Product managers mostly use Excel to answer quick questions from exported data. A dashboard shows the usual numbers; Excel is where you check, slice and explain them.

These overlap a lot with analyst work. The guides to Excel skills for business analysts and Excel skills for marketing jobs cover the neighbouring skills.

Week-over-week change in a metric#

Percent change shows how much a metric moved compared with its starting value. The formula is (new − old) ÷ old.

If weekly active users were 1,240 last week (B2) and 1,364 this week (C2):

=(C2-B2)/B2

The result is 0.1, which shows as 10% in the percentage format (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). The brackets matter: without them, Excel divides first and gives the wrong answer.

When the metric is already a percentage, such as a conversion rate going from 20% to 25%, say "up 5 percentage points" for the difference. The percent change would be 25%, which is easy to misread. The percent change formula guide covers decreases, zero starting values and other edge cases.

Funnel conversion with COUNTIFS#

A funnel counts how many users reach each step of a journey, then divides each step by the one before it. Say a user table has Yes/No columns for each step, and you count them with =COUNTIF(C2:C2001,"Yes") and so on. With example counts like these:

Step conversion is each step ÷ the step above (480 ÷ 2,000 = 24%). Overall conversion is each step ÷ the first step (60 ÷ 2,000 = 3%). The biggest drop shows where to look first.

To split the funnel by a segment, add a condition with COUNTIFS. For the activation rate of users who signed up in week W2:

=COUNTIFS(B2:B8,"W2",C2:C8,"Yes")/COUNTIF(B2:B8,"W2")

On the exercise data, 3 of the 4 W2 users activated, so this returns 0.75 (75%). The COUNTIFS function guide explains the range and criteria pairs, and COUNTIFS and SUMIFS with multiple conditions has more examples.

Cohort retention table with COUNTIFS#

A cohort is a group of users who started in the same period, such as everyone who signed up in January. A retention table shows what share of each cohort is still active 1, 2, 3 months later.

Start with an activity list that has one row per user per active month, with these columns: User (A), Signup month (B), Active month (C) and Month number (D). The month number is the months since signup:

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

Then build a grid with signup months down column G (from G3) and month numbers 1, 2, 3 across row 2 (from H2). In H3:

=COUNTIFS($B$2:$B$5000,$G3,$D$2:$D$5000,H$2)/$F3

Here F3 holds the cohort size. The mixed references do the work: $G3 keeps column G as you fill right, and H$2 keeps row 2 as you fill down. Fill the formula across and down, and every cell shows the share of that cohort active in that month.

One warning: COUNTIFS counts rows, not people. If a user can appear twice in the same month, remove duplicates first, or the retention rate can pass 100%.

Revenue by plan with SUMIFS and XLOOKUP#

SUMIFS adds the numbers that meet every condition you give it. With a payments list where C holds the plan, D the month and E the amount:

=SUMIFS(E2:E500,C2:C500,"Pro",D2:D500,"Mar")

This returns total Pro revenue for March. The SUMIFS function guide lists every argument.

Exports often split details across tables: events in one, accounts in another. XLOOKUP brings a detail across so you can summarise it. To pull each user's plan from an Accounts sheet:

=XLOOKUP(A2,Accounts!$A$2:$A$500,Accounts!$C$2:$C$500,"Unknown")

The fourth argument shows "Unknown" instead of #N/A when a user is missing. XLOOKUP needs Excel 2021 or Microsoft 365; the XLOOKUP function guide also shows the VLOOKUP version for older files.

Pivot tables for quick slicing#

A pivot table summarises a long list in seconds, without formulas. It is the fastest way to answer "what does this look like by plan, by country or by week?"

Select your data, choose Insert > PivotTable, then drag Plan to Rows, Week to Columns and User to Values (set to Count). To see shares instead of counts, right-click a value and choose Show Values As > % of Row Total. When the question becomes a recurring report, switch to COUNTIFS and SUMIFS so the numbers update in place. The beginner's guide to pivot tables walks through a first one.

Common metric mistakes#

Most wrong product numbers come from the setup, not the formula. Check these before you share a result:

1. Counting events instead of users. Ten clicks by one user is one user. Remove duplicates first.
2. The wrong denominator. Activation rate is activated ÷ signed up, not activated ÷ all visitors.
3. Comparing incomplete periods. This week's partial data against last week's full week always looks like a drop.
4. Mixing percent change and percentage points. 20% to 25% is +5 points, or +25% relative.
5. Ranges that miss new rows. Format the data as a table (Insert > Table) so formulas include new exports.

Practise it#

Product interviews often include a short data task, and these formulas are what it tests. Try the activation-rate exercise on this page, then practise percent change in a live spreadsheet, build segment totals in the COUNTIFS and SUMIFS lesson, and slice data in the pivot tables lesson. Each answer is checked instantly. For a timed run, try the data analyst Excel mock test.