Customer Churn Analysis in Excel — Free Template + Guide (2026)

Acquiring a customer costs five to ten times more than keeping one — yet most teams measure acquisition obsessively and churn vaguely. This guide fixes that in 40 minutes of plain Excel: define churn precisely, calculate the monthly rate with COUNTIFS, build a cohort retention view, separate revenue churn from logo churn, chart the retention curve, and pinpoint the segments where churn concentrates — then turn those numbers into a retention plan. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Customer Churn Analysis?

Churn analysis is the systematic measurement of customer loss: how many customers (and how much revenue) you lose per period, which kinds of customers leave, when in their lifecycle they leave, and — the payoff — what you can do about it. The headline metric is the churn rate: the percentage of the customer base lost in a period. Its mirror image is the retention rate (100% minus churn), and the two together govern the economics of any subscription, membership, or repeat-purchase business.

Why does a percentage point matter so much? Because churn compounds. A business losing 3% of customers monthly retains 69% after a year; at 7% monthly it keeps just 42%. That gap dwarfs almost anything marketing can achieve at the top of the funnel — and it works in reverse too: cutting churn from 5% to 4% extends average customer lifetime from 20 to 25 months, lifting lifetime value by 25% with no new acquisition spend. Churn is the quiet denominator under every growth number.

A useful churn analysis answers four escalating questions. How much? — the monthly rate and its trend. Which money? — logo churn (customers lost) versus revenue churn (MRR lost), because five tiny accounts and one enterprise account are very different losses. Who and when? — cohort and segment views revealing that, say, customers from one channel churn at triple the rate, mostly in their second month. What now? — targeted interventions whose impact you can verify in the next cohort’s curve.

Everything above is achievable in plain Excel with COUNTIFS, SUMIFS, a line chart, and discipline about definitions — no BI tool or SQL required. The discipline part matters most, which is why this guide’s first step is not a formula at all.

Before You Start

Churn analysis dies from definitional sloppiness more often than from formula errors. Settle three things first.

One row per customer, with dates

The whole analysis runs off a flat table: one row per customer with a start date, a cancel date (blank while active), and the recurring revenue. If your billing system exports transactions instead, collapse them to this shape first — MINIFS for first purchase, MAXIFS for last, per customer ID.

Pick the unit: customers or revenue (you will need both)

Decide up front to track logo churn and revenue churn. They diverge whenever account sizes vary, and each misleads alone: logo churn hides the loss of a whale; revenue churn hides a mass exodus of small accounts that signals product trouble ahead.

Mind the small-numbers problem

With under ~300 customers, monthly churn rates are noisy — one extra cancellation moves the rate visibly. Plan to report a 3-month moving average alongside the raw monthly figure, and resist re-explaining every wiggle.

1.Step 1 — Define Churn for Your Business

Before a single formula, write one sentence: “A customer counts as churned when…” — and get the team to agree to it. The candidates:

Cancellation date — the customer actively cancels. Crisp, but in subscription businesses many customers churn passively (a failed card, a non-renewal) and never “cancel.”

End of paid access — churned when the last paid period lapses without renewal, usually after a grace period (commonly 30 days for failed payments). This is the standard for SaaS and the definition this guide uses.

Inactivity — for non-subscription businesses, churned when no purchase occurs within X days of the last (e.g. 90 days for e-commerce). The window should reflect your natural repurchase cycle: =MEDIAN() of gaps between orders, times two or three, is a defensible starting point.

Then settle three boundary cases that otherwise corrupt the numbers later. Downgrades to free: logo-retained but revenue-churned — the Step 5 split handles them, but only if the data records the downgrade. Pauses: decide whether a paused subscription is churned (and reactivation a “new” customer) or merely dormant; either works if consistent. Same-month join-and-leave: a customer who starts and cancels in the same month is usually excluded from that month’s churn rate (they were never in the starting base) but tracked separately as early churn — it is a real and informative failure mode.

2.Step 2 — Lay Out the Customer Data

