A dedicated CRM is overkill for a five-person team closing a dozen deals a month. A Google Sheet tracks the same pipeline — stages, weighted forecasts, win rates — for free, as long as you build it deliberately instead of letting it sprawl into a wall of unsorted rows. This builds a sales tracker in four parts: a clean deal log, the formulas that turn it into a forecast, a dashboard that summarizes itself, and the formatting that flags deals going stale.
Set up the deal log
Everything starts with one tab — call it Sales Log — where each row is a deal and each column is a single fact about it. The temptation is to track everything: lead source, last contact, next step, industry, a notes field that becomes a novel. Resist it. Every extra column is one more thing to fill in, and a tracker people don’t fill in is just an empty grid. The columns below cover forecasting, ownership, and follow-up, which is most of what a small team actually acts on.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Deal | Value | Stage | Probability | Close date |
| 2 | Acme renewal | 8000 | Proposal | 60% | 2026-05-30 |
| 3 | Beta Corp | 4500 | Negotiation | 80% | 2026-05-22 |
Lock the stage column so it stays consistent
Stage only works as a filter and a grouping key if everyone types it identically — “Proposal,” not “proposal” or “Proposal sent.” One stray variant and your stage counts split in two. Make the column a dropdown with data validation so it can only hold the stages you defined: Lead, Qualified, Proposal, Negotiation, Closed Won, Closed Lost. Do the same for Owner once more than one person works the pipeline. Clean inputs here are what make every formula downstream trustworthy — garbage in the Stage column means garbage in every count and forecast that reads it.
One column people skip until they need it: a date-entered or last-touched field. It costs nothing to fill and answers the question every pipeline review asks — which deals have gone quiet? A deal in Negotiation that hasn’t moved in three weeks is a different risk than one updated yesterday, and without a timestamp you’re guessing. Add it from the start; you’ll be glad it has history the first time you want to sort by it and chase the stalled deals.
The formulas that turn a list into a forecast
A list of deals isn’t a forecast until the numbers do some work. Three formulas carry most of the weight, and none of them is complicated — the value is in what they reveal, not how clever they are.
Weighted pipeline value
=ARRAYFORMULA(IF(B2:B="", "", B2:B * D2:D))Multiply each deal’s value by its probability to get its weighted contribution: an $8,000 deal at 60% is worth $4,800 in a realistic forecast, not the full eight grand you’d love to book. Spreading it down the column with ARRAYFORMULA means every new deal is weighted the moment you enter it. Sum that column and you have a forecast that quietly discounts the long shots — far more honest than adding up raw deal values and hoping.
Deal count by stage
=COUNTIF(C2:C, "Proposal")Count how many deals sit in each stage to see where the pipeline is bunching up. A dozen deals stuck in “Proposal” is a different problem than a dozen in “Negotiation” — the first says your proposals aren’t landing, the second says your closing needs help. The count points you at the conversation worth having this week.
Win rate
=COUNTIF(C2:C, "Closed Won") / (COUNTIF(C2:C, "Closed Won") + COUNTIF(C2:C, "Closed Lost"))Closed-won divided by all closed deals gives your win rate — the single number that tells you whether the pipeline math is healthy. Format the cell as a percentage and it becomes the headline metric for the whole tracker. Watch it over months, not days: a win rate based on three closed deals is noise, but across thirty it’s the truest signal you have about whether the pipeline converts.
Average won deal size
=AVERAGEIF(C2:C, "Closed Won", B2:B)The average value of the deals you actually close tells you whether to chase more deals or bigger ones. If the win rate is healthy but revenue is flat, average deal size is usually where the answer hides — you’re winning, but winning small. Pair it with the closed-won count and you can see at a glance whether a strong month came from volume or from one large contract, which are two very different things to try to repeat.
A dashboard tab that updates itself
Keep the log for data entry and put the summary on its own Dashboard tab, so the people who only want the numbers never have to scroll through raw rows. The dashboard is just the formulas above, pointed at the log and arranged for reading rather than editing.
=SUMIF('Sales Log'!C:C, "Closed Won", 'Sales Log'!B:B)That totals won revenue by referencing the log tab directly. Build a small block of headline figures — total pipeline, weighted forecast, win rate, and a deal count per stage — and every one recalculates the instant the log changes. Then add a chart: select the stage-count range, go to Insert → Chart, and pick a column chart to show where deals cluster across the pipeline. Because the chart reads the same live formulas, it never needs a manual refresh; it redraws itself as deals move from stage to stage.
What goes on the dashboard is a judgment call worth making once. A useful default: a row of headline numbers at the top — pipeline total, weighted forecast, win rate — a stage-count chart below them, and a short list of the deals closing this month pulled with a FILTER. Resist adding every metric you can compute. A dashboard showing five numbers people act on beats one showing twenty they skim past, and the clutter actively hides the figures that matter. The same discipline that keeps the log clean keeps the dashboard useful: show what drives a decision, and leave everything else one click away in the log.
'Sales Log'!B:B) in dashboard formulas, never fixed ranges like B2:B100. New deals entered below row 100 then count automatically, instead of silently falling outside a range nobody remembered to extend.
Flag deals that need attention
A tracker earns its keep when it tells you what to do without being read line by line. Conditional formatting does exactly that — it colors rows based on their own data, so the deals that need action announce themselves.
- Select the close-date column on the Sales Log.
- Open Format → Conditional formatting.
- Choose “Date is before” and a custom value one week from today.
- Pick a fill color, click Done — deals closing this week now light up.
The same approach surfaces stale deals: a custom-formula rule that highlights any row whose close date is already past but whose stage still isn’t “Closed Won” or “Closed Lost.” Those are the deals slipping through the cracks — promised last month, never resolved. For the full range of rules and custom formulas, see the advanced conditional formatting techniques guide, and Google’s own conditional formatting documentation walks through every menu option.
Keep it clean as the pipeline grows
A tracker that works in month one and rots by month six is worse than no tracker, because people stop trusting the numbers and quietly go back to guessing. A few habits keep it reliable long after the novelty wears off.
- ✓ Stage and Owner are dropdowns, never free text
- ✓ Dashboard formulas reference whole columns so new rows count automatically
- ✓ Weighted-value and summary columns use ARRAYFORMULA, so there’s nothing to drag down
- ✓ Closed deals stay in the log — they’re what win rate is calculated from
- ✓ One person owns the tracker’s structure, even if everyone edits rows
If deals arrive from a web form, feed them in with an array formula so each submission lands in the log automatically, with no copy-paste step to forget. The less manual upkeep the tracker needs, the longer it stays accurate — and an accurate tracker is the only kind anyone opens twice.
When the sheet is enough — and when it isn’t
For a small team, this sheet does what a starter CRM does and costs nothing: a clean log, a forecast that discounts itself by probability, a dashboard that never goes stale, and formatting that surfaces the deals about to slip. Build the four parts once, keep the inputs disciplined, and the tracker does the watching for you — so your time goes to closing deals instead of maintaining the sheet that tracks them. And when the team finally outgrows it, you’ll move to a CRM already knowing exactly which numbers matter — because you built every one of them by hand.