How to do RFM segmentation in Excel
RFM (Recency, Frequency, Monetary) is the cleanest customer-segmentation method that doesn't need machine learning. With one transactions sheet you can score every customer on a 1-5 scale across three dimensions in 20 minutes. Here's how, with formulas you can paste directly.
TL;DR
RFM scores each customer on three dimensions: how recently they bought (R), how often (F), and how much they spent (M).
Score from 1 to 5 per dimension using =PERCENTRANK or quintile splits. The combined score RFM is a 3-digit code: 555 = champions, 111 = lost.
You don't need a database — pure formulas on a transactions sheet are enough. Or skip the formulas with our free tool.
1.Lay out the transactions sheet
Two columns minimum: customer_id and order_date. Three columns ideal: add order_value. The sheet should have one row per order, with hundreds or thousands of rows.
2.Compute Recency, Frequency, Monetary per customer
Use a pivot table on customer_id with: max(order_date) - today's date as Recency in days; count(order_date) as Frequency; sum(order_value) as Monetary. Drop into a new sheet.
3.Score each dimension 1-5
For Recency: =6 - PERCENTRANK.INC(R_range, R_value, 0) * 5 (smaller days = higher score). For Frequency and Monetary: =PERCENTRANK.INC(...) * 5 (bigger = higher).
4.Concatenate the RFM code
=R_score & F_score & M_score gives a 3-digit code per customer (e.g. 543, 222, 111).
5.Map codes to named segments
Use VLOOKUP against a 125-row segment table that maps codes like 555 → Champions, 511 → New customers, 122 → At risk, 111 → Lost. The standard segment grid is in the references below.
What the segments mean
Champions (555, 554, 545, 544, 455)
Bought recently, often, and spent heavily. ~5-10% of customers, ~25-40% of revenue. Treat them as VIPs — early access to new products, personal outreach, retention budget concentrated here.
Loyal customers (453, 543, 444, 343)
Strong on F and M, R drifting down. Re-engagement campaigns work. Don't waste discount budget — they need a reason to come back, not a price cut.
At risk (155, 154, 144, 254)
High historic value (F and M) but very low recency. The most important segment to act on — you're losing your best historic customers right now. Phone call, not email.
Hibernating / lost (111, 112, 121, 122)
Low everywhere. Don't spend money trying to revive them — cost-per-recovery exceeds CLV. Suppress from active campaigns, exclude from cohort analysis.
Common mistakes
1. Equal-width buckets instead of quintiles. If 80% of your customers are in the £0-100 bucket, splitting at £20/£40/£60/£80 will compress them into RFM=1. Use percentile-based splits (PERCENTRANK or QUARTILE.INC) so each bucket gets ~20% of customers.
2. Recency = absolute date, not recency. Use days since last order, not the date itself. Otherwise the formulas break across financial years.
3. Re-running RFM every day. RFM is a snapshot. If you recompute scores daily, customers oscillate between segments based on the daily ranking noise. Run weekly or monthly, hold scores stable in between.
Skip the formulas
The cohort retention tool, forecasting calculator, and anomaly detector all run client-side, no signup. For everything end-to-end, DataHub Pro — £19/user/month, 14-day free trial.
Compare with all 12 tools →