How to Analyze Customer Lifetime Value (LTV) in Excel
for Smarter Retention Decisions

How to Calculate Customer Lifetime Value (LTV) in Excel

1. Create a Unique List of Customers

• Start by generating a unique list of all customer IDs from the dataset.
• Use the UNIQUE function to pull each customer only once.
• Keep this list in a separate summary area so you can build customer-level calculations beside it.
• Using an Excel table makes the references easier to manage as the dataset grows.

2. Sum Total Gross Profit for Each Customer

• Once you have one row per customer, calculate total gross profit at the customer level.
• Use the SUMIFS function to add all gross profit associated with each customer ID.
• This gives you a single lifetime gross profit value for every customer in your summary table.
• After filling the formula down, adjust the formatting so the numbers are easier to read.

3. Determine Whether Each Customer Has Ever Been a Member

• The next step is to identify whether a customer was ever a member at any point in time.
• Instead of checking only the latest transaction, scan the full history for each customer.
• Use COUNTIFS to count whether that customer ever had an active membership flag in the dataset.
• Then use an IF statement to label the result as:
  • Member
  • Non-member
• This creates a clean membership category for each customer in the summary table.

4. Pull in the Customer Type

• After identifying membership status, add the customer type for each customer.
• Use VLOOKUP to match the customer ID in your summary table back to the source data and return the customer type.
• This allows you to classify each customer into categories such as:
  • Residential
  • Commercial
• Once completed, your summary table should now include:
  • Customer ID
  • Total Gross Profit
  • Member Status
  • Customer Type

5. Build the Segment List for Analysis

• Create a small output section that lists the customer groups you want to analyze.
• In the video, the breakdown is based on combinations of:
  • Residential Non-member
  • Residential Member
  • Commercial Non-member
  • Commercial Member
• This creates a simple segment-level framework for averaging LTV across customer groups.

6. Calculate Average LTV by Segment

• With the customer-level summary table complete, calculate the average lifetime value for each segment.
• Use AVERAGEIFS to average total gross profit based on:
  • Member status
  • Customer type
• This gives you an average LTV for each customer group rather than just individual customer totals.
• Fill the calculation down for each segment in your output list.

7. Sort the Results for Better Visuals

• After the segment averages are calculated, arrange the results in the order you want to present them.
• In the walkthrough, the values are positioned so the chart displays the highest category at the top.
• This makes the final visualization easier to read and compare.

8. Create the Chart

• Highlight the segment names and their average LTV values.
• Go to Insert and use Recommended Charts to create the visual.
• Select the first suggested chart if it matches the layout you want.
• Resize the chart as needed and adjust the font size so it fits cleanly on the page or dashboard.
• Update the chart title to match the KPI name.

9. Finalize the Layout

• Clean up the summary section so it is easy to review.
• Make sure the labels are clear and the formatting is consistent.
• At this point, you have a complete LTV view showing which customer groups generate the highest value over time.

10. Result

• You now have a structured Excel analysis that:
  • Aggregates gross profit at the customer level
  • Identifies historical membership status
  • Classifies customers by type
  • Calculates average LTV for each segment
  • Visualizes the results in a chart for quick comparison