Dispatch Fill Rate Dashboard in Excel
(with Heatmaps)

How to Build Dispatch Fill Rate Tracking

1. Create a unique list of service dates

• Identify the Service Date column in your dataset.
  
• In a new column (example: starting at B2), create a unique list:
  
  • Use UNIQUE() on the service date range.
    
  • If your dataset is in an Excel Table, this will update automatically as new rows are added.
    
• Format the output as a date and center-align the column if desired.
  

2. Sort service dates from most recent to oldest

• Wrap the UNIQUE output with SORT() to reverse the order (descending):
  
  • Sort by the date values (descending).
    
• This puts the newest service date at the top of your list.
  

3. Count Booked, Available, and Blocked by service date

• Find the column that contains the status values Booked / Available / Blocked (in the video, this is Column N).
  
• For each service date in your unique list, use COUNTIFS() to count:
  
  • Booked occurrences for that service date
    
  • Available occurrences for that service date
    
  • Blocked occurrences for that service date
    
• You can build the first COUNTIFS and then duplicate it, changing only the word criterion:
  
  • Replace “Booked” with “Available”
    
  • Replace “Booked” with “Blocked”
    

4. Calculate Dispatch Fill Rate

• Compute fill rate as:
  Booked ÷ (Booked + Available)
  
• Do not include Blocked in the denominator.
  
• Format as a percentage.
  

5. Handle zeros so your chart does not drop to the bottom

• If fill rate evaluates to 0, return NA() so Excel charts can ignore the point:
  
  • Use an IF() statement:
    
    • If the result is 0, return NA()
      
    • Otherwise return the fill rate calculation
      
• Wrap the whole thing in IFERROR() so any unexpected divide-by-zero scenarios also return NA().
  

6. Add a target line

• In a new column, create a constant target (example: 80%):
  
  • Enter the target once, then reference the cell above (or copy down) to repeat it for all dates.
    
• This will become the benchmark line in the chart.
  

7. Build the line chart for Fill Rate and Target

• Select your Service Date, Fill Rate, and Target columns.
  
• Insert a Line Chart (or use Recommended Charts).
  
• If you prefer both series as lines, keep both as line series.
  

8. Format the chart for readability

• Make the fill rate line thinner:
  
  • Double-click the series → paint bucket / format options → reduce line weight.
    
• Format the target line differently:
  
  • Make it thinner and optionally dashed.
    
• Optional: remove the chart title to save space.
  

9. Set up the heatmap grid by time window and day of week

• Use the dataset’s Scheduled Window bucket field (the “time window” categories).
  
  • If you do not have this, create it with IFS() based on hour ranges (example: 10–12, 12–14, etc.).
    
• Build a grid:
  
  • Rows = time windows
    
  • Columns = day of week (Mon–Sat, and omit Sun if no data)
    

10. Calculate Fill Rate per time window and day

• In the heatmap grid cell (example: Monday + 10–12), calculate:
  Booked ÷ (Booked + Available)
  
• Use COUNTIFS() with three criteria:
  
  • Scheduled Window equals the row’s window
    
  • Day of Week equals the column’s day
    
  • Status equals “Booked”
    
• For the denominator, repeat COUNTIFS with “Booked” plus COUNTIFS with “Available”.
  
• Format the result as a percentage.
  

11. Fix formula dragging with absolute and relative references

• Before dragging the heatmap formula across and down:
  
  • Lock dataset column ranges using F4 so they do not shift.
    
  • Lock the correct parts of the row/column references so:
    
    • Time window reference changes when dragging down, but not across.
      
    • Day reference changes when dragging across, but not down.
      
• Then drag across the days and down the time windows.
  

12. Apply conditional formatting heatmaps

• Heatmap option 1 (simple):
  
  • Select the grid → Home → Conditional Formatting → Color Scales
    
  • Choose a scale where green indicates higher fill rate.
    
• Heatmap option 2 (threshold-based):
  
  • Select the grid → Conditional Formatting → Highlight Cell Rules → Less Than
    
  • Enter your benchmark (example: 65%) to flag low-performing windows.
    

13. Add a benchmark label to the threshold cell

• If you want the benchmark input to be obvious:
  
  • Select the benchmark cell → Ctrl + 1
    
  • Use Custom number format to display a label like:
    
    • Benchmark: 65%
      
• Color the benchmark input cell (example: blue) so the team knows it is editable.