How to Do RFM Analysis in Excel

Performing an RFM analysis is a refreshingly simple way to understand exactly who your best customers are - and who's at risk of slipping away. Instead of guesswork, you use real transaction data to group customers into actionable segments. This guide will walk you through, step-by-step, how to conduct your own RFM analysis using only Microsoft Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is RFM Analysis Anyway?

RFM analysis is a technique used to segment customers based on their purchase behavior. It scores customers on three key dimensions:

• Recency (R): How recently did the customer make a purchase? A customer who bought last week is often more valuable than one who bought last year. Recency is a strong predictor of future engagement.
• Frequency (F): How often do they purchase? A customer who buys every month is more loyal and engaged than someone who has only made one or two purchases ever.
• Monetary (M): How much money do they spend? Customers who spend more are, naturally, more valuable to your business. This helps distinguish big spenders from small, infrequent ones.

By combining these three factors, you can move beyond simple metrics like "total sales" and identify specific customer groups. This allows you to create highly targeted marketing campaigns, improve customer retention, and maximize lifetime value. You can reward your "Champions" with exclusive offers, try to win back your "At-Risk" customers, and nurture your "New Customers" so they become loyal fans.

Before You Begin: Preparing Your Data in Excel

Clean, organized data is the foundation of a successful analysis. To get started, you'll need a list of your transactions. Your Shopify, Salesforce, or Stripe accounts are great places to export this data from.

Your spreadsheet should contain at least the following four columns:

• Customer ID: A unique identifier for each customer (e.g., user ID, email address).
• Order ID: A unique identifier for each transaction. This helps you count distinct orders.
• Order Date: The date each transaction occurred.
• Order Value: The total amount paid for each order.

Once you have your data exported as a CSV or Excel file, follow these best practices:

1. Clean Your Data: Ensure dates are formatted correctly as dates (not text). Check that Order Value is in a number format. Remove any rows with missing critical information.
2. Use an Excel Table: The absolute best first step is to format your data range as an Excel Table. Click anywhere inside your data, then go to the "Insert" tab and click "Table" (or use the shortcut Ctrl+T). Tables make formulas much easier to write and manage as your data grows. Give your table a descriptive name, like "SalesData."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Calculate Recency, Frequency, and Monetary Values

We can't score customers until we have their individual R, F, and M values. The cleanest and fastest way to do this for a large dataset is with a PivotTable.

First, we need a "snapshot date" to calculate recency against. This is typically today's date or one day after the last transaction in your dataset. In an empty cell outside your table, enter this formula:

=TODAY()

Name this cell SnapshotDate for easy reference.

Now, let's create the PivotTable:

Create the PivotTable

1. Click anywhere inside your "SalesData" table.
2. Go to the "Insert" tab and click "PivotTable."
3. Excel will automatically select your table range. Choose to place the PivotTable in a new worksheet and click "OK."

In the "PivotTable Fields" pane on the right:

1. Drag Customer ID to the "Rows" area.
2. Drag Order Date to the "Values" area. It will likely default to Count of Order Date. Click it, select "Value Field Settings," and change the summary from Count to Max. This will find the most recent purchase date for each customer. Format this field as a Date.
3. Drag Order ID to the "Values" area. The default Count of Order ID is exactly what we need for Frequency.
4. Drag Order Value to the "Values" area. Change the summary from Count to Sum. This gives us our Monetary value. Format this field as Currency.

When you're done, you'll have a summary table showing each customer's most recent purchase date, their total number of orders, and their total lifetime spend. Copy this PivotTable and paste it as values (Paste Special > Values) into a new sheet. You can name your columns CustomerID, LastPurchaseDate, Frequency, and Monetary.

Calculate Recency

Now, add one more column to your new table called Recency. Here, we'll calculate the number of days since a customer made a purchase. The formula is:

=SnapshotDate - [LastPurchaseDate_Cell]

Assuming your snapshot date is in a cell named SnapshotDate and your last purchase dates begin in B2, the formula would be:

=SnapshotDate - B2

Drag this formula down for all customers. You now have your raw R, F, and M values ready for scoring.

Step 2: Assign RFM Scores from 1 to 5

The raw values aren't very useful for comparison. A frequency of 50 is great, but so is a monetary value of $10,000. To standardize them, we will rank each customer on a scale of 1 to 5 for each metric.

For this, we'll use quintiles, which divide a set of data into five equal parts. The QUARTILE.INC function in Excel is perfect for this. We'll need a small reference table to hold our quartile boundaries.

