How to Calculate Churn Rate in Excel, Monthly and Yearly

Churn rate tells you what share of your customers left during a period, and in Excel it is one division: customers lost ÷ customers at the start. This guide shows how to work out the churn rate in Excel from a monthly summary, how to count the two numbers from a raw customer list with COUNTIFS, and how to turn monthly churn into a yearly figure without overstating it. It suits anyone reporting on subscriptions, memberships or customer accounts.

What is churn rate?#

Churn rate is the percentage of customers at the start of a period who left by the end of it. Churn means customers cancelling or not renewing. The standard formula is:

churn rate = customers lost during the period / customers at the start of the period

If you start September with 400 customers and 12 cancel during the month, the churn rate is 12 ÷ 400 = 3%.

Customers who join during the month are left out of this calculation, even if they also cancel. That keeps the rate fair: you only measure how many of the people you already had were lost. Businesses define churn in slightly different ways, so write your rule down and use it every month. The numbers in this guide are examples only.

The same idea measures staff leaving a company, where it is called turnover. The guide to HR analytics in Excel covers that version.

How to calculate monthly churn rate in Excel#

If you already have the counts, put them in a monthly table and divide. In D2:

=C2/B2

Fill D2 down (Windows: Ctrl+D; Mac: ⌘D) and format column D as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). August is 8 ÷ 410 = 1.95%, which displays as 2.0% with one decimal. The guide to the percentage formula explains why you divide first and format second.

Retention rate is the other side of the same number. In E2, =1-D2 shows that July kept 97% of its starting customers.

Count customers at the start and customers lost with COUNTIFS#

When you only have a customer list, COUNTIFS builds both numbers from the start and cancel dates. A blank cancel date means the customer is still active.

Put the first day of the month in G1 (9/1/2026) and the first day of the next month in G2 (10/1/2026).

Customers at the start are those who started before 1 September and hadn't cancelled by then:

=COUNTIFS(B2:B9,"<"&G1,C2:C9,"")+COUNTIFS(B2:B9,"<"&G1,C2:C9,">="&G1)

The first COUNTIFS counts customers with no cancel date (3); the criteria "" matches blank cells. The second counts those who cancelled on or after 1 September (2). The total is 5. C-104 left in August, and C-106 and C-108 joined in September, so none of them count.

Customers lost are starting customers who cancelled during September:

=COUNTIFS(B2:B9,"<"&G1,C2:C9,">="&G1,C2:C9,"<"&G2)

The result is 2 (C-102 and C-105). C-108 cancelled in September too, but joined after the start, so it is left out. The churn rate on this tiny list is 2 ÷ 5 = 40%.

The "<"&G1 pattern joins an operator to a date cell; the guide to COUNTIFS with a date range explains it step by step. The COUNTIFS function guide has the full syntax.

Churn rate by plan or segment#

To compare churn between plans, regions or sales channels, add the segment as a condition. With a Plan column in B and a status column in C:

=COUNTIFS(B2:B8,"Pro",C2:C8,"Churned")/COUNTIF(B2:B8,"Pro")

If 1 of 4 Pro customers churned, the result is 0.25, or 25%. The exercise on this page uses this exact formula. Use cell references instead of typed plan names, and the same formula fills down a whole summary table. The guide to COUNTIFS and SUMIFS with multiple conditions covers more patterns.

How to calculate yearly churn rate#

There are two correct ways to get a yearly figure.

Directly, for the whole year: count customers at the start of the year and how many of them were lost by the end. Divide, exactly as for a month.

From a steady monthly rate: compound it. With the monthly churn rate in B2:

=1-(1-B2)^12

At 3% a month, that gives 30.6% a year. The tempting shortcut, 3% × 12 = 36%, overstates it. Each month you lose 3% of a smaller group, because last month's leavers are already gone. The ^ operator raises a number to a power; =1-POWER(1-B2,12) gives the same answer.

Report the change in churn correctly#

Churn going from 3.0% to 4.0% is a rise of 1 percentage point, but a 33% relative increase (1 ÷ 3). Both are correct; they answer different questions. Write "percentage points" for the gap between two rates, and use the percent change formula only when you mean the relative change:

=(D4-D2)/D2

With July in D2 and September in D4, that returns 0.333. The guide to the percent change formula covers the formula and its edge cases, such as a starting value of zero.

Common churn rate mistakes#

These errors make churn look better or worse than it is:

1. Dividing by customers at the end. The end count already excludes leavers, so the rate comes out too high.
2. Counting new customers who left in the same month. Keep them out of both counts, or apply a different rule consistently.
3. Multiplying monthly churn by 12. Use =1-(1-rate)^12 instead.
4. Text dates. Imported dates stored as text won't match "<"&G1. Convert them before counting.
5. Mixing customer churn and revenue churn. Losing one large customer may be a small customer churn rate but a large share of revenue. If revenue matters, divide revenue lost by revenue at the start as a separate measure.

Practise it#

Churn reports are built from counts and percentages, both of which appear in data and business analyst tests. Practise COUNTIFS and SUMIFS in a live spreadsheet to count customers by date and plan, then practise percentages and percent change to turn the counts into rates. Each lesson checks your formula instantly. Try the exercise on this page first.