Emergency Call Response Time:
Median by Month + SLA Target + Heatmaps

How to Build Emergency Call Response Time in Excel

1. Create the month list for your report table

• Go to your starting cell and type Month.
  
• In the next cell, type January.
  
• Drag the month list down through December.
  
• Center the month labels for readability.
  

2. Identify the filter criteria you need to calculate the median

• You are calculating a median response time, but only for rows that meet all criteria:
  
  • Call type is Emergency
    
  • Job status is Completed
    
  • Month matches the month label in your table
    

3. Use FILTER to return the response-time values that match your criteria

• Locate the columns in your dataset:
  
  • Response Time in Minutes
    
  • Month
    
  • Call Type
    
  • Job Status
    
• In the first month row (January), use the FILTER function to return only response times that match:
  
  • Month equals January
    
  • Call Type equals Emergency
    
  • Job Status equals Completed
    
• Combine multiple criteria using multiplication (AND logic).
  
• Add parentheses around each logical test so the FILTER calculation works correctly.
  

4. Wrap the FILTER output in MEDIAN to get the monthly median

• Take the FILTER formula you just built and wrap it inside MEDIAN.
  
• Press enter to return the median emergency response time for January.
  
• Drag the formula down for all months.
  
• Remove decimals to match a clean “minutes” format.
  

5. Add your SLA target row for benchmarking

• Add a row labeled SLA Target.
  
• Enter your target time (example used: 55 minutes).
  
• Reference that target and fill it across all months so the same SLA target repeats for every month.
  
• Format the SLA target with the same number format as the median.
  

6. Create a combo chart to compare median vs SLA target

• Click inside your month table.
  
• Go to Insert → Recommended Charts.
  
• Choose a Combo chart so you can use:
  
  • Columns for the Median values
    
  • Line for the SLA Target
    
• If the combo option does not show up under Recommended Charts:
  
  • Go to All Charts → Combo
    
  • Set Median to Clustered Column
    
  • Set SLA Target to Line
    
• Update the title to something like “Emergency Call Response Time by Month”.
  
• Add data labels to the column series if you want the values shown on the bars.
  
• Adjust the month labels so they are horizontal, not angled.
  

7. Add weekday and hour fields to support the heatmap view

• Confirm your dataset includes:
  
  • Weekday
    
  • Hour
    
• Create a unique list of hours using UNIQUE().
  
• Sort the hour list using SORT() so it runs smallest to largest.
  
• Copy and paste values to remove formulas.
  
• Drag the hour headers across to build the top row of the heatmap table.
  

8. Build the weekday by hour median calculation using FILTER + MEDIAN

• Use the same approach as the monthly median, but add two more criteria:
  
  • Weekday equals the weekday label (Monday, Tuesday, etc.)
    
  • Hour equals the hour header (0, 1, 2, etc.)
    
• Use correct absolute references so the formula copies properly:
  
  • Lock the weekday row reference so it changes left to right but not top to bottom.
    
  • Lock the hour column reference so it changes top to bottom but not left to right.
    
• Keep the same additional criteria:
  
  • Job Status equals Completed
    
  • Call Type equals Emergency
    
• Wrap the FILTER result in MEDIAN.
  

9. Use IFERROR to hide gaps where no data exists

• Some weekday-hour combinations will have no emergencies.
  
• Wrap the MEDIAN(FILTER()) formula inside IFERROR so blanks show instead of errors.
  
• Copy and paste formulas across the table (instead of dragging) to prevent Excel table references from shifting unexpectedly.
  
• Format the table as whole minutes using number formatting and remove decimals.
  

10. Apply conditional formatting to create the response-time heatmap

• Select the full heatmap range.
  
• Go to Home → Conditional Formatting → Color Scales.
  
• Choose a scale where higher values are red (because higher response time is worse).
  

11. Create an SLA threshold highlight that only flags values above a target

• Type your threshold value in a cell (example used: 50).
  
• Select the heatmap range.
  
• Go to Conditional Formatting → Highlight Cell Rules → Greater Than
  
• Enter 50 and apply formatting.
  
• If blanks get highlighted and you do not want that:
  
  • Create a new rule that applies to blanks
    
  • Set it to “No Format”
    
  • Go to Manage Rules
    
  • Turn on Stop If True for the blank rule so blanks stay unformatted
    

12. Format the SLA threshold cell to display a label like “SLA Threshold: 50 minutes”

• Select the threshold cell.
  
• Press Ctrl + 1 to open Format Cells.
  
• Go to Custom.
  
• Use a custom format so the number displays as:
  
  • SLA Threshold: 50 minutes
    
• Keep the cell numeric so you can change the threshold value anytime without rewriting text.
  

Optionally, remove zeros from the heatmap display for a cleaner look.