Estimates Run per Month Dashboard
(With Close Rate by Rep)

Estimates Run per Month and Rep Step by Step

1. Build the month list for the summary table

• In a new sheet or section, type Month as a header.
  
• Type January underneath it.
  
• Drag the fill handle down until you reach December.
  
• Center the month labels for readability.
  

2. Convert the dataset into an Excel Table

• Click anywhere inside the raw dataset.
  
• Press Ctrl + T and confirm the range has headers.
  
• Rename the table to something easy to reference (example: TBL).
  
• This lets you reference columns cleanly in formulas and keeps ranges dynamic as data grows.
  

3. Count estimates run by month

• Create a column header called Estimates Run.
  
• Use COUNTIFS to count rows where:
  
  • The Estimate Run Flag is TRUE
    
  • The Month equals the month label in your summary table (January, February, etc.)
    
• Use table column references in the formula so the dataset expands automatically.
  
• Copy the formula down for all months.
  

4. Count closed deals from those estimates by month

• Create a column header called Closed.
  
• Use COUNTIFS again, but this time count rows where:
  
  • Estimate Run Flag is TRUE
    
  • Sold Flag is TRUE
    
  • Month equals the month label in your summary table
    
• Copy the formula down for all months.
  

5. Calculate close rate by month

• Create a column header called Close Rate.
  
• Divide Closed by Estimates Run for each month.
  
• Format as percentage using Ctrl + Shift + 5.
  

6. Add a dynamic target close rate line

• Create a single input cell for Target Close Rate (example: 45%).
  
• Create a Target column next to Close Rate where each month references that single target cell (example: equals the target cell).
  
• This makes the target line dynamic when you change the benchmark.
  

7. Build the combo chart with a target band line

• Highlight Month and Estimates Run.
  
• Hold Ctrl and also select:
  
  • Close Rate
    
  • Target
    
• Insert a chart using Insert → Recommended Charts, then switch to Combo.
  
• Set:
  
  • Estimates Run as clustered columns on the primary axis.
    
  • Close Rate as a line on the secondary axis.
    
  • Target as a line on the secondary axis.
    
• If Close Rate shows as a column, change its chart type to Line.
  
• Adjust chart title and sizing.
  
• Add data labels only to the series you want to emphasize to avoid clutter.
  

8. Create unique lists for Lead Source and Sales Rep

• In a new area, create a header for Lead Source.
  
• Use a sorted unique list:
  
  • SORT(UNIQUE(TBL[Lead Source]))
    
• Copy and paste values (Ctrl + Shift + V) to freeze the list if needed.
  
• Repeat for Sales Rep Name:
  
  • SORT(UNIQUE(TBL[Sales Rep Name]))
    
• Copy and paste values if needed.
  
• Transpose the Sales Rep list so reps run across columns:
  
  • Copy the list, then Paste Special → Transpose.
    

9. Build the Estimates Run matrix by Rep and Lead Source

• Set up a grid where:
  
  • Lead Sources are listed down the left side.
    
  • Sales Reps are across the top.
    
• In the first cell of the grid, use COUNTIFS to count Estimate Run Flag = TRUE with criteria:
  
  • Sales Rep Name equals the rep header cell.
    
  • Lead Source equals the lead source row cell.
    
• Fix references correctly so dragging works:
  
  • Lock the rep row when copying down.
    
  • Lock the lead source column when copying across.
    
• Fill the formula across all reps and down all lead sources.
  

10. Build the Close Rate matrix with a minimum volume threshold

• Create a second matrix for Close Rate.
  
• Start with the same COUNTIFS logic, but now:
  
  • Numerator counts rows where Estimate Run Flag = TRUE and Sold Flag = TRUE.
    
  • Denominator counts rows where Estimate Run Flag = TRUE.
    
• Divide numerator by denominator.
  
• Wrap it in an IF statement so low-volume combinations show a dash:
  
  • If denominator is less than your threshold (example: 10), return “-”
    
  • Otherwise return the close rate calculation
    
• Copy across and down to complete the matrix.
  
• Format as percentage where values exist.
  

11. Add Overall Close Rate by Lead Source and by Rep

• Create an Overall by Lead Source column:
  
  • Use the close rate formula but remove the Sales Rep criteria.
    
  • Keep Lead Source as the only grouping criteria.
    
• Create an Overall by Rep row:
  
  • Use the close rate formula but remove the Lead Source criteria.
    
  • Keep Sales Rep as the only grouping criteria.
    
• Format as percentage.
  

12. Add benchmark-based conditional formatting

• Create a benchmark input cell (example: 45%).
  
• Select the close rate matrix range.
  
• Apply conditional formatting:
  
  • Highlight cells less than the benchmark value.
    
• Add another conditional format rule:
  
  • If the cell contains “-” then fill it gray.
    
• This makes underperforming areas stand out and removes noise from low-volume combinations.