A sales pipeline tracker in Excel works best when it stays honest about what it is — a lightweight visibility tool, not a full CRM. For a small team without HubSpot or Salesforce, a single sheet with one row per deal and a few well-chosen formulas is the quickest way to know which deals are moving, which are stuck, and which next action is overdue.
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreThis tutorial builds the tracker from scratch: a deals Table with a small stage list, calculated columns for weighted value and days-in-stage, conditional formatting that surfaces stale deals automatically, and a summary panel at the top that reads a forecast straight from the rows below. By the end you will have a working pipeline you can run a 15-minute weekly review against without opening any other tool.
Requires Microsoft 365 (desktop or web). The summary uses XLOOKUP, SUMIFS, FILTER, and COUNTIFS — all available there. Excel 2019 alternatives are noted where each modern function appears, so a 2019 reader can still build the same tracker with INDEX/MATCH and a manual refresh on the live views. For broader Excel context see the Excel formulas hub.
Setup is about 30–40 minutes for the first tracker. After that, cloning the sheet for the next quarter is a 5-minute job — every formula references the deals Table by name, so blanking the rows resets the tracker without touching any of the logic.
What we are building
Two sheets, in this order:
- Stages — a tiny lookup table with seven rows: Lead, Qualified, Discovery, Proposal, Negotiation, Closed Won, Closed Lost. Each stage has a probability (5%, 20%, 35%, 60%, 80%, 100%, 0%). The whole forecast hinges on this table.
- Pipeline — the working sheet. Top: summary panel (counts by stage, total pipeline value, weighted forecast, deals needing follow-up). Middle: live "needs next action" filtered view. Bottom:
tblDeals— the only sheet anyone edits.
The discipline is the same as in the project tracker: nothing on the summary or the filtered views is hand-edited. Every number flows up from the deal rows through formulas. If a number looks wrong, the fix is in a deal row, not the summary cell.
Step 1 — Build the Stages lookup table
On a new sheet named Stages, in cells A1:B8, type:
A1: Stage B1: Probability
A2: Lead B2: 5%
A3: Qualified B3: 20%
A4: Discovery B4: 35%
A5: Proposal B5: 60%
A6: Negotiation B6: 80%
A7: Closed Won B7: 100%
A8: Closed Lost B8: 0%Click inside A1:B8, press Ctrl+T, confirm "My table has headers". Name the Table tblStages in the Table Design tab.
Why these seven and these probabilities? They mirror the standard B2B funnel that most enterprise CRMs ship with. Probabilities are calibrated against industry benchmarks for win rate by stage — 5% for a cold lead, 20% once qualified, climbing to 80% in active negotiation. Adjust them for your specific business after you have closed 30+ deals and have your own conversion data; until then, these are a sane starting point.
Beginner pitfall. Inventing 12 stages that map to internal handoff steps ("SDR-passed", "AE-accepted", "PoC-running"…). Every extra stage halves the deals-per-stage count and the forecast becomes statistically meaningless. Seven stages, no more, until you have a real reason to split one.
Step 2 — Build the Deals Table
On a sheet called Pipeline, leave rows 1–18 empty (the summary will go there in Step 5). In row 20, type the headers across A–J:
A20: Deal B20: Account C20: Owner D20: Stage
E20: Value F20: Probability G20: Weighted H20: Expected close
I20: Last activity J20: Next actionAdd one sample row in row 21. Select A20:J21, press Ctrl+T, then in Table Design name it tblDeals.
Add data validation on the Stage column. Click the column header → Data → Data Validation → Allow: List → Source: =tblStages[Stage] → OK. Now Stage is a dropdown sourced live from the Stages table — add or rename a stage there and every deal cell's dropdown updates automatically.
Format Value (column E) as Currency. Format Expected close (column H) and Last activity (column I) as Short Date.
Step 3 — Add the calculated columns
Three columns in tblDeals are formula-driven, not typed. Click into the first data cell of each and type the formula — Excel auto-fills it down the whole column because this is a Table.
F (Probability) — looks up the stage's probability from tblStages:
=XLOOKUP([@Stage], tblStages[Stage], tblStages[Probability], 0)The 0 at the end is the if-not-found value, so a deal with a missing or mistyped stage shows zero probability rather than #N/A. Excel 2019 fallback: =IFERROR(INDEX(tblStages[Probability], MATCH([@Stage], tblStages[Stage], 0)), 0).
G (Weighted) — value × probability:
=[@Value] * [@Probability]This is what the forecast actually adds up. A £40,000 deal at Discovery (35%) contributes £14,000 to the forecast, not £40,000.
Add one more helper column. Insert a new column after Last activity (call it K, header Days idle):
=IF([@[Last activity]]="", "", TODAY() - [@[Last activity]])Days since the last activity. Anything over 14 days on an active stage is a deal that is silently dying. The conditional formatting in Step 4 surfaces these without you having to look.
Step 4 — Conditional formatting for stage colours and stale deals
Select the body of tblDeals (click inside, then Ctrl+A once). Home → Conditional Formatting → New Rule → "Use a formula" for each:
- Stage = Closed Won → green strikethrough. Formula:
=$D21="Closed Won". Format: light green fill, grey text, strikethrough. Replace 21 with your first data row. - Stage = Closed Lost → grey fade. Formula:
=$D21="Closed Lost". Format: grey text on white fill. - Negotiation stage → bold blue. Formula:
=$D21="Negotiation". Format: bold, blue text. The deal is close — make it visible. - Stale active deal (idle > 14 days, not closed) → red border. Formula:
=AND($K21>14, $D21<>"Closed Won", $D21<>"Closed Lost"). Format: thick red left-border. - Slipped close date (Expected close in past, not closed) → amber fill. Formula:
=AND($H21<TODAY(), $D21<>"Closed Won", $D21<>"Closed Lost", $H21<>""). Format: amber fill.
The visual rules stack: a stale Negotiation deal whose close date already slipped ends up bold blue, with an amber fill and a red left-border. That is exactly the deal you want to glance at on Monday morning.
Real-world scenario. A 4-person agency selling £20k–£80k retainers used to argue every Monday about which deals to call. After adding the stale-deal red-border rule, the call list became visually obvious — they ran the standup off the sheet, picked the 5 reddest rows, made the calls in the next hour. Pipeline conversion improved measurably (their close rate on Discovery-stage deals went from 28% to 41% over a quarter) because deals stopped silently dying.
Step 5 — Build the summary panel
Move to the top of the Pipeline sheet. Lay out the summary in rows 1–14. Use tblDeals structured references throughout.
A1: PIPELINE — Q2 2026
A3: Counts by stage A8: Pipeline value
B3: =COUNTIFS(tblDeals[Stage], "Lead") B8: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Lead")
B4: =COUNTIFS(tblDeals[Stage], "Qualified") B9: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Qualified")
B5: =COUNTIFS(tblDeals[Stage], "Discovery") B10: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Discovery")
B6: =COUNTIFS(tblDeals[Stage], "Proposal") B11: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Proposal")
B7: =COUNTIFS(tblDeals[Stage], "Negotiation") B12: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Negotiation")Now the headline numbers — the four cells that actually answer the questions a sales lead asks:
D1: Total open value E1: =SUMIFS(tblDeals[Value], tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost")
D2: Weighted forecast E2: =LET(weightedOpen, SUMIFS(tblDeals[Weighted], tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost"),
wonValue, SUMIFS(tblDeals[Value], tblDeals[Stage], "Closed Won"),
weightedOpen + wonValue)
D3: Closed this quarter E3: =SUMIFS(tblDeals[Value], tblDeals[Stage], "Closed Won")
D4: Win rate E4: =LET(won, COUNTIFS(tblDeals[Stage], "Closed Won"),
lost, COUNTIFS(tblDeals[Stage], "Closed Lost"),
won/(won+lost))Format E1, E2, E3 as currency; E4 as percentage.
Why LET on E2 and E4? LET names sub-expressions inside one formula. On E4 the win-rate calculation calls COUNTIFS(..., "Closed Won") twice — once for the numerator and once inside the denominator. Naming it won means Excel evaluates that COUNTIFS once and reuses the result, and the formula reads as won/(won+lost) instead of a 130-character string. Same logic on E2: weightedOpen and wonValue document the two parts of the forecast. Without LET both still work; LET is the readability tax-cut, M365 only.
Why two values for forecast? Total open value is the maximum possible if every open deal closes. Weighted forecast is the realistic number — it sums each deal's value × stage probability, plus everything already in Closed Won. The gap between them is your conversion risk; the bigger the gap, the more your pipeline depends on early-stage deals converting.
Add three risk indicators:
D6: Stale deals (>14d) E6: =COUNTIFS(tblDeals[Days idle], ">14", tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost")
D7: Slipped close dates E7: =LET(past, tblDeals[Expected close]<TODAY(),
open, (tblDeals[Stage]<>"Closed Won")*(tblDeals[Stage]<>"Closed Lost"),
hasDate, tblDeals[Expected close]<>"",
SUMPRODUCT(past*open*hasDate))
D8: Missing next action E8: =COUNTIFS(tblDeals[Next action], "")Conditional-format E6:E8 with red text when value > 0. Now the summary tells you in one glance how big the pipeline is, what it is realistically worth, and which three risks are eating it.
Step 6 — Add a "needs next action" live view
Below the summary, in row 16, add a live filtered view of deals that need attention right now. The criterion: open deals whose Last activity is more than 7 days old or whose Next action is empty.
A16: NEEDS NEXT ACTION
A17: =LET(stale, tblDeals[Days idle]>7,
missing, tblDeals[Next action]="",
open, (tblDeals[Stage]<>"Closed Won")*(tblDeals[Stage]<>"Closed Lost"),
FILTER(tblDeals[[Deal]:[Owner]], (stale+missing)*open, "Inbox zero — every deal has a next action"))The result spills under row 17. The + between the two criteria acts as OR; the * outside acts as AND. So this filter says: "open deal AND (idle > 7 days OR no next action)". Excel 2019 fallback: a manual PivotTable filtered to Open stages with a slicer for Days idle bucket.
Step 7 — Worked example: 12 deals, mid-quarter
Drop these into tblDeals to test (assume today is 2026-04-30):
Deal Account Owner Stage Value Expected Last activity Next action
Acme website rebuild Acme Ltd Sara Negotiation 45000 2026-05-15 2026-04-28 Send revised SOW
Beta SaaS pilot Beta Co Marcus Discovery 22000 2026-06-30 2026-04-22 Discovery call #2
Cresta annual rene. Cresta Plc Sara Closed Won 68000 2026-04-15 2026-04-15
Delta integration Delta Inc Priya Proposal 31000 2026-04-25 2026-04-20 Chase signature
Eden audit Eden Studio Sara Qualified 8500 2026-05-30 2026-04-10
Fern PoC Fern Ltd Priya Discovery 14000 2026-06-15 2026-04-05 Schedule technical call
Globex retainer Globex SE Marcus Lead 18000 2026-07-01
Helix migration Helix AG Sara Negotiation 52000 2026-05-08 2026-04-29 Legal review
Iris workshop Iris Ltd Marcus Closed Lost 6000 2026-04-10 2026-04-12
Juno onboarding Juno Co Priya Qualified 11500 2026-05-20 2026-04-25 Send pricing
Kite annual contract Kite Ltd Sara Closed Won 24000 2026-04-22 2026-04-22
Linnet migration Linnet plc Marcus Negotiation 38000 2026-04-18 2026-04-15 Resend contractExpected summary numbers:
- Counts: Lead 1, Qualified 2, Discovery 2, Proposal 1, Negotiation 3, Closed Won 2, Closed Lost 1.
- Total open value (B8 sums excluded): 18,000 + 8,500 + 11,500 + 22,000 + 14,000 + 31,000 + 45,000 + 52,000 + 38,000 = £240,000.
- Weighted forecast = (18000×5% + 8500×20% + 11500×20% + 22000×35% + 14000×35% + 31000×60% + 45000×80% + 52000×80% + 38000×80%) + (68000 + 24000 won) = 144,100 + 92,000 = £236,100.
- Closed this quarter: £92,000.
- Win rate: 2 won / (2 won + 1 lost) = 66.7%.
- Stale deals (>14 days idle): Eden audit (20d), Fern PoC (25d), Linnet migration (15d) = 3.
- Slipped close dates (Expected before today, still open): Delta (25 Apr), Linnet (18 Apr) = 2 deals, sum £69,000.
- Missing next action: Cresta (won, irrelevant), Eden audit, Globex retainer, Iris workshop (lost), Kite (won) — among open deals, 2 (Eden, Globex). The COUNTIFS counts all 5; you can refine the formula to exclude closed:
=COUNTIFS(tblDeals[Next action], "", tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost")= 2.
If your sheet matches these to within rounding, every formula is wired correctly. Now mark Linnet as Closed Won and watch the slipped-dates count drop to 1, the closed-this-quarter total jump to £130,000, and the weighted forecast recalculate — without touching the summary panel.
The full formula reference
| Where | Formula | What it does |
|---|---|---|
| Deals F Probability | =XLOOKUP([@Stage], tblStages[Stage], tblStages[Probability], 0) | Pulls stage probability from the Stages table |
| Deals G Weighted | =[@Value] * [@Probability] | Probability-weighted contribution to the forecast |
| Deals K Days idle | =IF([@[Last activity]]="", "", TODAY() - [@[Last activity]]) | Days since last touch — feeds the stale-deal rule |
| Excel 2019 Probability fallback | =IFERROR(INDEX(tblStages[Probability], MATCH([@Stage], tblStages[Stage], 0)), 0) | Pre-XLOOKUP equivalent |
| B3 Stage count | =COUNTIFS(tblDeals[Stage], "Lead") | Deal count per stage (repeat for each) |
| B8 Stage value | =SUMIFS(tblDeals[Value], tblDeals[Stage], "Lead") | Total deal value per stage |
| E1 Total open value | =SUMIFS(tblDeals[Value], tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost") | Maximum possible if every open deal closes |
| E2 Weighted forecast | =LET(weightedOpen, SUMIFS(tblDeals[Weighted], tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost"), wonValue, SUMIFS(tblDeals[Value], tblDeals[Stage], "Closed Won"), weightedOpen + wonValue) | Realistic forecast: weighted open + actual won; LET names the two halves |
| E3 Closed this quarter | =SUMIFS(tblDeals[Value], tblDeals[Stage], "Closed Won") | Booked revenue |
| E4 Win rate | =LET(won, COUNTIFS(tblDeals[Stage], "Closed Won"), lost, COUNTIFS(tblDeals[Stage], "Closed Lost"), won/(won+lost)) | Closed-won as a share of all closed deals; LET avoids the duplicate COUNTIFS |
| E6 Stale deals | =COUNTIFS(tblDeals[Days idle], ">14", tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost") | Open deals with no activity in 14+ days |
| E7 Slipped close dates | =LET(past, tblDeals[Expected close]<TODAY(), open, (tblDeals[Stage]<>"Closed Won")*(tblDeals[Stage]<>"Closed Lost"), hasDate, tblDeals[Expected close]<>"", SUMPRODUCT(past*open*hasDate)) | Open deals whose expected close has already passed; LET names the three filter parts |
| E8 Missing next action | =COUNTIFS(tblDeals[Next action], "", tblDeals[Stage], "<>Closed Won", tblDeals[Stage], "<>Closed Lost") | Open deals with no defined next step |
| A17 Needs-action view | =LET(stale, tblDeals[Days idle]>7, missing, tblDeals[Next action]="", open, (tblDeals[Stage]<>"Closed Won")*(tblDeals[Stage]<>"Closed Lost"), FILTER(tblDeals[[Deal]:[Owner]], (stale+missing)*open, "Inbox zero — every deal has a next action")) | Live spill of deals needing attention; LET names the OR + AND halves of the filter |
| CF Stale deal | =AND($K21>14, $D21<>"Closed Won", $D21<>"Closed Lost") | Red-border on rotting deals |
| CF Slipped close | =AND($H21<TODAY(), $D21<>"Closed Won", $D21<>"Closed Lost", $H21<>"") | Amber fill on past-due open deals |
Variations: customise for your sales motion
The skeleton (tblStages + tblDeals + summary panel + filtered view) stays identical. What changes is the stage list, the probabilities, and which extra metric matters most.
| Use case | Stages (with probabilities) | Extra summary metric |
|---|---|---|
| B2B SaaS / enterprise | Lead 5, Qualified 20, Discovery 35, Proposal 60, Negotiation 80, Closed Won 100, Closed Lost 0 | Average deal cycle time (days from Lead to Won) |
| Agency / consulting | Inbound 10, Brief 30, Proposal 55, Negotiation 75, Closed Won 100, Closed Lost 0 | Hours required (capacity vs pipeline) |
| Freelancer | Enquiry 15, Scoped 40, Quote sent 65, Booked 100, Declined 0 | Booked weeks vs available weeks |
| Real estate / brokerage | Listed 10, Viewed 30, Offer received 55, Under contract 80, Closed 100, Withdrawn 0 | Median days on market |
| Recruiting / staffing | Sourced 5, Screened 20, Submitted 40, Interview 60, Offer 85, Placed 100, Rejected 0 | Submitted-to-placed conversion |
| Multi-rep team (5+) | Standard B2B stages | Pipeline by Owner — add a slicer on Owner |
For multi-rep teams, add a slicer (Table Design → Insert Slicer → Owner) and the same summary works per-rep with a click. For longer cycles where stage probabilities drift over time, replace the static tblStages probabilities with a small calibration sheet that learns from your closed-won history — but only after you have 50+ closed deals to learn from.
Common mistakes
- Inventing 12 stages. Each split halves the deal count per stage and the forecast becomes noise. Seven stages, no more.
- Treating Probability as fixed industry truth. The defaults are starting points. After 30+ closed deals, calibrate from your actual conversion data.
- Using the same Probability column for hand-overrides. If a rep can edit Probability for one specific deal, the lookup formula is now broken on that row. Add a separate "Confidence override" column if you need that, and pick max(stage probability, override) in the Weighted column.
- Ignoring Last activity. Without it, you cannot detect rotting deals, and the weekly review becomes a guessing game.
- Counting Closed Lost in the win rate denominator only. A team with 10 wins and 0 losses has an undefined win rate, not 100%. The formula's
(won + lost)denominator handles this — but if you change it, watch out. - Treating the tracker as a CRM. If you find yourself adding contact emails, call notes, attachments, and email history, you have outgrown Excel. Move to HubSpot, Pipedrive, or Folk.
Troubleshooting
- Probability column shows 0 for valid stages. Trailing space in either the Stages source ("Lead ") or the dropdown. Re-enter the Stages rows cleanly; existing typos: select the Stage column → Find & Replace → trim each value.
- Weighted forecast is wildly low. The Stages table's Probability column is formatted as percentage but the underlying values are 5 / 20 / 35 instead of 0.05 / 0.20 / 0.35. Re-enter as
5%(Excel stores 0.05) rather than typing 5. - FILTER returns #CALC!. Empty array — add a fallback like
"Inbox zero"as the third argument. - Slipped close count includes deals that have no Expected close date. The SUMPRODUCT criteria includes
(tblDeals[Expected close]<>""); check yours has that term. - Stale-deal rule colours every row red. The Days idle column is text instead of a number — usually because Last activity was typed as text. Reformat Last activity as Short Date and retype the stale rows.
- Summary numbers do not change when I add a deal. The new deal landed below the Table boundary because you typed past the auto-extend zone, then pressed Tab. Click in the Table → Table Design → Resize Table → re-select the full range.
When this is not the right tool
- More than ~150 active deals or ~5 reps. Performance is fine; ergonomics are not. Move to HubSpot, Pipedrive, Folk, or Salesforce.
- Email tracking, call recording, automation. Excel does not own these workflows. Use a CRM with native email integration.
- Multi-stage lead routing or territory rules. CRMs handle this with workflow builders; Excel cannot.
- Compliance / audit trail. If a deal's history needs to be tamper-evident, Excel is not enough.
- Internal project work, not external sales. Use the project tracker instead — different schema, different metrics.
Related tutorials on this site
- How to Build a Project Tracker in Excel Without Turning It Into a Mess — same Table-driven discipline, different schema for internal work.
- Excel + AI for Sales Ops: Pipeline Cleanup, Forecasts, and Territory Reporting — the AI-augmented version of this workflow.
- How to Build an Inventory Tracker in Excel That Stays Maintainable — sibling pattern for stock-side workflows.
- How to Build an Interactive Dashboard in Excel (No VBA) — when the summary panel needs to grow into a multi-chart dashboard.
- Excel Tables: Structured References, Growth, and Cleaner Models — why
tblDeals-style references are non-negotiable. - How to Add a Dropdown List in Excel Using Data Validation — deeper dive on Step 2.
- Conditional Formatting Tips That Actually Save Time — more patterns for Step 4.
- The Complete Excel Guide 2026 — hub for every Excel tutorial on the site.