Lead scoring in Excel: the formulas, the limits, and when to move on

Lead scoring in Excel means keeping one row per lead, looking up points for each attribute from a table you control, multiplying those points by weights that sum to 100, and adding a recency formula so old activity fades. It works in Excel and Google Sheets with five functions: INDEX and MATCH, SUMPRODUCT, MIN, IF, and a power formula for decay.

This guide gives you the layout and the formulas, then an honest look at where a spreadsheet stops and how to move the model into your CRM when it does.

How to set up the workbook

Three tabs keep the model readable. One rule matters most: no points and no weights typed inside a formula. Every number a person might change lives in a labeled cell.

Tab	What it holds	Who edits it
Leads	One row per lead: the exported values, then the calculated columns	Nobody by hand. Paste, then fill down
Lookup	Each factor, each value it can take, and the points for it, from 0 to 10	The model owner
Weights	A percentage weight per factor, plus the decay settings	The model owner

Leads tab. Columns A to H are pasted from your CRM export: lead name (A), then company size, industry, seniority, required system, and disqualifier (B to F), the date of the last high-intent activity (G), and the count of high-intent actions in the last 30 days (H). Columns I to M hold the points for B to F. N and O are the fit and engagement scores, and P and Q are the band and the play. The headers in B1 to F1 must match the factor names on the Lookup tab exactly, because the lookup formula reads them.

Lookup tab. One long table. Column D is a helper that joins factor and value into a single key.

A: Factor	B: Value	C: Points	D: Key
Company size	200 to 1,000	9.4	Formula below
Company size	50 to 199	6.6
Industry	Core	9.2
Disqualifier	Personal email	4.3

The key in D2, filled down:

=A2&"|"&B2

Points here are demo values. Set yours from your ideal customer profile.

Weights tab. Weights run across a row so they line up with the points columns on the Leads tab.

	B	C	D	E	F
Row 1	Company size	Industry	Seniority	Required system	Disqualifier
Row 2: Weight (%)	30	25	25	15	5

Below that sit three settings: actions for full credit in B5 (3), half-life in days in B6 (14), and an “as of” date in B7. The disqualifier weight is the share it subtracts.

The formulas

Each formula goes in row 2 of the Leads tab and fills down. All of them work unchanged in Excel and Google Sheets. If your locale uses a comma as the decimal mark, swap the commas between arguments for semicolons.

Value to points: INDEX and MATCH

In I2, then fill right to M2 and down:

=IFERROR(INDEX(Lookup!$C$2:$C$40, MATCH(B$1&"|"&B2, Lookup!$D$2:$D$40, 0)), 0)

MATCH finds the row where the key equals the header plus the lead’s value, and INDEX returns the points from that row. IFERROR returns 0 when there is no match, so a blank field contributes nothing.

If you prefer one small table per factor, VLOOKUP does the same job:

=IFERROR(VLOOKUP(B2, Lookup!$B$2:$C$5, 2, FALSE), 0)

Keep FALSE as the last argument. Without it, VLOOKUP returns the nearest match and scores the wrong row.

Weighted fit score: SUMPRODUCT

In N2:

=IF(F2="Competitor", 0, ROUND(MAX(0, SUMPRODUCT(I2:L2, Weights!$B$2:$E$2)/100 - M2*Weights!$F$2/100), 1))

SUMPRODUCT multiplies each factor’s points by its weight and adds the results. Dividing by 100 gives a score out of 10. Both ranges must be the same size, or Excel returns a #VALUE! error.
The disqualifier points, times their weight, are subtracted, and MAX(0, ...) stops the score going negative.
ROUND(..., 1) makes the band formula read the same number a person sees.
The outer IF is a hard disqualifier. A competitor scores 0 whatever else is true.

To confirm the weights add up, put this in B3 of the Weights tab:

=IF(SUM(B2:F2)=100, "OK", "Weights must sum to 100")

Recency decay and caps: a power formula and MIN

In O2:

=IF(G2="", 0, ROUND(10*MIN(H2, Weights!$B$5)/Weights!$B$5*0.5^((Weights!$B$7-G2)/Weights!$B$6), 1))

MIN(H2, Weights!$B$5) is the cap. Three actions earn full credit and a fourth adds nothing, so one busy visitor cannot run up the score.
0.5^(days/half_life) is the decay. With a 14-day half-life, activity from 14 days ago is worth half, and from 28 days ago a quarter.
Weights!$B$7 is the “as of” date. Put =TODAY() there for a live sheet, or a fixed date when two people need to get the same numbers.

