Excel guide · 4 min read
How to calculate sales cycle length in Excel
=E2-D2 per deal. The average cycle is =AVERAGEIFS(G2:G11,F2:F11,"Won"); the median, which isn't skewed by one marathon deal, is =MEDIAN(IF(F2:F11="Won",G2:G11)). Leave open deals out and report lost deals separately.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Deal | Rep | Amount | Created | Closed | Outcome | Cycle days | Size | Open age | Report date | Nov 11, 2026 | |
| 2 | D-301 | Maya | 12000 | Jun 2, 2026 | Jul 14, 2026 | Won | 42 | Small | Large deal from | 25000 | ||
| 3 | D-302 | Liam | 48000 | May 11, 2026 | Aug 28, 2026 | Won | 109 | Large | ||||
| 4 | D-303 | Ana | 9500 | Jul 6, 2026 | Jul 31, 2026 | Won | 25 | Small | ||||
| 5 | D-304 | Maya | 30000 | Jun 15, 2026 | Sep 18, 2026 | Lost | 95 | Large | ||||
| 6 | D-305 | Liam | 15000 | Jul 20, 2026 | Sep 4, 2026 | Won | 46 | Small | ||||
| 7 | D-306 | Ana | 62000 | Jun 1, 2026 | Oct 16, 2026 | Won | 137 | Large | ||||
| 8 | D-307 | Maya | 21000 | Aug 10, 2026 | Oct 2, 2026 | Won | 53 | Small | ||||
| 9 | D-308 | Liam | 8000 | Sep 1, 2026 | Sep 22, 2026 | Won | 21 | Small | ||||
| 10 | D-309 | Ana | 40000 | Aug 3, 2026 | Open | Large | 100 | |||||
| 11 | D-310 | Maya | 11000 | Sep 14, 2026 | Open | Small | 58 |
Download this sheet as an Excel practice file (.xlsx)
Cycle days per deal
Export deals from your CRM with the created date in D, the close date in E and the outcome in F (Won, Lost or Open). Cycle days in G2: =IF(F2="Open","",E2-D2). Open deals stay blank because they haven't finished their cycle yet.
Check what "created" means in your CRM. If reps create deals late, after weeks of calls, the cycle looks shorter than it is. If marketing creates every lead as a deal, it looks longer. Pick the stage where a deal really starts and use that date.
Result: 42
42: D-301 was created on 2 June and won on 14 July.
Average and median of won deals
Sales cycle usually means won deals only: =AVERAGEIFS(G2:G11,F2:F11,"Won") gives about 61.9 days. Including the lost deal pushes a plain =AVERAGE(G2:G11) to 66, and that number means nothing: lost deals often drag on before someone finally marks them lost. Report lost cycle time separately if you want it (95 days here).
Two big deals took 109 and 137 days, which pulls the average up. The median of won deals is =MEDIAN(IF(F2:F11="Won",G2:G11)): half the won deals closed faster than this and half slower. In Excel 2019 and earlier, press Ctrl+Shift+Enter to confirm it.
Result: 46
46: the median won deal took 46 days, against an average of about 61.9.
By rep and by deal size
Per rep: =AVERAGEIFS(G2:G11,F2:F11,"Won",B2:B11,"Ana") gives 81 days for Ana, against 47.5 for Maya and about 58.7 for Liam. Before you call Ana slow, look at what she closes: one of her two wins is the 62,000 deal.
Band deals by size so you compare like with like. H2 labels each deal with =IF(C2>=$L$2,"Large","Small"), with the threshold (25,000) in an input cell. The median per band uses two conditions multiplied together: =MEDIAN(IF((F2:F11="Won")*(H2:H11="Large"),G2:G11)) gives 123 days for large deals; small deals come to 42. One blended number would hide that large deals take about three times as long.
Result: 123
123: the median cycle for won deals of 25,000 or more. Small won deals have a median of 42 days.
Flag stale open deals
For open deals, age matters more than cycle: I2 is =IF(F2="Open",$L$1-D2,""), the days since creation as of the report date in L1. A deal older than the typical cycle for its size band deserves a look.
The flag compares the age with the median of the same band: =IF(I11>MEDIAN(IF((F2:F11="Won")*(H2:H11=H11),G2:G11)),"Stale","OK"). D-310 (small, 58 days old against a median of 42) is Stale. D-309 is 100 days old, which sounds bad, but large deals take a median of 123 days, so it's still OK. Compare it with the overall median of 46 instead and you'd chase the wrong deal.
Result: Stale
Stale: D-310 has been open 58 days, longer than the 42-day median for small deals.
Is the cycle getting longer? Trend by close quarter
Group won deals by the quarter they closed: =AVERAGEIFS(G2:G11,F2:F11,"Won",E2:E11,">="&DATE(2026,10,1),E2:E11,"<"&DATE(2027,1,1)) gives 95 days for Q4 so far, against 48.6 for Q3 (July to September).
Before anyone panics, count the deals behind each number: Q4 has only 2 wins, one of them the 137-day deal. With a handful of deals per quarter, show the count next to the average, use the median, or look at a rolling 6 or 12 months instead.
Result: 48.6
48.6: the average cycle of the 5 deals won in Q3.
Common mistakes
Including open deals
Leave their cycle blank and measure their age separately against the typical cycle.
Mixing won and lost deals in one average
Filter on Won for sales cycle; report lost cycle time on its own.
One cycle number for every deal size
Band deals by amount and compare each band with its own median.
Reading a trend from 2 deals
Show the deal count beside each period and prefer longer windows when counts are small.
FAQ
How do you calculate sales cycle length?
For each won deal, subtract the created date from the closed date. The sales cycle length is the average or median of those days across won deals in a period.
How do I calculate the median with a condition in Excel?
Use MEDIAN with IF inside: =MEDIAN(IF(F2:F11="Won",G2:G11)). In Excel 365 press Enter; in Excel 2019 and earlier confirm it with Ctrl+Shift+Enter.
Should lost deals count in the sales cycle?
Usually not. Sales cycle describes how long it takes to win. Track how long lost deals stay open separately, since long-running losses are a pipeline hygiene issue.
How do I find deals that have been open too long?
Calculate each open deal's age as report date minus created date and compare it with the median cycle for similar deals. Flag it with IF when the age is greater.
Functions used
AVERAGEIFSAverages with several conditionsMEDIANMiddle valueIFPicks a result from a testAVERAGEMean of numbersDATEBuilds a date
Related guides
- How to calculate win rate in Excel
- How to calculate a weighted pipeline in Excel
- How to find the median in Excel
- How to calculate days between two dates in Excel
- How to calculate time to hire in Excel
- How to calculate supplier lead time in Excel
Updated