RFM Analysis in Excel: Segment Customers by Value

RFM analysis in Excel scores every customer on three things: Recency (how many days since the last order), Frequency (how many orders) and Monetary value (how much they spent in total). You build it with three formulas, MAXIFS, COUNTIFS and SUMIFS, then turn each number into a score and group customers with similar scores. This guide is for analysts, marketers and job seekers who want a clear, repeatable way to segment a customer list using only an order export.

What RFM analysis measures#

RFM looks at buying behaviour, not at who the customer is. The three measures answer three simple questions:

A customer who bought last week, buys often and spends a lot scores high on all three. A customer who bought once, months ago, scores low. The scores don't predict anything on their own. They give you groups you can compare, report on and treat differently. The data below is example data.

Step 1: set up the order list and an analysis date#

Start from one row per order. Put the orders on a sheet called Orders, with the order date in column A, the customer in column B and the amount in column C. A short example looks like this:

On a second sheet, list each customer once in column A. In Microsoft 365 or Excel 2021, =UNIQUE(Orders!B2:B500) does this for you. Then type an analysis date in H1, for example 31 Oct 2026, the day after the data ends.

Use a fixed date, not TODAY(). With TODAY(), every recency value changes overnight, so a segment you report on Monday is different by Friday.

Step 2: calculate recency with MAXIFS#

Recency is the analysis date minus the customer's latest order date. MAXIFS finds the latest date, because a later date is a bigger number in Excel. In B2:

=$H$1-MAXIFS(Orders!$A$2:$A$500,Orders!$B$2:$B$500,A2)

MAXIFS works like MAX, but only over the rows that match a condition. The plain MAX function would return the latest date in the whole list. MAXIFS needs Excel 2019 or later; the guide to MAXIFS and MINIFS covers it in detail. Format the result as a number, not a date, or Excel may show it as a date in 1900.

Step 3: calculate frequency and monetary value#

Frequency is a count of orders, and monetary value is a sum of amounts, both for one customer. In C2 and D2:

C2: =COUNTIFS(Orders!$B$2:$B$500,A2)
D2: =SUMIFS(Orders!$C$2:$C$500,Orders!$B$2:$B$500,A2)

Fill all three formulas down (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the order ranges fixed while A2 moves down the list. The COUNTIFS function guide and the SUMIFS function guide explain the arguments, and the article on COUNTIFS and SUMIFS with several conditions shows how to add a date range, for example only orders from the last 12 months.

With a full order list, the summary for six example customers looks like this:

Step 4: turn each measure into a score#

A score puts every customer on the same 1-to-3 scale, so the three measures can be read together. Pick cut-off values that make sense for your business and keep them in cells. Here, with IFS:

E2: =IFS(B2<=30,3,B2<=90,2,TRUE,1)
F2: =IFS(C2>=6,3,C2>=3,2,TRUE,1)
G2: =IFS(D2>=1000,3,D2>=400,2,TRUE,1)

Notice that recency runs the other way: fewer days gives a higher score. IFS checks the tests in order and stops at the first TRUE one, and the final TRUE,1 catches everything else.

For larger lists, many analysts use 1 to 5 and set the cut-offs with percentiles, so each score holds about the same number of customers. For example, =PERCENTILE.INC(D2:D500,0.8) gives the spend that only the top 20% reach. The guide to percentiles and quartiles shows how.

Step 5: build the RFM code and segments#

Join the three scores into one code with &. In H2:

=E2&F2&G2

The codes are descriptions, not verdicts. Name the groups that matter to you, such as "Top customers" for 333 or "Lapsed regulars" for codes that start with 1 and have high F and M. To count each segment, use =COUNTIF(H2:H7,"333"), or =COUNTIF(H2:H7,"1*") for every code that starts with 1.

Common RFM mistakes in Excel#

A few slips make RFM scores misleading. Check these:

1. Returns and refunds counted as orders. Negative amounts reduce spend but still add to frequency. Filter them out with an extra COUNTIFS condition such as Orders!$C$2:$C$500,">0".
2. Customer names that don't match. "Ana Lopez" and "Ana Lopez " (with a space) are two customers. Use a customer ID if you have one.
3. Recency shown as a date. The result of a date minus a date may pick up a date format. Change it to Number.
4. Cut-offs typed inside formulas. Put them in cells so you can change them and see the effect on every score.
5. Mixing time periods. Use the same date window for all three measures, for example the last 12 months.

Once the scores are in place, a PivotTable or a short summary table turns them into a report. The sales report guide shows how to lay one out, and the simple Excel CRM is a good home for the customer list.

Practise it#

RFM is three conditional formulas you will use in any customer or sales job. Practise COUNTIFS and SUMIFS in a live spreadsheet, then practise AVERAGEIF and MAXIFS to find latest dates by customer. ExcelReady checks each formula as you type. Start with Ben's recency in the exercise on this page.