Band and play: nested IF

In P2 and Q2:

=IF(N2>=7, "7 and up", IF(N2>=4, "4 to 6.9", "Under 4"))

=IF(N2>=7, IF(O2>=6, "Call within one business day", "Follow up this week"),
 IF(N2>=4, IF(O2>=6, "Qualify fast", "Nurture"), "No rep time"))

The cutoffs are starting points. Move them until the top band holds a list your team can work in a week.

A worked example

Five demo leads, scored on October 1, 2026 with the settings above. Every number came out of the formulas in this article.

Lead	Size	Industry	Seniority	System	Disqualifier	Days ago	Actions	Fit	Engagement	Play
A	200 to 1,000	Core	Director	Yes	Personal email	3	4	8.3	8.6	Call within one business day
B	50 to 199	Core	VP or C-level	Yes	None	21	2	8.0	2.4	Follow up this week
C	200 to 1,000	Adjacent	Manager	Blank	None	2	3	5.8	9.1	Qualify fast
D	1,001 to 5,000	Core	VP or C-level	Yes	Competitor	1	6	0.0	9.5	No rep time
E	Under 50	Other	Individual contributor	No	None	92	1	1.8	0.0	No rep time

Lead A 8.3/10

Company size (200 to 1,000) 30% 9.4
Industry (core) 25% 9.2
Seniority (director) 25% 8.4
Runs the required system 15% 8.7
Personal email at sign-up 5% -4.3

Demo data. Lead A from the worked example: four factors add, the disqualifier subtracts, and the fit score is the weighted sum.

Lead A by hand: 2.82 plus 2.3 plus 2.1 plus 1.305 is 8.525. The disqualifier subtracts 4.3 at 5%, or 0.215, for 8.31, shown as 8.3. Engagement is 10 times 0.5 raised to 3/14, or 8.6, with the fourth action capped.

Three rows show why each formula earns its place:

Lead C has no value for the required system. The lookup returns 0, so the factor adds nothing and the lead lands at 5.8. With the field filled in, it would score 7.1.
Lead D would score 7.1 from the weights alone, because a 5% disqualifier can subtract half a point at most. The hard IF is what takes a competitor to 0.
Lead E was last active 92 days ago. After six and a half half-lives, engagement rounds to 0.0.

Checks worth building in

A scoring sheet fails quietly. A typo in a value, a trailing space from an export, or a date that arrived as text all produce a number that looks fine.

Check	Formula	What it catches
Factors that scored nothing	`=COUNTIF(I2:L2, 0)`	Blank fields, and values missing from the Lookup tab
Dates stored as text	`=ISNUMBER(G2)`	Exported dates the decay formula cannot read

The risk is documented. An audit of 50 operational spreadsheets, published in the Journal of Organizational and End User Computing, found errors in all but three, with just under 1% of formulas returning a wrong result. Protect the Lookup and Weights tabs, keep one owner, and date every version.

What a spreadsheet does well

Design. The whole model sits on one screen: factors, values, points, weights.
Agreement. Sales and marketing can argue about a weight, change it, and watch the ranking move in the meeting.
Testing on a sample. Score last quarter’s leads, sort by fit, and check whether the ones that became customers sit near the top. Nothing touches a live record.
Cost. It is free, and everyone already knows how to use it.

For a small team with a few dozen leads a week, that can be the whole answer. Export on Monday, paste, sort, and work the list.

Where a spreadsheet stops

The model is the same in all three columns. What changes is where it runs. The middle column uses HubSpot’s scoring tool as the example, described from HubSpot’s documentation on how the tool works and building scores. Other CRMs differ, so check with your vendor.

	Spreadsheet	CRM native scoring (HubSpot)	BoostIdeal in HubSpot
Stays current as data changes	Only when someone pastes a new export	Updates continuously as records meet the criteria	Hourly by default, and in near real time with webhook sync
Every record scored	Only the rows in the export	All records or a chosen segment, retroactively when turned on	Leads, contacts, accounts, and custom objects, or a CSV you upload
Reasons visible to reps on the record	In the points columns, if a rep opens the file	Score history cards on the record	Reasons write back to HubSpot properties beside the score
Who can change it, and how safely	Anyone with edit access. One overwritten cell changes every score	Users with edit permission for Lead Scoring, who can test records first	You set the weights. Several models can run side by side before anything acts on one
Triggers workflows	No, unless you import the scores back	Yes. Score properties work in workflows, segments, and reports	Yes, through the properties it writes. Your HubSpot workflows do the acting
Effort to maintain	Export, paste, fill down, and re-import, every cycle	Review rules and thresholds each quarter	Review factors and weights each quarter
Error risk	Broken references, unfilled formulas, stale exports, competing copies	Misconfigured rules. Empty properties earn no points	Wrong weights reach every record. Empty fields contribute 0

