Contact Rate % in Excel: Track Lead Quality by
Month & Channel

Contact Rate % Calculation Steps

1. Pull only the columns you need

• In your dataset, focus on these three fields:
  • Lead Date
  • Channel
  • Contacted
• Ignore “speed to lead” for this build. The calculation is based on whether Contacted is marked Yes or No.

2. Create Month and Year from Lead Date

• Add two helper columns next to Lead Date:
  • Month
  • Year
• Use the TEXT function to extract each:
  • For Month: use TEXT with a month format (four Ms)
  • For Year: use TEXT with a year format (four Ys)
• Once you confirm the two new columns are correct, keep the final Month and Year columns you plan to use and delete any duplicates you created while testing.

3. Create a unique sequential Month-Year list

• Use UNIQUE on the Month column to generate a distinct list.
• Use keyboard selection to grab the full range quickly:
  • Click Month
  • Hold Shift and press Right Arrow
  • Then press Ctrl + Shift + Down Arrow
• Press Enter to return the unique list.
• Remove formulas so the list is static:
  • Ctrl + C
  • Ctrl + Shift + V

4. Create a unique Channel list and place it across the top

• Your dataset is an Excel table (example name used: Table Leads).
• Build a horizontal, alphabetized channel header using three wrapped functions:
  • UNIQUE to get distinct channels
  • SORT to alphabetize
  • TRANSPOSE to turn the list into a row
• After it spills across the row:
  • Ctrl + C
  • Ctrl + Shift + V

5. Build the numerator for Contact Rate %

• The numerator is the count of leads where:
  • Channel matches the column header
  • Month matches the row
  • Year matches the row
  • Contacted equals Yes
• Use COUNTIFS with four criteria:
  • Channel
  • Month
  • Year
  • Contacted = Yes
• Confirm the first cell works (example from the video: January 2025 for the first channel returns a count like 8).

6. Build the denominator for Contact Rate %

• The denominator is the total number of leads for that same slice:
  • Same Channel
  • Same Month
  • Same Year
• Use the same COUNTIFS formula but remove the Contacted criteria entirely so it counts both Yes and No.

7. Divide numerator by denominator and format as percent

• Contact Rate % = (Contacted Yes count) / (Total leads count)
• Format the result:
  • Ctrl + Shift + 5

8. Fix the drag errors with absolute references

• Dragging across and down will break if your channel and date references shift incorrectly.
• Add dollar signs to lock what must stay fixed:
  • Lock the channel header row so it stays the same when dragging down
  • Lock the month-year column so it stays the same when dragging across
• Use F4 to cycle reference-locking options quickly until the reference behavior matches your layout.

9. Handle table reference shifting by pasting values

• Because it’s an Excel table, some structured references can still shift unexpectedly when you drag.
• The workaround used in the video:
  • Copy the correct formula result
  • Paste into the full range
• This locks everything in place so the matrix calculates correctly.

10. Add channel averages and month averages

• Create an average column to calculate each channel’s average across the year.
  • Start from the same Contact Rate % structure and remove the Month and Year criteria where needed.
• Create an average row to calculate each month’s average across channels.
  • Copy the formula logic and remove the Channel criteria where needed.
• Paste values where necessary to prevent Excel from trying to interpret labels as criteria.

11. Add a benchmark and conditional formatting

• Enter a benchmark value (example: 80%) in a single cell.
• Apply conditional formatting to highlight underperformance:
  • Select the full contact rate matrix (and the averages if desired)
  • Home > Conditional Formatting > Highlight Cells Rules > Less Than
  • Reference the benchmark cell or type the value directly (80)

12. Label the benchmark cell without turning it into text

• If you type “Benchmark 80%” directly, Excel treats it as text and conditional formatting won’t work.
• Keep it numeric and add the label via custom formatting:
  • Click benchmark cell
  • Ctrl + 1
  • Number > Custom
  • Add a custom format that displays “Benchmark: ” before the percentage while keeping it numeric