Guides

RFM Analysis in Excel: Segment Customers by Recency, Frequency and Monetary Value

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 6 October 2026 · Last updated: 6 October 2026

Ruhi runs Ruhi Organics, a fictional online grocery store in Pune. Her order list has 321 orders from 60 customers. Customer C-1005 last ordered 9 days ago, ordered 14 times and spent ₹29,900: RFM code 555, a Champion. Customer C-1007 also spent well (8 orders, ₹12,710) but the last order was 230 days ago: code 145, At risk. That is the customer Ruhi should call this week, before C-1007 is gone for good.

RFM analysis ranks customers on three questions: how Recently did they buy, how Frequently do they buy, and how much Money do they spend. In Excel you calculate the three values per customer with MAXIFS, COUNTIFS and SUMIFS, turn each into a score from 1 to 5 with percentile cut-offs, and use a simple IF rule to name segments such as Champions, Loyal, At risk and Lost.

Download RFM_Analysis_Template.xlsx

Excel file, 33 KB. Built for Microsoft 365 Excel on Windows; it also opens in Excel 2021 and 2024. All numbers in this workbook are fictional teaching data, not real company figures.

The Dashboard sheet with the sample data (Ruhi Organics is a fictional business).

=5-COUNTIF(RCuts,"<"&[@[Recency Days]])
=1+COUNTIF(FCuts,"<"&[@Frequency])
=1+COUNTIF(MCuts,"<"&[@Monetary])

What R, F and M mean

Recency matters most: a customer who bought last week is far more likely to buy again than one who bought last year, even if the old customer spent more. That is why RFM puts R first.

Step 1: Start from one row per order

You need only four columns. Here are the first rows of the sample (the full table has 321 orders):

1. Put the cursor in the data and press Ctrl + T to make it a table.
2. Name it Orders in Table Design > Table Name.
3. Remove cancelled and fully returned orders first, or they will push customers into the wrong segment.
4. Pick an analysis date. The template keeps it in a named cell AsOfDate on the Lists sheet (30-09-2026 in the sample). Use a fixed date for a report you will share; use =TODAY() only for a live sheet.

Step 2: Make the customer list

1. Copy the Customer ID column of Orders to a new sheet.
2. Select it and click Data > Remove Duplicates > OK.
3. Press Ctrl + T and name the table Customers.
4. In Microsoft 365 you can instead type =UNIQUE(Orders[Customer ID]) in an empty cell. It spills the list, but a spilled list cannot sit inside a table, so paste it as values if you want a table.

Step 3: Recency, frequency and monetary formulas

Type each formula once in the first row of the Customers table. The table copies it down to every customer.

Format Last Order as a date and Recency Days as a whole number. If you see a 5-digit number in Last Order, the cell is just showing the date as a number: choose Home > Number Format > Short Date.

Step 4: Turn the values into scores from 1 to 5

A score of 1 to 5 makes customers easy to compare. The common way is quintiles: the best 20% of customers get 5, the next 20% get 4, and so on. You need four cut-off points per column: the 20th, 40th, 60th and 80th percentile.

=PERCENTILE.INC(Customers[Recency Days],0.2)
=PERCENTILE.INC(Customers[Frequency],0.2)
=PERCENTILE.INC(Customers[Monetary],0.2)

With the sample data the cut-offs are:

The template types the percentiles 20%, 40%, 60% and 80% in column A and uses =PERCENTILE.INC(Customers[Recency Days],$A6), so you can change them. The four recency cut-offs sit in Calculations!B6:B9 and have the name RCuts (frequency FCuts, monetary MCuts). Then one COUNTIF gives the score:

1. R Score =5-COUNTIF(RCuts,"<"&[@[Recency Days]]). C-1007 has 230 days. All four cut-offs are smaller, so COUNTIF gives 4 and the score is 5 - 4 = 1.
2. F Score =1+COUNTIF(FCuts,"<"&[@Frequency]). C-1007 has 8 orders. Three cut-offs (2, 3 and 5.4) are smaller than 8, so the score is 1 + 3 = 4.
3. M Score =1+COUNTIF(MCuts,"<"&[@Monetary]). ₹12,710 is above all four cut-offs, so the score is 5.
4. RFM Code =[@[R Score]]&[@[F Score]]&[@[M Score]] joins them: 145.

Recency is reversed (5 minus the count) because fewer days is better. Frequency has many ties (lots of customers with 1 or 2 orders), so its groups are not exactly 20% each. That is normal.

Step 5: Name the segments

The 125 possible codes are too many to act on, so group them. The template uses R and F only, which is the most common teaching version:

=IF(AND([@[R Score]]>=4,[@[F Score]]>=4),"Champions",
 IF(AND([@[R Score]]>=3,[@[F Score]]>=3),"Loyal",
  IF([@[R Score]]>=4,"New / promising",
   IF([@[F Score]]>=3,"At risk",
    IF([@[R Score]]<=2,"Lost / hibernating","Needs attention")))))

The order of the IFs matters: each IF only sees customers that the earlier ones did not catch. With the sample data the Segment summary on the Calculations sheet shows:

12 Champions bring 50.3% of all revenue. The 11 At-risk customers bring another 20.8%, which is a lot of money to lose quietly.

Step 6: Read the R x F grid

The Calculations sheet also counts customers for every pair of R and F scores with formulas such as =COUNTIFS(Customers[R Score],$A25,Customers[F Score],1) (R score in column A, F score fixed per column), and a colour scale turns it into a heat map:

Top-right (recent and frequent) is where you want customers to be. Bottom-right (frequent but not recent) is the At-risk corner. A full top-left column (recent, one order) means many new customers: the job is to get their second order.

What to do with each segment

Run the analysis again every month with a new analysis date and watch customers move between segments. A rising At-risk count is an early warning, months before revenue drops.

Do it with a PivotTable instead

1. Click inside the Orders table and choose Insert > PivotTable > OK.
2. Drag Customer ID to Rows.
3. Drag Order Date to Values, click it, choose Value Field Settings > Max and format it as a date.
4. Drag Order ID to Values (it shows Count) and Amount to Values (Sum).
5. Next to the PivotTable, calculate recency and the scores with normal formulas, or copy the PivotTable and paste it as values first.

The table-and-formula version in the template updates by itself when you add orders; a PivotTable needs Data > Refresh All.

Common mistakes

• Using =TODAY() in a report you send: the scores change every day, and nobody can check your numbers next week.
• Leaving cancelled orders or refunds in the data.
• Scoring over too short a period. Use at least 6 to 12 months of orders, more if customers buy rarely.
• Using quintiles on a very small list. With fewer than about 20 customers, rank them by hand or use fixed rules such as recency under 30 days.
• Treating the segment names as fixed laws. They are a starting point; change the IF rules to fit how often your customers normally buy.