Sales funnel formula in Excel or Google Sheets
Put the inputs in column B from row 2, then paste the formulas below. They work the same in Excel and Google Sheets.
| Cell | What | Value or formula | Example |
|---|---|---|---|
| B2 | Revenue target ($) | 600000 | 600000 |
| B3 | Average deal size ($) | 15000 | 15000 |
| B4 | Win rate (%) | 25 | 25 |
| B5 | Lead to opportunity rate (%) | 12 | 12 |
| B7 | Deals needed | =ROUNDUP(B2/B3,0) | 40 |
| B8 | Opportunities needed | =ROUNDUP(B7/(B4/100),0) | 160 |
| B9 | Leads needed | =ROUNDUP(B8/(B5/100),0) | 1334 |
Get the ready-made spreadsheet
The same calculator as an .xlsx file, with the formulas set up. Free: enter your email and the download starts here right away. Your email also joins Clarivoxx Insights (a weekly article and a monthly newsletter; unsubscribe in one click).
Frequently asked questions
- How many leads do I need to hit my target?
- Divide the target by deal size to get deals, deals by win rate to get opportunities, and opportunities by lead conversion to get leads. $600k ÷ $15k = 40 deals, ÷ 25% = 160 opportunities, ÷ 12% = 1,334 leads.
- Which conversion rate should I use?
- Your own rates from the last two to four quarters, per channel if you can. Industry averages give a funnel that looks fine on paper and misses in reality.
- How do I need fewer leads?
- Raise any step: a slightly higher win rate or lead-to-opportunity rate cuts the leads needed more than most extra campaigns would add.
About the author
Abdessamad Ghanem
Customer Experience & Customer Success Consultant · Founder of Clarivoxx
Abdessamad Ghanem works across Customer Support, Sales, Customer Success, Account Management and Partner Management. He writes Clarivoxx Insights for B2B SaaS professionals who own retention, adoption and customer outcomes.