Native scoring is the natural next step for most teams whose CRM includes it. Our guide to HubSpot lead scoring limits covers where that runs out.

Signs you have outgrown the spreadsheet

What you see	What it means	What to do
Reps ask which file is the current one	The score has no single home	Put the score on the CRM record
The Monday export slipped twice this month	The score is as fresh as one person’s calendar	Move to scoring that updates with the data
A lead booked a demo and waited for the next refresh	Engagement goes stale between exports	Score in the CRM and trigger on the change
Someone “fixed” a weight and nobody noticed for weeks	Changes are untracked and untested	Limit who can edit, and test before switching

One of these is a nuisance. Three or more, and the sheet costs more than it saves. Wanting routing or tasks to fire on the score settles it, because a workflow cannot read a file.

Migration checklist: spreadsheet to CRM scoring

Freeze the model. Stop editing weights and points. Save a dated copy and name its owner.
Export factors and weights. The Lookup and Weights tabs are the spec. Add the caps, half-lives, disqualifiers, and band cutoffs.
Rebuild. Map every spreadsheet column to a CRM property first, then recreate each factor in the scoring tool.
Run both in parallel. Keep scoring in the sheet for two to four weeks while the new score fills in beside it.
Compare rankings on recent wins. Take last quarter’s closed-won leads and check where each model ranks them. Investigate every lead where the two disagree by more than a point.
Switch workflows last. Point routing, tasks, and lists at the new score only after the comparison holds. Then archive the sheet as read-only.

For the fit side of the rebuild, see our five-step framework for building an ICP scoring model.

Where BoostIdeal fits

BoostIdeal publishes a lead scoring worksheet, an account scoring worksheet, and a one-page lead scorecard, each with a worked example you can copy; downloadable files are being added. Use them to design the model. When the sheet runs out, BoostIdeal runs that model inside HubSpot: you build it and set the weights by hand, with AI helping you analyze your data, and each score out of 10 writes back to the record with its reasons. It also accepts a CSV upload, so a spreadsheet list can be scored without a HubSpot sync.

It is not the right fit if your data is not in HubSpot and you do not want to work from CSV uploads, or if you want a vendor to hand over a number you never question. And if a weekly sheet is still working for your team, keep the sheet.

Frequently asked questions

Can you do lead scoring in Excel?

Yes. Keep one row per lead, a lookup table that turns each attribute into points, and a row of weights that sum to 100. SUMPRODUCT produces the weighted fit score, and a power formula fades engagement as it ages. It suits design, testing, and low weekly volume.

What is the formula for lead scoring in Excel?

The core is =SUMPRODUCT(points_range, weights_range)/100, which gives a score out of 10 when points run from 0 to 10 and weights sum to 100. Points come from INDEX and MATCH against a lookup table. Subtract disqualifier points, and wrap the result in MAX(0, ...) so it cannot go negative.

Does lead scoring work the same way in Google Sheets?

Yes. INDEX, MATCH, VLOOKUP, SUMPRODUCT, MIN, MAX, IF, IFERROR, ROUND, and TODAY behave the same in both, and the formulas in this guide run unchanged. A single shared workbook, in either product, helps with the version problem.

How do you add score decay in a spreadsheet?

Multiply the activity credit by 0.5^(days/half_life), where days is today’s date minus the date of the activity. With a 14-day half-life, activity loses half its value every two weeks. Keep the half-life in its own cell so you can tune it.

Is a spreadsheet or software better for lead scoring?

A spreadsheet is better for designing and testing the model. Scoring in the CRM is better for running it, because the score stays current, sits on the record, and can trigger workflows. Most teams should do the first, then the second.

How do I get spreadsheet scores into my CRM?

Most CRMs can import a CSV into a custom property, matched on email or record ID. That puts a number on the record, but it is a snapshot from the day of the import. If you are importing every week, move the model into the CRM.

The takeaway

A spreadsheet is the right place to build a lead scoring model and a risky place to run one for long. Use three tabs, keep every point and weight in a labeled cell, and test the ranking against leads you already know. If a weekly sheet keeps up with your volume, stay there. When the score has to stay current, appear on the record, or trigger a workflow, freeze the model, rebuild it in your CRM, run both in parallel, and switch the workflows last.