Build the master table on a sheet named Customers — one row per customer:

Rules that keep the formulas honest: cancel date stays blank while active (not “N/A”, not 31/12/9999 — blank, so COUNTIFS(C:C,"") counts actives cleanly); dates are real Excel dates, not text (check with =ISNUMBER(B2)); MRR is the current monthly value for actives and the final monthly value for churned customers (what walked out the door). Add any segmentation columns you will want in Step 7 now — plan, channel, company size — while the export is fresh.

Convert the range to an Excel Table (Ctrl+T, name it tblCust). New rows auto-extend every downstream formula, and structured references like tblCust[Cancel date] read far better than column letters in the COUNTIFS to come.

3.Step 3 — Monthly Churn Rate with COUNTIFS

On a new Churn sheet, list month-start dates down column A (2025-01-01, 2025-02-01, … — fill with =EDATE(A2,1)). Three formulas per month do all the work.

Customers at month start — started before the month, and either still active or cancelled on/after the month start. In B2:

The two COUNTIFS handle the two ways to be alive on day one: never cancelled (blank C) or cancelled later. Customers churned during the month — cancel date inside the month. In C2:

Churn rate — losses over the starting base. In D2, formatted as a percentage:

Fill all three down the month list. If 500 customers start March and 25 cancel during it, March churn is 5.0%. Two conventions to note. The denominator is the starting base — customers who join mid-month are not at risk for the full month and would dilute the rate (some teams use the average of start and end counts; fine, but be consistent). And retention rate is simply =1-D2 — worth its own column, since “95% retention” and “5% churn” land differently in different rooms.

Add the smoothing column now too — a 3-month moving average =AVERAGE(D2:D4) from the third month — and chart both: raw rate as faint bars, smoothed rate as the bold line. The trend is the story; the monthly wiggle is mostly noise.

4.Step 4 — Build a Cohort View

The monthly rate blends customers of every age, but churn is rarely age-neutral — most subscription businesses lose customers heavily in the first two or three months, then settle. A cohort view exposes this: group customers by signup month and track each group’s survival over tenure.

Set up a grid: cohort months down column A (as month-start dates), tenure 0–12 across row 1. Each cell asks: of the customers who started in cohort month A, how many were still active N months later? With cohort month in $A2 and tenure number in B$1, the percentage formula is:

It looks long but is only three counts: cohort members still active (blank cancel date), plus cohort members who survived past the tenure month, divided by the cohort’s size. Fill the grid; blank out cells for tenure months that have not happened yet (the formula’s IFERROR plus future-date logic largely handles this). Apply a colour scale (conditional formatting heat map) — green high, red low — and the triangle becomes readable at a glance.

Read it two ways. Across a row: one cohort’s survival curve — where does it stabilise? Down a column: different cohorts at the same age — are newer cohorts retaining better, i.e. is whatever you changed working? The column read is the experiment-evaluation tool that the blended monthly rate can never be, because the monthly rate mixes every vintage together.

For a deeper treatment of the technique, see the dedicated cohort analysis in Excel tutorial.

5.Step 5 — Revenue Churn vs Logo Churn

Counting customers treats a £49 Starter account and a £499 Business account as equal losses. Revenue churn corrects that. Add to the monthly table:

MRR at month start — same conditions as the customer count, but summing column D. In E2:

Churned MRR — the revenue that walked out. In F2:

Gross revenue churn is =F2/E2. But the metric investors actually ask about is net revenue churn, which credits expansion MRR — upgrades and seat additions from customers who stayed. Track expansion in its own column or sheet, then:

Net revenue churn can be negative — expansion outpacing losses — which is the celebrated “net negative churn” of the best SaaS businesses. Its complement, net revenue retention (=1-net churn, e.g. 104%), means the existing base grows with zero new sales. Compare the logo and revenue rates every month: logo churn high while revenue churn is low means small accounts are leaving (often acceptable); the reverse means large accounts are quietly bleeding — an emergency the logo rate hides completely.

6.Step 6 — Chart the Retention Curve

