Customer Retention Rate Formula: Calculate It in Sheets or Excel

Your customer retention rate is the share of the customers you had at the start of a period who are still customers at the end of it. The customer retention rate formula is ((E - N) / S) x 100, where S is the number of customers at the start, E the number at the end, and N the new customers added during the period ^[1]. Subtracting N keeps new sales from hiding the customers you lost. A free spreadsheet with one row per customer and two dates per row calculates it in minutes.

The customer retention rate formula, step by step

The formula only works if you decide three things first. Pick the period: a month, a quarter or a year. Then define what still a customer means for your business. For a retainer or a subscription, it means the contract has not ended. For a shop or a service business without contracts, it means the customer bought again within their normal buying cycle. Finally, count the customers who ended the period in place, not the ones who merely bought once.

1. Count S. How many customers were active on the first day of the period.
2. Count E. How many customers were active on the last day.
3. Count N. How many customers joined during the period.
4. Calculate. Subtract N from E, divide by S, multiply by 100.

The result answers one question: of the people who were already with you, how many stayed? Churn is the other side of the same number. If 85% stayed, 15% left. Signing four new clients while losing two old ones is not a retention rate of 120%. It is a leak covered by sales, and the formula is built to show it.

How to calculate customer retention rate in Google Sheets

Everything here uses COUNTIFS, a built-in function that counts rows meeting several conditions at once. Google's syntax is COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]), and its examples join an operator to a date with an ampersand, as in "<"&DATE(1969,7,20) ^[2]. That is the whole trick.

1. Build the customer list. On a tab called Customers, put the name in column A, the start date in column B and the end date in column C. Headers go in row 1.
2. Fill the end date honestly. For a lost customer, enter the date they cancelled or stopped buying. For an active customer, enter a far-future date such as 12/31/2099, so every formula below can treat the column as dates.
3. Set the period. In F1, type the first day of the period. In F2, type the last day.
4. Count S. In F4, enter =COUNTIFS(B:B,"<"&F1,C:C,">="&F1). That counts customers who started before the period and had not left when it began.
5. Count E and N. In F5, enter =COUNTIFS(B:B,"<="&F2,C:C,">"&F2) for customers active on the last day. In F6, enter =COUNTIFS(B:B,">="&F1,B:B,"<="&F2) for customers who started inside the period.
6. Calculate the rate. In F7, enter =(F5-F6)/F4 and format the cell as a percentage.

Add a direct check in F8: =COUNTIFS(B:B,"<"&F1,C:C,">"&F2)/F4. It counts the starting customers who are still active at the end, one by one. When F7 and F8 disagree, a new customer also left during the same period, and F8 is the true figure.

The same customer retention rate in Excel

Excel needs no changes. COUNTIFS has the same syntax there, and Microsoft's own examples combine conditions on a number column and a date column in one formula ^[3]. Paste the formulas from the Sheets steps, and the F8 check, into the same cells and they return the same counts.

Two rules from that page matter here. Every additional range must have the same number of rows and columns as the first, so keep B:B and C:C as whole columns, or give both the same row limits. And a criterion that points to an empty cell is treated as 0, so a blank F1 or F2 quietly returns nonsense. Fill both period dates before you trust the result.

To watch the trend, add a small monthly table. Put each month's first and last day in two columns, copy the formulas across each row with the period cells swapped for that row's dates, and you get one retention rate per month. A falling line tells you more than any single month.

A worked example: 40 customers in one year

Here is an illustration with round, invented numbers. A bookkeeping firm starts the year with 40 active clients. During the year it signs 12 new clients and loses 6 of the original 40. It ends the year with 46 clients.

Now change one fact. Say the firm actually signed 13 new clients and one of them left in November. E is still 46, but N becomes 13. The formula now gives (46 - 13) / 40 = 82.5%. Yet 34 of the original 40 are still there, so the real rate is 85%. The direct count in F8 avoids this error, which is why the sheet keeps both.

Look at the size of the gap, too. Losing 6 clients a year means the firm must sign 6 new ones just to stand still. Growth only starts with the seventh.

Mistakes that skew the number

Most wrong retention figures come from the inputs, not from the math.

• A window shorter than the buying cycle. A customer who orders every quarter looks lost in a monthly count. Match the period to how often people normally buy.
• Deleting lost customers. A deleted row takes its start date with it, and S shrinks after the fact. Keep every customer and fill the end date instead.
• Stale end dates. If nobody records a cancellation, the customer stays active forever and the rate looks better than it is.
• Counting accounts and revenue together. Customer retention counts people. A client who downgrades still counts as retained, so track revenue lost separately if downgrades are common.
• Comparing different periods. A quarterly rate is not comparable to a monthly one. Compare like with like.

Read the result next to the list of who left. The rate tells you how much leaked. The end-date rows tell you who, and the reason column you add next to them tells you why. That second part is where the strategy starts, and our guide to customer retention strategies covers what to do with it.

Where the spreadsheet stops

The formulas never break. The inputs do, and they break in predictable places as the customer base grows.

• Nobody updates the end dates. Sales, invoices and cancellations live in other systems. Each one has to be copied in by hand, so the sheet drifts from reality within weeks.
• It reports, it never acts. The sheet tells you that 6 clients left last year. It cannot remind anyone to call a client who has gone quiet before they leave.
• There is no history per customer. You see two dates, not the emails, calls and complaints that came before the cancellation.
• Shared editing collides. When two people update the list, rows get overwritten and the counts shift without anyone noticing.

The practical threshold is concrete. Keep the sheet while one person owns the list and can update it weekly in a few minutes. Move when you have more active customers than you can review in one sitting, when a second person starts editing, or when you notice a client has left only after the fact. At that point you need a tool that keeps the history and acts on it.

To be clear, none of the tools below calculates the retention rate for you; keep the sheet for that. What they add is the follow-up that moves the number. Keap runs prebuilt automations for review, referral and post-purchase follow-up on every plan. Zoho CRM can score each customer's churn likelihood on its Professional edition.

For how these tools compare across renewals, support and outreach, see our guide to customer retention tools.

The bottom line

The customer retention rate formula is ((E - N) / S) x 100, and a spreadsheet handles it well: one row per customer, a start date, an end date, and COUNTIFS for S, E and N. Add a direct count of starting customers still active, because it catches new customers who also left. Define still a customer to match your buying cycle and never delete lost rows. The sheet measures the leak. Once updating it falls behind or clients leave before anyone notices, add a CRM that follows up while you keep measuring.