Contact-to-Close Rate: Build a Sales Funnel in Excel

Contact to Close Rate

1. Set up the dataset as an Excel table

• Build a basic table with columns for lead date, lead source, assigned sales rep, minutes to first contact, and binary funnel flags.
  
• Use binary fields for each stage: Contacted Flag, Appointment Set Flag, Appointment Show Flag, Closed Flag.
  
• Make sure the data is formatted as a table (Ctrl + T) so structured references work consistently.
  

2. Count total leads

• Create a label like “Number of Leads”.
  
• Use COUNTA on a reliable column such as Lead Source inside the table to get the total lead count.
  
• Format the number with Ctrl + Shift + 1 and apply alignment (center values, left-align labels).
  

3. Calculate the overall funnel percentages

• Create a simple funnel list: Leads, Contacted, Appointment Set, Appointment Show, Closed.
  
• For each stage, use SUM on the corresponding flag column to count the number of 1s.
  
• Divide each SUM by the total lead count to get a percentage.
  
• Format these as percentages.
  

Formulas used conceptually:

• Percent Contacted = SUM(Contacted Flag) / Total Leads
  
• Percent Appointment Set = SUM(Appointment Set Flag) / Total Leads
  
• Percent Appointment Show = SUM(Appointment Show Flag) / Total Leads
  
• Percent Closed = SUM(Closed Flag) / Total Leads
  

4. Insert the simple funnel chart

• Highlight the funnel stage labels and the percentage values.
  
• Insert a recommended chart that produces the funnel style visual.
  
• Remove any extra series you do not need.
  
• Adjust the chart width and column thickness to get the look you want.
  
• Rename the chart title to something like “Simple Sales Funnel”.
  

5. Create a unique list of lead sources

• Generate the list using UNIQUE on the table Lead Source column.
  
• Copy and paste values (Ctrl + Shift + V) to lock it in.
  
• Sort the list A to Z or use SORT(UNIQUE()).
  

6. Build the lead source funnel rates using SUMIFS and COUNTIFS

For each lead source, calculate each stage as a ratio. The pattern is always:

• Numerator: SUMIFS of the stage flag for that lead source
  
• Denominator: either COUNTIFS of leads for that lead source, or SUMIFS of the prior stage flag for that lead source
  

Use these exact stage definitions from the video:

• Lead to Contact
  
  • Numerator: SUMIFS(Contacted Flag, Lead Source, selected lead source)
    
  • Denominator: COUNTIFS(Lead Source, selected lead source)
    
• Contact to Appointment Set
  
  • Numerator: SUMIFS(Appointment Set Flag, Lead Source, selected lead source)
    
  • Denominator: SUMIFS(Contacted Flag, Lead Source, selected lead source)
    
• Appointment Set to Appointment Show
  
  • Numerator: SUMIFS(Appointment Show Flag, Lead Source, selected lead source)
    
  • Denominator: SUMIFS(Appointment Set Flag, Lead Source, selected lead source)
    
• Appointment Show to Close
  
  • Numerator: SUMIFS(Closed Flag, Lead Source, selected lead source)
    
  • Denominator: SUMIFS(Appointment Show Flag, Lead Source, selected lead source)
    
• Lead to Close
  
  • Numerator: SUMIFS(Closed Flag, Lead Source, selected lead source)
    
  • Denominator: COUNTIFS(Lead Source, selected lead source)
    
• Fill down the formulas for all lead sources.
  
• Format all results as percentages.
  

7. Add conditional formatting by column

• Apply color scales one column at a time (not across the whole table).
  
• Home > Conditional Formatting > Color Scales.
  
• Choose the scale where higher values are green.
  
• Use Format Painter and double-click it to quickly apply the same formatting across the other percentage columns.
  

8. Duplicate the entire block and switch it to Sales Rep analysis fast

• Copy the lead source analysis block and paste it below.
  
• Replace the unique list with a unique list of Sales Reps instead of Lead Sources.
  

Shortcut used in the video:

• Highlight the copied formula area.
  
  
• Press Ctrl + H to Find and Replace.
  
• Find: Lead Source
  
• Replace with: Sales Rep
  
• Make sure “Look in” is set to Formulas.
  
• Click Replace All.
  

Now the entire funnel table recalculates by Sales Rep without rebuilding formulas.

9. Calculate revenue impact between reps

• Create an “Average Deal Size” metric that ignores zeros:
  
  • Use AVERAGEIFS(Booked Revenue, Sales Rep, selected rep, Booked Revenue, “>0”)
    
• For each rep, calculate expected revenue from 100 leads:
  
  • Expected Revenue = Lead to Close Rate * Average Deal Size * 100
    
• Subtract one rep’s expected revenue from another to quantify the dollar difference in performance.
  

10. Scan for leakage and prioritize coaching

• Use the rep funnel table to spot where drop-offs are happening:
  
  • Lead to Contact leakage
    
  • Contact to Appointment Set leakage
    
  • Appointment show leakage
    
• Sort or visually scan the conditional formatting to identify the worst bottleneck stages and the reps who need targeted improvement.