This article covers how to build a pipeline that generates weighted forecasts, tracks conversion by stage, and stays current without manual babysitting.
What Your Pipeline Sheet Needs (And What It Doesn't)
Most pipeline templates online are either too sparse (just a list of names) or bloated with fields nobody fills in. As of April 2026, the most functional single-sheet pipelines use this column structure:
| Column | Header | Notes |
|---|---|---|
| A | Lead Name | Company or contact |
| B | Deal Value | Raw number, no formatting in formula cells |
| C | Stage | Dropdown: Prospect / Qualified / Proposal / Negotiation / Closed Won / Closed Lost |
| D | Probability % | Auto-filled by stage (via VLOOKUP) or manual override |
| E | Weighted Value | =B2*D2 |
| F | Owner | Rep name |
| G | Close Date | Date format |
| H | Days to Close | =G2-TODAY() |
| I | Next Action | Free text |
That's 9 columns. Add more if your team actually uses them. Don't add them speculatively.
The Formulas That Matter
The pipeline view is only useful if it answers: how much will we close this quarter, and what's at risk?
Weighted pipeline total (the number your VP actually wants):
=SUMPRODUCT(B2:B200, D2:D200)
This multiplies every deal value by its probability and sums the result. A $100,000 deal at 30% probability contributes $30,000 to the forecast — same as a $30,000 deal you're certain to close.
Pipeline by stage (so you know where deals are stuck):
=SUMIF(C2:C200, "Proposal", B2:B200)
Run this for each stage. If you have 40 deals in Proposal but 3 in Negotiation, something's breaking at the demo-to-close step.
Conversion rate between stages (the metric most teams skip):
=COUNTIF(C2:C200,"Negotiation") / COUNTIF(C2:C200,"Proposal")
A healthy B2B pipeline typically converts 50–70% from Proposal to Negotiation and 60–80% from Negotiation to Closed Won. If you're not measuring this, you're guessing.
Auto-Filling Probability by Stage
The most common time sink in manual pipelines is updating probability when a deal moves stages. Fix it with a lookup table.
Create a small table somewhere in the sheet (say, K1:L6):
| Stage | Default Probability |
|---|---|
| Prospect | 10% |
| Qualified | 25% |
| Proposal | 40% |
| Negotiation | 65% |
| Closed Won | 100% |
| Closed Lost | 0% |
Then in column D:
=IFERROR(VLOOKUP(C2, $K$1:$L$6, 2, FALSE), 0)
Reps can override the cell manually if a specific deal warrants it. The VLOOKUP just handles the default so nobody has to remember the numbers.
The Quarter Rollup Every Manager Asks For
Once the structure is in place, pivot tables handle the reporting layer. Insert > Pivot table, then:
- Rows: Stage
- Columns: Owner
- Values: Sum of Weighted Value
This gives you a matrix: who has what, and in what stage. A rep with $400K in Proposal and nothing in Negotiation looks good on paper but has a timing problem.
For date-based filtering (this quarter only), add a calculated column:
=IF(AND(G2>=DATE(2026,4,1), G2<=DATE(2026,6,30)), "Q2", "Other")
Then filter the pivot by that column.
Where Sheets Falls Short (And When to Actually Move to a CRM)
Google Sheets handles pipelines well up to roughly 300–400 active deals. Past that, SUMPRODUCT across thousands of rows starts dragging, and multi-user editing creates conflicts that corrupt data in ways that are genuinely hard to recover from.
The other failure mode is activity tracking. Sheets can store "next action" as a text field, but it won't remind anyone. If your team misses follow-ups because the spreadsheet doesn't ping them, that's not a spreadsheet problem — that's a CRM problem.
For teams under 20 reps with fewer than 300 deals, the CRM argument usually comes down to process theater rather than actual need. According to HubSpot's 2024 State of Sales report, 35% of sales reps still manage their pipeline primarily in spreadsheets — not because they don't know better, but because the flexibility often outweighs the overhead of a formal CRM.
Keeping the Pipeline Current Without Manual Updates
The actual cost of a pipeline tracker isn't building it — it's keeping it up to date. Reps forget to move deals, managers nag in Slack, and the sheet slowly becomes fiction.
One approach that works: connect HubSpot or Salesforce directly to Sheets and sync deals automatically. ModelMonkey can pull deal data from HubSpot into a Google Sheet on demand — you ask it "get all open deals from HubSpot and organize by stage," and it builds the table. No export, no copy-paste, no CSV.
The result is a pipeline that reflects actual CRM state while staying in Sheets for all the analysis work (custom rollups, scenario models, board-ready formatting) that CRMs handle badly.
A Note on Multi-Currency Pipelines
If your team closes deals in multiple currencies — common for teams with reps in Latin America, Europe, or Southeast Asia — add a Currency column and a conversion formula:
=B2 * VLOOKUP(J2, CurrencyTable!$A$2:$B$10, 2, FALSE)
Where column J holds the currency code (USD, EUR, BRL, MXN) and CurrencyTable is a small lookup with static exchange rates you update monthly. This is obviously imprecise for financial reporting, but for pipeline forecasting in a single currency it's good enough.