How to do RFM Analysis in Excel — Segment Customers by Value from Scratch

RFM — Recency, Frequency, Monetary — is the most actionable customer segmentation model you can build without machine learning. It tells you exactly which customers are Champions (buy often, buy recently, spend the most), which are At Risk (were Champions but have gone quiet), and which are Lost. This tutorial walks through building the complete model in Excel: DATEDIF for recency, COUNTIFS for frequency, SUMIFS for monetary value, PERCENTILE-based 1–5 scoring, and segment label assignment. No VBA, no add-ins, no paid tools.

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

Step 1 Prepare your transaction data as an Excel Table

RFM analysis starts with a transaction-level log — one row per transaction (or order), not one row per customer. If you already have aggregated data you’ll need to reconstruct the raw transactions, or adapt the Recency step to use last-transaction-date directly.

Required columns

CustomerID must be a consistent identifier — the same string or number for the same customer across all rows. OrderDate must contain real Excel date values, not text strings. Revenue must be a number (no £ symbols, no commas as thousands separators in the raw cell value).

Convert the range to an Excel Table: click inside the data and press Ctrl+T. On the Table Design tab, rename it to Transactions. This ensures all MAXIFS, COUNTIFS, and SUMIFS formulas auto-expand when you add new transaction rows in future months.

Step 2 Build the customer list and calculate Recency

Add a new worksheet called RFM. Column A will hold unique CustomerIDs. If you’re on Microsoft 365 you can use =SORT(UNIQUE(Transactions[CustomerID])) to auto-populate the list. For Excel 2016/2019, remove duplicates: copy the CustomerID column, paste into the RFM sheet, then Data → Remove Duplicates.

Recency: days since last purchase

In column B (Recency), enter this formula for the first customer row and drag down:

This formula does three things: MAXIFS finds the most recent OrderDate for this CustomerID; DATEDIF(..., TODAY(), "D") calculates the number of complete days between that date and today. A lower Recency value means the customer purchased more recently — which is better. Keep this in mind when scoring.

Common issue — MAXIFS returns 0: this usually means the CustomerID in column A doesn’t exactly match the Transactions table. Check for trailing spaces (use TRIM(A2)), text vs number mismatch, or case differences. A quick check: enter =COUNTIFS(Transactions[CustomerID], A2) — if it returns 0, there is no matching row.

Step 3 Calculate Frequency with COUNTIFS

Frequency measures how many times a customer has transacted. In column C (Frequency):

This counts the total number of rows in the Transactions table where CustomerID matches. If one physical order can span multiple rows (e.g. one row per line item), you need to count distinct OrderIDs instead:

This SUMPRODUCT formula counts distinct OrderIDs for the customer, regardless of how many line items each order contains. It’s more complex but gives you a genuine “number of purchases” count rather than a line-item count.

Frequency within a date window

If you only want to score activity within the last 12 months (a common approach to focus on active customers only), add a date filter:

Step 4 Calculate Monetary value with SUMIFS

Monetary is the total revenue generated by the customer. In column D (Monetary):

For a time-windowed monetary value (e.g. only last 12 months to match the frequency window):

Average Order Value as a secondary metric

Add column E for Average Order Value (AOV), which is a useful supplement to raw Monetary for segments where high spend but very few orders may indicate a bulk B2B customer rather than a loyal consumer:

Wrap in IFERROR to handle customers with zero frequency: =IFERROR(D2/C2, 0).

Step 5 Score each dimension 1–5 using PERCENTILE thresholds

Rather than using arbitrary absolute thresholds (“anyone who spent over £500 gets score 5”), PERCENTILE-based scoring adapts automatically to your customer distribution. A customer who spent £300 might be in the top 20% for a small business but the bottom 20% for a large retailer. Percentile scoring always compares customers against each other.

Setting up the threshold table

On a helper sheet (or a side area of the RFM sheet), calculate the percentile breakpoints. For the Frequency and Monetary columns:

Name these cells F_P20, F_P40, F_P60, F_P80 (and equivalent M_ and R_ versions for Monetary and Recency) using the Name Box.

F_Score formula (higher Frequency = higher score)

R_Score formula (REVERSED — lower Recency days = higher score)

Note the reversed direction: customers with more days since last purchase (higher Recency value) get a lower score. R_P80 is the 80th percentile of the Recency days column — so “above P80 days” means the customer is in the most dormant 20% and gets score 1.

M_Score formula (higher Monetary = higher score)

Add three score columns: R_Score (col F), F_Score (col G), M_Score (col H). Then combine them into an RFM string in column I:

This creates values like “555” (Champion) or “133” (At Risk). And an overall RFM score in column J (optional, for quick sorting):

Step 6 Assign segment labels and build summary output

Add column K (Segment). Use IFS to map RFM string patterns to segment names. The full 125-combination mapping is impractical — use a simplified model that covers the highest-value marketing actions:

Standard RFM segments and what to do with them

Summary pivot table by segment

Insert a PivotTable from the RFM sheet. Drag Segment to Rows. Add CustomerID as Count to Values (how many customers per segment). Add Monetary as Sum to Values (total revenue per segment). Add Recency as Average to Values (average days dormant per segment). This gives you the segment distribution and tells you where your revenue is concentrated — typically Champions represent 10–15% of customers but 40–60% of revenue.

Exporting segment lists for marketing

To extract At Risk customers for a win-back campaign, use FILTER (Microsoft 365):

On Excel 2016/2019, use AutoFilter on the Segment column and copy visible rows. Export the CustomerID and email list to CSV and upload to your email platform (Klaviyo, Mailchimp, HubSpot) as a segment for targeted campaigns.