How to Track Appointment Show Rate by
Lead Source and Sales Rep in Excel

Appointment Show Rate

1. Convert the dataset into an Excel Table

• Click anywhere inside your dataset.
  
• Press Ctrl + T.
  
• Check My table has headers.
  
• Click OK.
  
• Using a table makes formulas easier to read and helps you select columns cleanly.
  

2. Create a unique list of Lead Sources

• In a blank area, type a UNIQUE() formula referencing the table’s Lead Source column.
  
• Make sure you click the table column reference (not the whole column letter).
  
• Copy the results and paste values using Ctrl + C, then Ctrl + Shift + V.
  
• This becomes the Lead Source list for the summary table.
  

3. Add the summary columns you will calculate

Create these columns next to the Lead Source list:

• Appointments Set
  
• Confirmations Sent
  
• Confirmed by Customer
  
• Appointments Showed
  
• Appointment Show Rate
  
• Average Show Rate
  

4. Count Appointments Set by Lead Source

• Since every row in the dataset represents an appointment that was set, you just need to count how many times each lead source appears.
  
• Use COUNTIFS() with:
  
  • Criteria range: table Lead Source column
    
  • Criteria: the lead source name in your summary list
    
• Fill down to calculate for each lead source.
  

5. Count Confirmations Sent by Lead Source

• Use COUNTIFS() to count how many rows have:
  
  • Confirmation Sent = Yes
    
  • Lead Source = selected lead source
    
• Fill down.
  

6. Count Confirmed by Customer by Lead Source

• Use COUNTIFS() to count how many rows have:
  
  • Confirmed by Customer = Yes
    
  • Lead Source = selected lead source
    
• Fill down.
  

7. Count Appointments Showed by Lead Source

• Use COUNTIFS() to count how many rows have:
  
  • Appointment Showed = Yes
    
  • Lead Source = selected lead source
    
• Fill down.
  

8. Calculate Appointment Show Rate by Lead Source

• In a single cell, calculate:
  
  • Appointments Showed ÷ Appointments Set
    
• Format as a percentage using Ctrl + Shift + 5.
  
• Fill down.
  

9. Calculate the overall Average Show Rate benchmark

• Count total “Yes” in Appointment Showed:
  
  • COUNTIFS(Table[Appointment Showed], “Yes”)
    
• Count total rows in the dataset:
  
  • COUNTA(Table[Lead Source])
    
• Divide:
  
  • Yes count ÷ total rows
    
• Format as percent.
  
• Copy the benchmark down so it repeats for every lead source (this becomes the benchmark line in the chart).
  

10. Build the combo chart with a benchmark line

• Highlight:
  
  • Lead Source names
    
  • Appointment Show Rate column
    
  • Average Show Rate benchmark column
    
• Hold Ctrl while selecting non-adjacent ranges.
  
• Insert a Combo Chart:
  
  • Clustered Column for Appointment Show Rate
    
  • Line for Average Show Rate benchmark
    
• If you do not see it in recommended charts:
  
  • Go to All Charts → Combo and select the same setup.
    
• Add data labels to the columns:
  
  • Click the bars → Data Labels
    
• Optionally format the benchmark line:
  
  • Make it thinner (example: 1.5)
    
  • Change to dashed
    
  • Keep it visible but not distracting
    

11. Create a Sales Rep by Lead Source matrix

• Build a unique list of Sales Reps using:
  
  • SORT(UNIQUE(Table[Sales Rep]))
    
• Copy and paste values (Ctrl + C, Ctrl + Shift + V).
  
• Create a unique list of Lead Sources again, copy/paste values.
  
• Transpose Lead Sources across the top (Paste Special → Transpose) so they become column headers.
  

12. Calculate show rate in one cell for each Sales Rep and Lead Source

For each matrix cell, calculate:

• Numerator: count of rows where:
  
  • Appointment Showed = Yes
    
  • Sales Rep = selected rep
    
  • Lead Source = selected lead source
    
• Denominator: count of rows where:
  
  • Sales Rep = selected rep
    
  • Lead Source = selected lead source
    
• Show Rate = Numerator ÷ Denominator
  
• Format as percent (Ctrl + Shift + 5)
  

Important step from the video:

• If dragging causes errors because table references shift to other columns, do not drag normally.
  
• Copy the formula, select the full target range (hold Shift), then paste to populate the matrix.
  

13. Add “Overall” show rates for Lead Source and for Sales Rep

• Lead Source overall:
  
  • Copy an existing cell formula and remove the Sales Rep criteria from both numerator and denominator.
    
• Sales Rep overall:
  
  • Copy the formula and remove the Lead Source criteria from both numerator and denominator.
    
• Format as percent and fill down as needed.
  

14. Highlight below-average performance with conditional formatting

• Select the matrix or the show rate column you want to flag.
  
• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than
  
• Click the benchmark value cell (the overall average) so the rule references it.
  
• Apply the format.
  
• Now anything below benchmark is automatically flagged.
  

15. Add a benchmark label without breaking the number format

If you want the benchmark cell to display text like “Overall Average 88%” without turning the value into text:

• Select the benchmark cell.
  
• Press Ctrl + 1.
  
• Go to Number → Custom.
  
• Add a label before the percent format using custom formatting.
  

16. Extra calculation shown at the end: average deal value excluding zeros

• To avoid averages being pulled down by zeros:
  
  • Use AVERAGEIFS() on Estimated Deal Value
    
  • Add criteria that deal value is greater than zero
    
• This isolates only rows with meaningful values.