Appointment Confirmation Rate Analysis in Excel
(Weekly Trend + Heatmap)

How to Build the Weekly Confirmation Rate Trend

1. Set up your worksheet layout

• Keep your raw dataset together in one table.
  
• Put formulas in columns either at the far left or far right of the dataset (not in the middle) so you can append new rows later and drag formulas down cleanly.
  

2. Create the Confirmed flag column

• Add a column that answers: “Was this appointment confirmed?”
  
• Use an IF check on the confirmation field:
  
  • If the confirmation field is blank, return No
    
  • If the confirmation field is not blank, return Yes
    
• Fill the formula down the full dataset.
  

3. Add helper columns for week and weekday

• Add Week Number using WEEKNUM() based on the Service Date.
  
• Add Weekday Name using TEXT() based on the Service Date with a weekday format (dddd) to return Monday, Tuesday, etc.
  
• Fill both formulas down.
  

4. Create a Time Window Bucket column

• Add a column that bins the Scheduled Window Start time into 2-hour buckets.
  
• Use a nested IF structure driven by HOUR(ScheduledWindowStart):
  
  • 6 to 8 AM
    
  • 8 to 10 AM
    
  • 10 to 12
    
  • 12 to 2
    
  • 2 to 4
    
  • 4 to 6
    
  • Outside Window for anything outside your defined ranges
    
• Fill the formula down.
  
• Adjust bucket size if your business uses 3-hour windows or different time blocks.
  

5. Build the weekly summary table

• Create a small summary area with these columns:
  
  • Week Number
    
  • Date Range
    
  • Confirmed Count
    
  • Not Confirmed Count
    
  • Confirmation Rate
    
  • Minimum Acceptable Rate
    

6. Generate a unique Week Number list

• Use UNIQUE() on the Week Number helper column to produce a unique list of weeks.
  
• Make sure you reference the table range (not the entire column) so it stays aligned with the dataset.
  
• Fill down automatically as new weeks appear.
  

7. Create the Week Date Range label

• For each Week Number, create a label like “MM/DD/YYYY – MM/DD/YYYY”.
  
• Use:
  
  • A minimum date lookup for that week
    
  • A maximum date lookup for that week
    
  • Combine them into one text string with a dash in the middle
    
• Format dates as MM/DD/YYYY so the range is easy to scan.
  

8. Count confirmed and not confirmed by week

• Confirmed Count:
  
  • Use COUNTIFS() to count rows where Confirmed = Yes and Week Number = the week in your summary row.
    
• Not Confirmed Count:
  
  • Copy the confirmed formula and change the criteria from Yes to No.
    

9. Calculate weekly Appointment Confirmation Rate

• Confirmation Rate:
  
  • Confirmed Count divided by (Confirmed Count + Not Confirmed Count)
    
• Format as percent using Ctrl + Shift + 5.
  
• Drag the formulas down for all weeks.
  

10. Add a dynamic Minimum Acceptable Rate

• Type your benchmark once (example: 80%).
  
• In the Minimum Acceptable Rate column, reference that benchmark cell so it becomes dynamic.
  
• Fill down so every week uses the same benchmark value.
  
• This lets you change one number and automatically update the table and chart for sensitivity checks.
  

11. Create the weekly trend chart with the benchmark

• Select:
  
  • Date Range labels
    
  • Confirmation Rate
    
  • Minimum Acceptable Rate
    
• Insert a combo-style chart (recommended charts usually picks it correctly).
  
• Title it: Appointment Confirmation Rate by Week.
  
• Adjust the Y-axis bounds for readability:
  
  • Set minimum to 0.5
    
  • Set maximum to 1.0
    

12. Build the heat map framework

• In a new section, list weekdays down the left:
  
  • Monday to Sunday
    
• Across the top, create your Time Window Buckets:
  
  • Use UNIQUE() on the Time Window Bucket column.
    
  • Paste values so you can reorder them into a logical sequence (8 to 10, 10 to 12, 12 to 2, etc.).
    
  • Transpose the bucket list across the top row (Paste Special → Transpose).
    

13. Calculate confirmation rate for each day and time bucket

• For each heat map cell:
  
  • Numerator: COUNTIFS() for Confirmed = Yes, Weekday = that row’s day, Bucket = that column’s bucket
    
  • Denominator: COUNTIFS() for Weekday = that day and Bucket = that bucket (no Yes/No filter)
    
  • Divide numerator by denominator to get the rate.
    
• Lock references correctly with $ signs so the formula can be dragged across and down.
  

14. Apply heat map formatting

• Highlight the heat map matrix.
  
• Apply Conditional Formatting:
  
  • Color Scale (high = green, low = red)
    

15. Add a dynamic “below benchmark” highlight

• Enter a benchmark value for the heat map (example: 85%).
  
• Highlight the heat map range.
  
• Conditional Formatting:
  
  • Highlight Cells Rules → Less Than
    
  • Reference the benchmark cell so it stays dynamic.
    
• Now changing the benchmark value automatically updates which cells are flagged.