The retention curve plots the share of a cohort still active at each month of tenure — it is one row of the Step 4 grid drawn as a line, or several rows overlaid. Select a cohort row (or average the last few mature cohorts for a smoother line), insert a Line chart, with tenure 0–12 on the X axis and survival percentage on Y, axis fixed 0–100%.

The shape carries the diagnosis:

Cliff then plateau — steep loss in months 1–3, then nearly flat. The product works for those who get going; the problem is onboarding and early activation. The plateau height is your loyal core, and it is the number to grow.

Steady decline — an unflattening slope means even tenured customers keep leaving: a value-delivery or competitive problem that onboarding fixes will not touch.

Renewal-step pattern — drops concentrated at months 12, 24 mean churn is an annual-renewal decision; interventions belong in the pre-renewal window, not spread across the year.

Overlay a simple projection to make the compounding visceral. With a constant monthly rate c, modelled survival at month n is:

with the churn rate in B1 and tenure in column A. Plot it as a dashed series against the actual curve: where the actual sits above the constant-rate model at later tenures (it usually does — churn risk falls with age), your mature base is healthier than the blended rate implies. That gap is also the honest correction to apply when someone computes lifetime value as 1/churn.

7.Step 7 — Identify At-Risk Segments

The overall rate tells you churn exists; segmentation tells you where it lives. Every segment churn rate is the Step 3 formula with one more criterion. By plan, with the plan name in H2:

(Use the full two-part starting-base count from Step 3 for precision; the short form above is the readable skeleton.) Build a small table of segment → churn rate for each dimension you captured in Step 2 — plan, acquisition channel, company size, tenure band — and sort descending. Two findings appear in almost every dataset: a channel effect (customers from discount promotions or one ad channel churning at multiples of organic) and a tenure effect (the first 60–90 days carrying most of the risk).

Then turn the historical pattern into a forward-looking flag on each active customer — a simple risk score summing their risk factors:

Weight each factor roughly by how much it multiplied churn in your segment table, sort the active base by score, and the top decile is your customer-success call list. Crude rule-based scoring like this routinely concentrates the majority of next quarter’s churn in the top-scored slice — you do not need machine learning to start acting on risk (though the segment table is exactly the feature analysis you would feed a model later, e.g. via regression analysis).

8.Step 8 — Use the Data to Reduce Churn

Analysis pays for itself only when it changes behaviour. Close the loop in three moves.

Price the prize

Compute what one churn point is worth so retention work can compete with acquisition for budget. With 500 customers at £160 average MRR, cutting monthly churn from 5% to 4% saves five customers × £160 × their (now longer) remaining lifetime — and lifts average lifetime from 20 to 25 months across the whole base, raising LTV by 25%:

That is the standard LTV formula — churn in the denominator is why small churn changes move it so violently.

Target the concentration, not the average

The Step 7 table says where churn lives; intervene there specifically. First-90-days cliff → onboarding emails, activation checklists, a week-2 check-in call. Promo-channel churn → fix the expectation set by the offer, or stop buying that traffic. Payment-failure churn (often 20–40% of “churn” in subscription businesses) → dunning emails and card-retry logic, the cheapest churn fix that exists. Renewal-step churn → a structured pre-renewal review 60 days out for every at-risk account.

Verify in the cohorts

The blended monthly rate responds slowly and noisily to any fix. The cohort grid is the honest scoreboard: compare the month-1 and month-3 survival of cohorts after the change to those before. If October’s onboarding revamp worked, the November cohort’s row shows it within two months — read down the columns of the Step 4 triangle. No movement in three cohorts means the intervention missed; iterate.

Try It: Live Churn Calculator

Enter a month’s numbers — starting customers, customers lost, new customers won, MRR churned, and expansion MRR — and the calculator returns logo churn, gross and net revenue churn, and projects the 12-month retention curve at the current churn rate, exactly as the formulas in Steps 3, 5, and 6 would.

Advanced Churn Analysis Tips

Separate voluntary from involuntary churn

