Speed-to-Lead (Median Minutes):
Build a Response-Time Heat Map in Excel

Median Minutes by Lead Source, Month, and Year

1. Confirm the Required Columns in Your Dataset

• Identify the Lead Date and Time column.
• Identify the Speed-to-Lead Minutes column.
• If Speed-to-Lead Minutes does not exist:
  • Add a Lead Created Time column.
  • Add a First Contact Time column.
  • Subtract the two to calculate minutes to first contact.

2. Extract Month and Year from the Lead Date

• Use the TEXT function to extract Month:

TEXT(LeadDateCell,”mmmm”)

• Use the TEXT function to extract Year:

TEXT(LeadDateCell,”yyyy”)

• These fields allow grouping by month and year inside the heat map.

3. Create a Unique List of Years

• Use the UNIQUE function on the Year column.
• Highlight the full range of years.
• Press Ctrl + Shift + V to paste values only.

4. Create a Unique, Sorted List of Marketing Channels Across the Top

Use three functions together:

• UNIQUE to remove duplicates.
• SORT to alphabetize.
• TRANSPOSE to move the list from a column to a row.

After generating the list:

• Copy.
• Paste values using Ctrl + Shift + V.

5. Build the Median Formula Using FILTER Inside MEDIAN

Excel does not have a MEDIANIFS function, so you must:

• Use FILTER to return Speed-to-Lead Minutes where:
  • Month equals the selected month.
  • Year equals the selected year.
  • Channel equals the selected marketing channel.
• Wrap FILTER inside MEDIAN to return the median of that filtered list.

Conceptually:

• FILTER the minutes based on Month × Year × Channel.
• Multiply logical tests so all conditions must be true.
• Wrap that FILTER inside MEDIAN.

This produces the median minutes for each cell in the heat map.

6. Lock Cell References Correctly

When copying the formula:

• Lock the column for Month when dragging left to right.
• Lock the row for Channel when dragging top to bottom.
• Keep Year fixed where necessary.
• Use dollar signs to control which references move and which stay fixed.

7. Copy and Paste Instead of Dragging Across the Table

• Copy the completed median formula.
• Paste it across the heat map grid.
• Avoid dragging if table references start misbehaving.

8. Add Overall Median Calculations

Create an Overall by Channel column:

• Copy the main median formula.
• Remove the Month and Year criteria.
• Keep only the Channel filter.

Create an Overall by Month row:

• Copy the main median formula.
• Remove the Channel filter.
• Keep Month and Year filters.

This produces:

• A total median per channel.
• A total median per month.

9. Add a Conditional Formatting Benchmark

• Highlight the full heat map.
• Go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than.
• Enter your benchmark value (for example, 10 minutes).
• Apply red formatting for values above target.

Any median response time greater than the benchmark will automatically highlight.

10. Create a Dynamic Target Cell That Remains Numeric

If Excel is treating your target value as text:

• Enter the number normally (for example, 20).
• Press Ctrl + 1.
• Go to Custom format.
• Add the word Target in quotation marks inside the custom format.

This allows the cell to display “Target 20” while still functioning as a number for conditional formatting.