A step-by-step guide to computing win rate and pipeline coverage in Excel from a CRM opportunity export: win rate by count and by value with COUNTIFS and SUMIFS, the coverage a team needs as one over its win rate, in-period pipeline from close dates, coverage per rep against target, aged pipeline removed, and the check that every deal is in exactly one status. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Win rate and pipeline coverage come from the same opportunity export and answer one question together: is there enough real pipeline to make the number. This guide gives the Excel formulas for both.
One sheet, Deals, one row per opportunity:
| Column | Content |
|---|---|
| A | Owner |
| B | Value |
| C | Status: Won, Lost or Open |
| D | Close date: actual for closed deals, expected for open |
| E | Stage |
| F | Stage entered date |
On a sheet named Calc: win-rate window start in B1; quarter start in B2; quarter end in B3; as-of date in B4. Targets per rep in a small table, rep in A10 down, target in B10 down.
=COUNTIF(Deals!C:C,"Won")+COUNTIF(Deals!C:C,"Lost")+COUNTIF(Deals!C:C,"Open")-COUNTA(Deals!C2:C50000)
Zero, or there are statuses you have not accounted for, such as "On hold" or a blank.
Won count, E1:
=COUNTIFS(Deals!C:C,"Won",Deals!D:D,">="&$B$1)
Lost count, E2: the same with "Lost". Win rate by count, E3:
=E1/(E1+E2)
By value, F1 and F2 use SUMIFS(Deals!B:B, ...) with the same criteria, and F3 is =F1/(F1+F2).
Expect the value rate to be lower. Use it for coverage.
=1/F3
A value win rate of 25 percent needs 4.0x. This replaces the borrowed 3x.
In C10, filled down beside each rep:
=SUMIFS(Deals!B:B,Deals!C:C,"Open",Deals!A:A,A10,Deals!D:D,">="&$B$2,Deals!D:D,"<="&$B$3)
Coverage, D10:
=C10/B10
Open deals with an expected close date already in the past are neither in this quarter nor real; count them separately:
=SUMIFS(Deals!B:B,Deals!C:C,"Open",Deals!A:A,A10,Deals!D:D,"<"&$B$4)
Add a column to Deals, G, days in stage:
=IF(C2="Open",Calc!$B$4-F2,"")
Build the norm per stage from won deals. If the export has the days each won deal spent in each stage, a pivot with Stage in Rows and the median is ideal; Excel pivots do not offer median, so in 365:
=MEDIAN(FILTER(Deals!G:G,(Deals!E:E="Proposal")*(Deals!C:C="Open")))
is a fallback using open deals, clearly labelled. Put each stage's norm in a small table, look it up into column H with XLOOKUP(E2,stages,norms), and flag aged deals in I:
=IF(AND(C2="Open",G2>2*H2),"Aged","")
Aged in-period value per rep:
=SUMIFS(Deals!B:B,Deals!C:C,"Open",Deals!A:A,A10,Deals!I:I,"Aged",Deals!D:D,">="&$B$2,Deals!D:D,"<="&$B$3)
Coverage excluding aged is (C10 − aged)/B10. Show it beside raw coverage and beside coverage needed. Three numbers per rep.
Owner in Rows, Status in Columns, Value in Values, Close date as a filter for the quarter. Open over target is coverage; won over won plus lost, on a second pivot filtered to the trailing year, is win rate. The pivot is faster for a look and harder to audit for a forecast call.
Win rate on marked outcomes only. If losses are rarely marked, the rate is inflated. Treat aged open deals as lost in a second version and show both.
Next quarter's deals in this quarter's coverage. The close-date criteria in Step 4 are the whole point.
Stage mismatch. Win rate computed on all opportunities, coverage counted from proposal stage onward, or the reverse. Filter both the same way.
Overwritten exports. Last week's pipeline is gone, so coverage cannot be tracked through the quarter. Save each week's export with its date.
A one-off pipeline review is a good spreadsheet job. Weekly, per rep, with saved snapshots and the stage norms recomputed from history, is where it strains; see the four signs a spreadsheet is no longer enough. For what the numbers should be, see what is a good win rate and what is a good pipeline coverage ratio; for the arithmetic by hand, pipeline coverage on ten deals. Covirage computes the same figures from the same export every week, with the snapshots kept and the check built in.
Weighted pipeline multiplies each deal by a stage probability and compares the sum to target directly. It is fine when the stage probabilities come from the team's own history. Most are defaults nobody has tested. Unweighted in-period pipeline over target, read against one over the measured win rate, uses only numbers you have computed yourself.
The trailing four quarters of closed deals. Shorter is noisy on a team with few deals; longer includes a market and a team that no longer exist. Put the start date in one cell so it can be changed and the effect seen.
Ask for the stage-entered date or the last-stage-change date; most CRMs can export it. Failing that, use days since created as a rough proxy per stage. The norm per stage is the median of that figure among deals that were won, which a pivot table gives directly.