How to Measure Sales Cycle Length
(Median Days) in Excel

Build the KPI Table and Combo Chart

1. Pull only the columns you need

• From your dataset, keep only:
  
  • Tech Name
    
  • Lead Created Date and Time
    
  • Final Outcome or Close Status
    
  • Estimate Run Date and Time
    
  • Deal Closed Date and Time
    
• Create a column for Sales Cycle Days from Estimate to Close:
  
  • Close Date minus Estimate Run Date
    

2. Create a Month column from Lead Created date

• Add a Month column based on the Lead Created Date and Time
  
• Use the TEXT function to extract the month name:
  
  • TEXT(LeadCreatedDateCell,”mmmm”)
    
• This becomes the month label you will use in monthly summaries
  

3. Build the monthly summary table layout

• Create a month list for the rows:
  
  • Type January and drag down to December
    
• Add columns for:
  
  • Closed Won count
    
  • Median Sales Cycle Days
    
  • Target line value
    

4. Count Closed Won deals by month

• Use COUNTIFS with two criteria:
  
  • Final Outcome equals “Closed Won”
    
  • Month equals the month in your summary table row
    
• Make sure “Closed Won” is inside quotation marks
  
• Fill the formula down for all months
  

5. Calculate median Sales Cycle Days by month using FILTER

• Use FILTER to return only cycle-day values where:
  
  • Final Outcome equals “Closed Won”
    
  • Month equals the month in your summary table row
    
• Important setup detail:
  
  • Combine criteria using multiplication so both conditions must be true
    
  • Wrap each condition in parentheses
    
• Then wrap the FILTER output in MEDIAN:
  
  • MEDIAN(FILTER(CycleDaysRange,(OutcomeRange=”Closed Won”)*(MonthRange=MonthCell)))
    
• Fill down for all months
  
• This avoids outliers by using the median instead of the average
  

6. Add a target line that can be adjusted

• Enter a target value once in the Target column header area
  
• Reference that cell in the monthly target row so it repeats down the table
  
• This makes the chart update automatically when you change the target value
  

7. Create the combo chart with a secondary axis

• Highlight the monthly table including:
  
  • Closed Won count
    
  • Median days
    
  • Target
    
• Insert a chart using Recommended Charts
  
• Switch to a Combo chart setup:
  
  • Closed Won count as columns on the primary axis
    
  • Median days as a line on the secondary axis
    
  • Target as a line on the secondary axis
    

8. Clean up chart formatting for readability

• Reduce decimals on the median axis:
  
  • Format axis numbers to 1 decimal place
    
• Make the target line subtle:
  
  • Change the target line color to gray
    
  • Reduce line thickness so it acts as a benchmark, not the focus
    
• Add data labels to the median line if needed:
  
  • Select the line, enable Data Labels
    
  • Adjust label number format to reduce decimals
    

9. Build the tech by lead source grid

• Create a sorted unique list of Tech Names:
  
  • SORT(UNIQUE(TechNameRange))
    
• Create a sorted unique list of Lead Sources:
  
  • SORT(UNIQUE(LeadSourceRange))
    
• Paste values to remove formulas
  
• Transpose the lead source list across the top using Paste Special, Transpose
  

10. Calculate median Sales Cycle Days for each tech and lead source

• Use FILTER to pull cycle-day values where:
  
  • Tech Name equals the row tech name
    
  • Lead Source equals the column lead source
    
  • Final Outcome equals “Closed Won”
    
• Wrap in MEDIAN to return a single number
  
• Fix relative reference issues before copying across the grid:
  
  • Lock columns or rows using dollar signs so the row tech stays fixed and the column lead source stays fixed correctly
    
• Use copy and paste across the grid rather than drag if needed
  

11. Handle no-data combinations cleanly

• Some tech and lead source combinations will have zero closed deals and cause errors
  
• Wrap your median formula with IFERROR:
  
  • If error, return a dash instead of an error
    

12. Add overall medians for context

• Overall by lead source:
  
  • Remove the tech criteria from your FILTER logic and copy across
    
• Overall by tech:
  
  • Remove the lead source criteria and copy down
    
• Overall median for everything:
  
  • Filter cycle days for Closed Won only, then take MEDIAN
    

13. Add conditional formatting to highlight slow areas

• Select the grid values plus the overall row and column
  
• Use Conditional Formatting:
  
  • Highlight Cells Rules
    
  • Greater Than
    
  • Reference the overall median cell
    
• This highlights any tech and lead source combination above the overall median so you can spot what is slowing the team down