On a new sheet or off to the side, create the boundaries for R, F, and M. For a 1-to-5 scale, we need four boundaries (20th, 40th, 60th, and 80th percentiles).

For example, to find the Recency boundaries:

• For the first quartile (20th percentile): =QUARTILE.INC(RFM_Table[Recency], 1)
• For the second (40th): =QUARTILE.INC(RFM_Table[Recency], 2)
• For the third (60th): =QUARTILE.INC(RFM_Table[Recency], 3)
• For the fourth (80th): =QUARTILE.INC(RFM_Table[Recency], 4)

Repeat this for Frequency and Monetary, placing the results in an easy-to-reference table.

Now, go back to your main RFM analysis table and add three new columns: R Score, F Score, and M Score. We'll use nested IF statements to assign scores.

Important Note: The scoring logic for Recency is reversed! A lower number of days means it was more recent, which is better. So, low Recency values get high scores.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

R Score Formula:

=IF(C2<=Recency_Quartile1, 5, IF(C2<=Recency_Quartile2, 4, IF(C2<=Recency_Quartile3, 3, IF(C2<=Recency_Quartile4, 2, 1))))

(where C2 is the first Recency value and the boundary cells reference your quartile calculations)

F Score and M Score Formulas:

For Frequency and Monetary, a higher raw value is better, so the logic is straightforward.

=IF(D2>Frequency_Quartile4, 5, IF(D2>Frequency_Quartile3, 4, IF(D2>Frequency_Quartile2, 3, IF(D2>Frequency_Quartile1, 2, 1))))

=IF(E2>Monetary_Quartile4, 5, IF(E2>Monetary_Quartile3, 4, IF(E2>Monetary_Quartile2, 3, IF(E2>Monetary_Quartile1, 2, 1))))

Drag these formulas down to assign scores for every customer.

Step 3: Combine Scores and Define RFM Segments

With individual scores assigned, it's time to create the final RFM score and segment your customers.

Add a new column called RFM Score. We'll simply combine the three individual scores using the CONCAT function:

=CONCAT(G2, H2, I2)

(where G2, H2, and I2 contain the R, F, and M scores, respectively)

This gives you a three-digit RFM score for each customer, like "555" for your best customers or "111" for your least engaged ones.

Now we define our segments. Here are some common segments you can start with:

• Champions (RFM starting with 5, scores are high): Bought recently, buy often, and are heavy spenders.
• Loyal Customers (RFM Frequency is 4-5): High-frequency buyers. Keep them engaged.
• Potential Loyalists (RFM Recency score 3-5, others vary): Recent buyers with potential to become loyal.
• New Customers (RFM starts with 5, frequency is low): Your newest customers. Welcome and onboard them carefully.
• At Risk (RFM Recency is 2-3): Used to be good customers but haven't purchased in a while.
• Hibernating (RFM Recency is 1-2): In danger of becoming inactive. A strong win-back campaign may be needed.
• Lost customers (RFM all scores 1-2): Have not purchased in a long period, hence unlikely to return again.

The cleanest way to assign these segment names in Excel is with a VLOOKUP table. Create a small table with two columns: your RFM Score (like '555', '554', '455', etc.) and the corresponding 'Segment Name'. Then, in a new column called Segment, use a VLOOKUP to automatically assign the segment name based on the customer's score.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Visualize and Act on Your Segments

Your analysis is done! The final step is to make it easy to understand. A new PivotTable and PivotChart are great for this.

Create a PivotTable from your final analysis sheet. Put your new Segment column in the "Rows" area and a Count of CustomerID in the "Values" area. This quickly shows you how many customers fall into each segment.

From this data, you can insert a Bar Chart or Pie Chart to create a simple, shareable dashboard graphic that brings your customer base to life for your team and stakeholders. Now you know exactly who to target with your next marketing campaign!

Final Thoughts

Building an RFM analysis in Excel is a powerful way to turn raw sales data into actionable customer insights. While this manual process takes time to set up, it provides a solid framework for creating more effective, personalized marketing that drives retention and growth.

We built Graphed because we believe valuable insights like RFM shouldn't be trapped behind hours of tedious spreadsheet work. Instead of downloading CSVs and building PivotTables manually, we connect directly to your data sources like Shopify and Google Analytics. When you want to create your RFM Analysis, you can just ask, "Create an RFM dashboard segmenting customers from our Shopify data." You’d get a live, interactive dashboard in seconds, freeing you up to act on insights instead of just gathering them.