Tag each cancellation as voluntary (chose to leave) or involuntary (payment failure, company closed). They need different cures — product/value work versus dunning and billing hygiene — and involuntary churn is typically the faster, cheaper win. A reason column plus a pivot table is all it takes.

Reactivations and the resurrection rate

Some churned customers come back. Track reactivations as their own metric rather than netting them silently against churn (which flatters the rate and hides both signals). A win-back campaign’s success is measurable only if resurrection has its own line.

Seasonality in churn

Churn often has a calendar: January budget resets, post-holiday consumer cancellations, fiscal-year-end B2B reviews. Compare each month to the same month last year as well as to last month, and consider a 12-month moving average line to separate trend from season before declaring victory or panic.

From monthly spreadsheet to always-on churn dashboard

The analysis in this guide goes stale the day after you build it. Upload your customer data to DataHub Pro and the churn rates, cohort heat map, and retention curves stay current automatically — one shareable link for the team instead of a monthly export-and-rebuild ritual.

Worked Example: Diagnosing a SaaS Churn Spike

A subscription software firm with 500 customers and £80,000 MRR sees monthly logo churn drift from 3.8% to 5.0% over a quarter. The founder’s first instinct — “the product is losing its edge” — would mean an expensive roadmap swerve. Instead, the team runs this guide.

Step 3 confirms the rise is real, not noise: the 3-month moving average climbs steadily, and at 500 customers a 1.2-point move is roughly six extra cancellations a month — beyond wiggle. Step 5 adds the crucial nuance: gross revenue churn rose only from 4.2% to 4.6%, and net revenue churn, after £2,000 of expansion, sits at 3.1%. The losses are concentrated in small accounts.

Step 7’s segment table finds the culprit in one sort. Churn by plan: Starter 9.1%, Pro 2.9%, Business 1.2%. Churn by channel: a spring promo campaign’s customers churn at 14% monthly versus 3.4% for organic. Cross-tabbing tenure shows the promo cohort dying almost entirely in months 1–2 — and the Step 4 cohort grid makes it visual: the March and April cohort rows (promo-heavy) plunge to 61% survival by month 2, while older cohorts plateau near 85%.

Diagnosis: not a product problem — an acquisition-quality problem. The 60%-off promo attracted deal-seekers with no use case. The fix: the promo is restructured (smaller discount, longer commitment), and a 14-day activation email sequence targets all new Starter signups. Two cohorts later, the column-read of the cohort grid shows month-2 survival for new cohorts back at 82%, and blended churn drifts down toward 4%. Total analysis time: an afternoon in Excel. Roadmap swerve avoided.

Common Errors & Fixes

COUNTIFS returns zero when data clearly exists

The dates are text, not real dates — check =ISNUMBER(B2). Re-parse with =DATEVALUE() or Text to Columns. Also confirm the criteria concatenate the comparison and the cell: ">="&A2, not ">=A2".

Churn rate over 100% or negative

The denominator is wrong — usually counting the month-end base, or the starting-base formula is missing one of its two COUNTIFS parts (still-active plus cancelled-later). Rebuild the Step 3 denominator exactly.

Cohort cells show survival above 100%

Customers are being double-counted across cohorts (a reactivated customer with a new row and the old cancel date intact) or the cohort-membership date criteria overlap month boundaries. Use >= month start and <= EOMONTH consistently, and dedupe customer IDs.

Active customers counted as churned

The cancel-date column contains placeholder text (“N/A”, “-”) or far-future dates instead of blanks. Clear them: COUNTIFS’ "" criterion only matches genuinely empty cells.

The rate changes when you re-export the data

The billing system backfills cancellations (failed payments confirmed days later). Snapshot monthly and report churn with a few days’ lag — or expect the most recent month to revise upward and label it provisional.

Tiny segments produce wild churn rates

Three cancellations in a 20-customer segment is “15% churn” and means almost nothing. Suppress or flag segment rates where the base is under ~50, or aggregate quarterly for small segments.