How to Build a Lead Scoring Model in Excel

A lead scoring model in Excel gives every sales lead a number, so the team calls the most promising ones first. You award points for fit (who the lead is) and engagement (what they did), add the points up, and turn the total into a grade such as Hot, Warm or Cold. This guide builds a small model step by step with XLOOKUP, SUM and SUMPRODUCT, and it suits anyone in sales, marketing or operations, or preparing for a sales analyst interview.

What is a lead scoring model?#

A lead is a person or company that might buy. A lead scoring model is a set of rules that turns facts about a lead into points. It has three parts:

1. Criteria: the facts you score, such as job title or "requested a demo".
2. Points: how much each fact is worth.
3. Grades: score bands that tell the team what to do, such as "Hot: call today".

There is no standard set of points. The numbers in this guide are example assumptions to show the method. Your own should come from your sales team's experience, and you should test them against past results (see the last step).

Here is the lead list used in the examples, in A1:E6:

Step 1: Put the points in a lookup table#

Keep the points in their own table, not inside formulas, so anyone can see and change them. Put the job title points in N1:O4:

The other example rules are:

• Employees: 0–49 scores 5, 50–249 scores 15, 250 or more scores 25.
• Pricing visits: 5 points per visit, capped at 20.
• Demo request: "Yes" scores 30.

The best possible score is 25 + 25 + 20 + 30 = 100, which makes the scores easy to read.

Step 2: Score each criterion with XLOOKUP#

Add one score column per criterion. Each uses the simplest function for its rule.

Title points (F2) come from the table with an exact match. If a title is missing from the table, the fourth argument returns 0 instead of #N/A:

=XLOOKUP(B2,$N$2:$N$4,$O$2:$O$4,0)

Company size points (G2) use bands, so they need an approximate match. Match mode -1 finds the largest threshold that is not above the number:

=XLOOKUP(C2,{0,50,250},{5,15,25},,-1)

For 320 employees, the largest threshold not above 320 is 250, so the result is 25. The guide to XLOOKUP approximate match explains match modes -1 and 1 in more detail. The braces hold an array constant; in a real model you may prefer a second small table.

Engagement points (H2 and I2) use MIN for the cap and IF for the yes/no question:

=MIN(D2*5,20)
=IF(E2="Yes",30,0)

XLOOKUP needs Microsoft 365 or Excel 2021 or later. In older Excel, use VLOOKUP with FALSE for the title and VLOOKUP with TRUE on a sorted table for the bands. The XLOOKUP function guide lists every argument.

Step 3: Add up the lead score with SUM#

The lead score is the sum of the four score columns. In J2:

=SUM(F2:I2)

Fill it down. The example leads score:

Keeping one column per criterion makes the score explainable: a sales rep can see that L-04 scored 70 mainly because of company size and a demo request.

Step 4: Score a checklist in one formula with SUMPRODUCT#

If your criteria are all yes/no, SUMPRODUCT can replace the helper columns. Record 1 for yes and 0 for no, then multiply each flag by its points and add the results:

=SUMPRODUCT(B2:B6,C2:C6)

The result is 80: 25 + 20 + 5 + 30, with the company size points left out because the flag is 0. The SUMPRODUCT guide and the SUMPRODUCT function reference show more uses.

For a weighted model, rate each criterion from 1 to 5 and give each a weight that adds up to 100%. With weights 30%, 20%, 20%, 10% and 20% and ratings 4, 3, 5, 2 and 5, =SUMPRODUCT(weights,ratings) returns 4.0 out of 5. This is the same maths as a weighted average in Excel.

Step 5: Turn scores into Hot, Warm and Cold grades#

Grades tell the team what to do. Put the minimum score for each grade in a table sorted from low to high, for example Q1:R4: 0 is Cold, 40 is Warm, 70 is Hot. Then in K2:

=XLOOKUP(J2,$Q$2:$Q$4,$R$2:$R$4,,-1)

L-01 (95) and L-04 (70) are Hot, L-03 (50) is Warm, and L-02 and L-05 are Cold. A score of exactly 70 counts as Hot, because match mode -1 accepts an exact match first. To count each grade for a summary, use =COUNTIF(K2:K6,"Hot"), which returns 2.

Step 6: Check the model against real results#

A scoring model is only useful if high scores really buy more often. Once you have a few months of data, add column L for the outcome (Won or Lost) and compare grades:

=COUNTIFS(K:K,"Hot",L:L,"Won")/COUNTIF(K:K,"Hot")

This is the win rate for Hot leads: Hot leads marked Won, divided by all Hot leads. If Warm leads win as often as Hot ones, your points or thresholds need adjusting. Because the points sit in tables, you change a number and every score updates.

To track what happens after scoring, connect the model to a sales pipeline tracker in Excel, or keep the leads in a simple CRM built in Excel.

Practise it#

A lead score is lookups plus addition, so practise both. Practise XLOOKUP in a live spreadsheet, including approximate match for score bands, and warm up with the SUM and AutoSum lesson. Then try the sales report interview drill to build a summary under realistic conditions. Each lesson checks your formula the moment you press Enter.