Appointment Set per Lead in Excel
(Sales Funnel + Rep Benchmarking)

 Appointment Set per Lead

1. Prepare the KPI summary table

• Clear any filters on the dataset so counts include all rows.
  
• Create a small summary area with these row labels:
  
  • Leads
    
  • Leads Contacted
    
  • Appointments Set
    
• Add two column headers:
  
  • Leads
    
  • Rate
    

2. Convert your dataset into an Excel Table

• Click anywhere inside the dataset.
  
• Press Ctrl + T to convert the range into a Table.
  
• This makes formulas easier to write and more reliable when the dataset grows.
  

3. Calculate total leads

• Use a COUNTA style approach to count rows.
  
• Count a column that is always populated for every lead (avoid columns with blanks).
  
• Example approach:
  
  • Count the Lead Source column, since every row represents one lead.
    

4. Calculate leads contacted

• Use the Contacted Flag column where:
  
  • 1 = contacted
    
  • 0 = not contacted
    
• Since it is binary, calculate total contacted with:
  
  • SUM(ContactedFlagColumn)
    

5. Calculate appointments set

• Use the Appointment Set Flag column where:
  
  • 1 = appointment set
    
  • 0 = not set
    
• Calculate appointments set with:
  
  • SUM(AppointmentSetFlagColumn)
    

6. Calculate the rates

• Leads rate:
  
  • Leads divided by Leads (this equals 100%)
    
• Leads Contacted rate:
  
  • Leads Contacted divided by Total Leads
    
• Appointments Set per Lead rate:
  
  • Appointments Set divided by Total Leads
    
• Format percentages with Ctrl + Shift + 5.
  

7. Create the top-of-funnel chart

• Select the three “Leads” values and their corresponding “Rate” values.
  
  • Use Ctrl to multi-select both ranges.
    
• Go to Insert → Recommended Charts and select the chart that best matches the funnel-style layout.
  
• Clean it up:
  
  • Remove any extra series you do not need.
    
  • Change chart background to black (if matching your dashboard style).
    
  • Change text color to white for readability.
    
  • Rename the chart title (example: Lead to Appointment Conversion Funnel).
    

8. Create a unique sales rep list

• Build a rep list using:
  
  • UNIQUE(Table[Sales Rep])
    
• Sort the list alphabetically using:
  
  • SORT(UNIQUE(Table[Sales Rep]))
    
• Copy and paste values (Ctrl + C, then Ctrl + Shift + V) so the list becomes static.
  

9. Build the rep performance table

Create these columns next to the rep list:

• Leads
  
• Leads Contacted
  
• Appointments Set
  
• Rate
  
• Benchmark
  

Then calculate each field:

• Leads per rep:
  
  • Count how many rows match the rep name (each row is one lead)
    
  • Use COUNTIF(Table[Sales Rep], RepCell)
    
• Leads Contacted per rep:
  
  • Use SUMIFS to sum Contacted Flag where Sales Rep equals the rep name
    
  • SUMIFS(Table[Contacted Flag], Table[Sales Rep], RepCell)
    
• Appointments Set per rep:
  
  • Use SUMIFS to sum Appointment Set Flag where Sales Rep equals the rep name
    
  • SUMIFS(Table[Appointment Set Flag], Table[Sales Rep], RepCell)
    
• Appointment Set per Lead rate per rep:
  
  • Appointments Set divided by Leads
    
  • Format as percent with Ctrl + Shift + 5
    

10. Add a benchmark line that stays dynamic

• Put your benchmark value in the first Benchmark cell (example: 65%).
  
• In the cells below it, reference the cell above:
  
  • This makes the entire benchmark column update automatically if you change the top value.
    

11. Create the rep chart with benchmark

• Select:
  
  • Sales Rep names
    
  • Rate column
    
  • Benchmark column
    
  • Use Ctrl to select multiple ranges.
    
• Go to Insert → Recommended Charts.
  
• Choose Combo:
  
  • Rate as columns
    
  • Benchmark as a line
    
• Format:
  
  • Change chart background to black (if needed).
    
  • Adjust the Y-axis bounds to show variation (example shown: 40% to 70%).
    
  • Make the benchmark line thinner and dashed.
    
  • Add data labels to the columns only.
    

12. Translate the rate into business impact

• Copy a rep’s rate values to a small “per 100 leads” block.
  
• Multiply the rep’s Appointment Set per Lead rate by 100 to make it tangible.
  
  • Example logic: If Rep A is 64% and Rep B is 57.6%, that is about 6 to 7 more appointments set per 100 leads.
    
• If you want to extend the model the same way shown in the video:
  
  • Use an assumed show rate and close rate to estimate incremental closed deals per 100 leads.
    
  • Calculate average deal size using AVERAGEIFS(BookedRevenue, BookedRevenue, “>0”)
    
  • Multiply incremental deals by average deal size to quantify revenue impact.