Close Rate on Run Estimates in Excel
(Weekly + Rep + Lead Source)

Close Rate on Run Estimates (%)

1. Confirm the required dataset fields

• Make sure your dataset includes (at minimum):
  
  • Estimate ID
    
  • Estimate Run Date
    
  • Sales Rep
    
  • Lead Source
    
  • Sold Flag (TRUE/FALSE)
    
• If your dataset does not include a Week Start Date column, add it next (this is required to calculate by week).
  

2. Create the Week Start Date field

• Option A: Use a week number with WEEKNUM() if you only need a numeric week grouping.
  
• Option B: Create a true Week Start Date (recommended for readability and charting):
  
  • Use WEEKDAY(date, 2) so the week starts on Monday.
    
  • Formula logic:
    
    • Week Start Date = Estimate Run Date minus weekday number plus 1
      
  • Build it like:
    
    • Take the estimate run date
      
    • Subtract WEEKDAY of that same date with Monday-based numbering
      
    • Add 1 back to land on Monday
      
• Add this formula to the right side of your dataset (not inside the dataset columns) so future paste-ins do not break your structure.
  

3. Generate a unique list of Week Start Dates

• Create a new section for your weekly summary.
  
• Use UNIQUE() on the Week Start Date column to generate a list of all unique week start dates.
  
• This list becomes the driver for the weekly rollups.
  

4. Create the weekly rollup table headers

Add these columns next to the Week Start Date list:

• Estimates per Week
  
• Sold per Week
  
• Close Rate
  
• Benchmark
  

Optional formatting:

• Center the headers and values so the table is easier to read.
  

5. Calculate Estimates per Week

• Use COUNTIFS() to count how many estimates occurred in each week.
  
• Criteria:
  
  • Week Start Date equals the week in your summary row
    
• Fill the formula down the full list of weeks.
  

6. Calculate Sold per Week

• Use COUNTIFS() again, but add one more criterion.
  
• Criteria:
  
  • Week Start Date equals the week in your summary row
    
  • Sold Flag equals TRUE
    
• Fill the formula down.
  

7. Calculate weekly Close Rate

• Close Rate = Sold per Week divided by Estimates per Week
  
• Format as percentage using Ctrl + Shift + 5
  
• Fill down.
  

8. Add a benchmark line using a dynamic benchmark cell

• Enter a benchmark value in a single cell (example: 45%).
  
• In the Benchmark column, reference that single benchmark cell so the entire benchmark series updates automatically when you change the benchmark.
  
• Fill down so every week has the same benchmark value tied to that one cell.
  

9. Build the weekly combo chart with a secondary axis

• Select:
  
  • Week Start Date
    
  • Estimates per Week
    
  • Sold per Week
    
  • Close Rate
    
  • Benchmark
    
• Insert a chart and switch to a Combo chart:
  
  • Estimates per Week and Sold per Week as columns
    
  • Close Rate and Benchmark as lines
    
  • Close Rate and Benchmark must be set to the secondary axis
    
• Rename the chart title to something clear like:
  
  • Close Rate on Estimates by Week
    

10. Clean up the weekly chart formatting

• Make the two column series the same color (so they look like a paired volume view).
  
• Adjust column gap width until the chart is readable.
  
• Format the benchmark line so it is visible but not overpowering:
  
  • Reduce line thickness
    
  • Change to a dashed line style
    
• The visual goal:
  
  • If the close rate line is above the benchmark line, performance is above target.

11. Create the Sales Rep and Lead Source matrix

• Create a second section to show close rate by:
  
  • Sales Rep (rows)
    
  • Lead Source (columns)
    
• Build the lists:
  
  • Use SORT(UNIQUE()) for Sales Reps
    
  • Use SORT(UNIQUE()) for Lead Sources
    
• Copy the Lead Source list and paste it transposed across the top so you can drag formulas across quickly.
  

12. Calculate close rate in the matrix using COUNTIFS

• Numerator: count of Sold estimates
  
  • Criteria:
    
    • Sales Rep equals row rep
      
    • Lead Source equals column lead source
      
    • Sold Flag equals TRUE
      
• Denominator: count of all estimates
  
  • Criteria:
    
    • Sales Rep equals row rep
      
    • Lead Source equals column lead source
      
• Close Rate = Numerator divided by Denominator
  
• Format as percent.
  

13. Lock references correctly so you can drag across and down

• When building the matrix formula:
  
  • Lock the Sales Rep column reference so it stays tied to the rep when dragging left to right
    
  • Lock the Lead Source row reference so it stays tied to the lead source when dragging top to bottom
    
• Use F4 to cycle through reference locking until:
  
  • Rep stays fixed by column when dragging across
    
  • Lead Source stays fixed by row when dragging down
    
• Once correct, drag the formula across the lead sources and down the rep list.
  

14. Add conditional formatting based on benchmark

• Type your benchmark percent in a cell (example: 45%).
  
• Highlight the matrix and apply:
  
  • Conditional Formatting
    
  • Highlight Cell Rules
    
  • Less Than
    
  • Enter 45% (or reference the benchmark cell)
    
• This instantly flags underperforming rep and lead source combinations.
  

15. Add a visible “Benchmark” label using custom formatting

• In the benchmark cell, apply a custom number format (Ctrl + 1) so the cell displays a label like:
  
  • Benchmark: 45%
    
• This makes the benchmark obvious to anyone viewing the table.
  

16. Create the coaching-friendly opportunity count view

• Copy the matrix to a new area.
  
• Replace the close rate formula with the denominator only:
  
  • This shows how many estimates (opportunities) each rep had for each lead source.
    
• Use this view together with the close rate matrix to spot issues like:
  
  • A rep with multiple opportunities but zero closes for a specific lead source
    
  • A lead source generating volume but producing poor outcomes across reps