Are You Losing Customers?
Track Gross Retention Rate in Excel

Gross Customer Retention Rate %

1. Create a Unique List of Customers

• Start by pulling a unique list from the Customer ID column.
• Use the UNIQUE function to remove duplicates.
• Sort the list so the customer IDs appear in numerical order.
• This becomes the base list you will use to track whether each customer stayed active from one year to the next.

2. Create the Service Year Headers

• Pull the distinct years from the Service Year column.
• Use the UNIQUE function again to generate the list of years.
• Use the TRANSPOSE function to place those years across the top of the table instead of down a column.
• This gives you a year-by-year structure for tracking customer activity.

3. Build the Customer Activity Matrix

• Create a grid that shows whether each customer was active in each service year.
• Use COUNTIFS to check three things at once:
  • the customer ID
  • the service year
  • whether the status is marked as active
• Add the correct dollar sign locking so the formula behaves properly when copied across and down.
• Copy the formula across all years and then down the entire customer list.

At this point, each row shows a customer’s activity history by year.

4. Add the Prior Customers Row

• Create a summary row for Prior Customers.
• Use SUM to total the active customers in each year.
• The first year serves as the starting point, but it does not yet have a prior year to compare against.
• This row gives you the number of customers that were active in each year before calculating retention.

5. Count the Customers Who Were Retained

• For each year after the first one, count how many customers were active in both:
  • the previous year
  • the current year
• Use COUNTIFS to find rows where both columns contain a value indicating the customer was active.
• This tells you how many customers were retained from one year to the next.

6. Count the Customers Who Were Lost

• Next, count how many customers were active in the prior year but not active in the current year.
• Use COUNTIFS again, but this time check for customers with activity in the previous year and no activity in the current year.
• This gives you the number of customers lost between the two years.

7. Calculate the Retention Rate Row

• Create a row for Retention Rate.
• Use the retained customer count and compare it to the prior customer total.
• Format the results as percentages.
• Copy the calculation across the remaining years so each year shows its own gross customer retention rate.

8. Add a Target Retention Rate

• Create a separate row for Target Rate.
• Enter your target once, such as 65%.
• Link the other target cells to that first target cell so that if the target changes, the full row updates automatically.
• This makes it easy to compare actual retention performance against a benchmark.

9. Insert the Retention Chart

• Highlight the full summary range, including:
  • Prior Customers
  • Lost Customers
  • Retention Rate
  • Target Rate
• Go to Insert and choose Recommended Charts.
• Change the chart type to a Combo Chart.

10. Format the Combo Chart Correctly

• Set Prior Customers and Lost Customers as stacked columns.
• Set Retention Rate as a line.
• Set Target Rate as a second line.
• Place the percentage-based series on the secondary axis so the retention lines are visible and scaled correctly.

This structure lets you see both volume and retention trend in one chart.

11. Clean Up the Chart Labels and Series Names

• Review the series names because Excel may pull incorrect labels from nearby cells.
• Use Select Data if needed to rename the series properly.
• Remove any accidental labels that came in from the source range.
• This makes the chart easier to understand at a glance.

12. Style the Retention and Target Lines

• Change the target line to a thinner or dotted line so it acts as a benchmark.
• Adjust the retention line color and thickness so it stands out clearly.
• Add data labels if you want to display retention percentages directly on the chart.
• If needed, format the labels so they are easier to read against the chart background.

13. Update the Chart Title

• Rename the chart so it clearly reflects the KPI being displayed.
• Use a title such as Gross Customer Retention Rate %.
• This final step makes the chart presentation-ready and easier to interpret in a dashboard or report.

14. Review the Year-to-Year Retention Pattern

• Once the chart is complete, you can quickly see:
  • how many customers you started with each year
  • how many were lost
  • how retention changed over time
  • whether performance stayed above or below your target
• This final layout turns the raw customer activity table into a clear retention view for customer success analysis.