How to track leads in Excel
A lead tracker that works is one row per lead, nine columns, and a single next action date column that you sort by every morning. Everything else — conversion rates, weighted pipeline value, what an enquiry is worth — comes out of those nine columns with COUNTIFS and SUMIFS, and this page works through both the layout and the sums.
One row per lead — the rule that decides everything
The most common way a lead spreadsheet fails is logging activities instead of leads: a row for the first call, another for the follow-up email, another for the quote. Within a month it is impossible to count how many live opportunities exist, because counting rows counts phone calls.
One row per lead. The row is updated as the lead moves. Activity history goes in a single free-text notes column, newest line at the top. If you genuinely need a full activity log you have outgrown a spreadsheet, and that is a real answer rather than a failure.
The nine columns
| Column | What goes in it | Why it earns its place |
|---|---|---|
| Name | Person and company | Identity. |
| Source | Referral, Google, LinkedIn, event, cold outreach | The only way to find out which channels produce revenue rather than traffic. |
| Date in | Date the enquiry arrived | Gives you cycle length and a cohort to measure conversion against. |
| Stage | One of a fixed list (below) | Every conversion rate on the sheet is a COUNTIFS on this column. |
| Value | Expected first-year or project value | Turns a pipeline count into a pipeline number. |
| Next action | What you will do next, in four words | Stops "follow up" meaning nothing. |
| Next action date | A date, never blank on a live lead | This is the column the whole system runs on. |
| Closed date | Filled on Won or Lost | Lets you measure a quarter rather than all history. |
| Notes | Free text, newest first | Context, so the next conversation is not a cold start. |
Set Stage as a data validation list so it cannot be typed freehand — "Proposal", "proposal" and "Proposal sent" are three different values to COUNTIFS, and one stray spelling silently breaks every number below. Format the range as a table (Ctrl+T) so new rows are picked up by the formulas automatically.
Five stages, defined by evidence
Stages are only useful if each one has an observable trigger. Five is enough for almost any small business:
- New — enquiry received, not yet spoken to.
- Qualified — you have established they have the problem, the budget and the authority. A conversation happened.
- Proposal — a written price is with them.
- Won — signed or paid.
- Lost — they said no, or ninety days passed with no reply.
That last clause matters. Without a rule that closes silent leads, the pipeline fills with corpses and every conversion rate you calculate is wrong in a direction that flatters you.
The conversion sums, worked
Take one quarter's cohort: 120 enquiries in.
| Stage | Count | Conversion from previous | From the top |
|---|---|---|---|
| Enquiries | 120 | — | 100% |
| Qualified | 54 | 45.0% | 45.0% |
| Proposal sent | 21 | 38.9% | 17.5% |
| Won | 7 | 33.3% | 5.8% |
At an average deal value of £3,400, those 120 enquiries produced 7 × £3,400 = £23,800.
Value per enquiry = £23,800 ÷ 120 = £198.33
That single figure is what the whole spreadsheet exists to produce. It is the most you could pay to generate one more enquiry before the channel stops making money — and if delivery costs you 40% of revenue, the real ceiling is 60% of it, or £119 per enquiry. Everything about how much marketing is worth doing becomes arithmetic at that point instead of an argument.
Read the funnel too. Qualified-to-proposal at 38.9% is the narrowest step here: 33 qualified leads never received a price. Getting nine more proposals out of the same 54 qualified leads, at the same 33.3% close rate, is three more wins — £10,200 — with no extra enquiries at all.
The formulas
With the range formatted as a table named Leads:
- Count a stage:
=COUNTIFS(Leads[Stage],"Proposal") - Count a stage within a quarter:
=COUNTIFS(Leads[Stage],"Won",Leads[Closed date],">="&DATE(2026,7,1),Leads[Closed date],"<"&DATE(2026,10,1)) - Value sitting at a stage:
=SUMIFS(Leads[Value],Leads[Stage],"Proposal") - Wins by source:
=COUNTIFS(Leads[Source],"Referral",Leads[Stage],"Won") - Overdue flag, as a tenth column:
=IF(AND([@[Next action date]]<>"",[@[Next action date]]<TODAY(),[@Stage]<>"Won",[@Stage]<>"Lost"),"OVERDUE","")
Conditional-format that last column red and it is the only thing you need to look at each morning. In Google Sheets the same formulas work with ranges in place of table references — =COUNTIFS(D:D,"Proposal").
Weighted pipeline, from your own history
"We have £68,000 in the pipeline" is not a forecast. Weight each stage by the conversion rate your own sheet measured, not by a percentage someone assigned by feel:
Weighted value = value at stage × that stage's historical close rate
From the cohort above, a lead at Proposal has a 33.3% chance of closing. Twelve open proposals at £3,400 each:
12 × £3,400 × 0.333 = £13,586
Do the same for each earlier stage, weighting by the rest of the path rather than a single step. A lead at Qualified still has two gates to clear, so its weight is 38.9% × 33.3% = 12.9%; twenty open qualified leads at £3,400 are worth 20 × £3,400 × 0.129 = £8,772. Add the bands: £13,586 + £8,772 = £22,358, against the £108,800 the raw totals would claim. It is a smaller, duller number, and it is roughly right, which the raw total never is.
The weekly review, and when to stop using a spreadsheet
Twenty minutes, same time each week, four passes: sort by next action date and clear anything overdue; move to Lost anything past your silence rule; re-read the next action text on every live lead and rewrite any that says "follow up"; then look at the counts by stage and note which step narrowed. That is the whole rhythm, and it is the part that produces the numbers — a tracker nobody updates measures nothing.
Three honest signals that a spreadsheet has stopped being the right tool: more than one person needs to edit it at the same time; you need the activity history rather than the current state; or you want emails and calls logged automatically. Until then a sheet with nine columns beats a CRM nobody fills in, because the data quality problem is the same in both and the spreadsheet costs nothing.
Arithmetic and general information only — not financial, tax, legal or investment advice. Your own figures, and your own accountant, decide what any of this means for you.
Do this sum for free
free break-even calculator — once you know what an enquiry is worth, it tells you how many closed deals a month actually cover your fixed costs. It runs in your browser and nothing you type is sent anywhere.
If you would rather not build it yourself
The Lead Tracker Spreadsheet ($99) is a built lead tracker with stage-by-stage conversion maths already wired up, follow-up sequences, a weighted pipeline view and a weekly review rhythm. One-time purchase, instant download.
Frequently asked questions
What columns should a lead tracker spreadsheet have?
Nine: name, source, date in, stage, value, next action, next action date, closed date and notes. One row per lead, never one row per activity, and stage set as a data validation list so the conversion formulas cannot be broken by a typo.
How do I calculate conversion rates in Excel?
Use COUNTIFS on the stage column and divide one stage by the previous one. For example =COUNTIFS(Leads[Stage],"Proposal")/COUNTIFS(Leads[Stage],"Qualified"). Restrict it to a period by adding the closed date column as a second and third criteria pair.
What is an enquiry actually worth?
Revenue won from a cohort ÷ enquiries in that cohort. In the worked example, 120 enquiries produced 7 wins at £3,400 — £23,800, or £198.33 per enquiry. If delivery costs 40% of revenue, the most you can pay for one more enquiry before it stops making money is 60% of that, about £119.
How should I weight my pipeline?
Multiply the value sitting at each stage by that stage's own historical close rate, measured from your sheet rather than assigned by feel. Twelve open proposals at £3,400 with a 33.3% proposal-to-win rate is 12 × £3,400 × 0.333 = £13,586 of expected revenue, not £40,800.
How do I stop leads going cold in a spreadsheet?
Give every live lead a next action date that is never blank, add an OVERDUE formula column comparing it to TODAY(), conditional-format it red, and sort by that column every morning. Then set a rule — ninety days of silence, say — that automatically moves a lead to Lost, so the pipeline stays honest.
When should I move from a spreadsheet to a CRM?
When more than one person needs to edit at the same time, when you need the full activity history rather than the current state, or when you want emails and calls logged automatically. Before that, a nine-column sheet that gets updated beats a CRM that does not.