Lead-to-Booked Appointment % Heatmap
(By Month and Channel)

Lead-to-Booked Appointment Heat Map Setup

1. Confirm the required fields in your dataset

• You need these columns available in your data:
  • Channel
  • Lead Date
  • Appointment Booked with values like Yes/No
• If your system does not track “Appointment Booked,” create it as a custom field or log it manually.

2. Extract Month and Year from Lead Date

• Create two helper columns: Month and Year
• Month formula using TEXT():
  • Use TEXT(LeadDateCell,”mmmm”) to return the month name
• Year formula using TEXT():
  • Use TEXT(LeadDateCell,”yyyy”) to return the year
• Copy both formulas down the entire dataset so every lead row has a month and year.

3. Build a unique Month-Year list for the heat map rows

• Use UNIQUE() on the Month and Year helper columns together to generate a Month-Year list
• Quick selection trick used in the video:
  • Select the Month and Year headers
  • Use Ctrl + Shift + Down Arrow to select the full range
• Paste as values using Ctrl + Shift + V so the list is fixed
• If your source data is not already chronological, sort the Month-Year list so the heat map reads in time order.

4. Build a unique Channel list across the top and format it

• Create a unique list of Channel values using a structured reference from your Excel table
• Wrap the unique list in:
  • SORT() to alphabetize it
  • TRANSPOSE() so it runs left-to-right across columns (heat map header row)
• Paste as values using Ctrl + Shift + V
• Adjust column widths for longer channel names so the header row is readable.

5. Convert your dataset to an Excel Table

• If your dataset is not already a table:
  • Click anywhere inside the data
  • Press Ctrl + T
• Confirm the table name (the video uses a table like TableLeads)
• Using a table makes structured formulas easier, but it also changes how dragging behaves later, so copy/paste is important.

6. Create the numerator using COUNTIFS

• The numerator counts booked appointments for a specific:
  • Month
  • Year
  • Channel
  • Appointment Booked = “Yes”
• Use COUNTIFS() (not COUNTIF) because you need multiple criteria
• Criteria used in the video:
  • Appointment Booked column equals “Yes” (in quotes)
  • Month equals the month value in your heat map row
  • Year equals the year value in your heat map row
  • Channel equals the channel value in your heat map column header

7. Create the denominator using COUNTIFS

• Copy the numerator formula
• Remove the Appointment Booked = “Yes” criteria
• This denominator now counts total leads for that same:
  • Month
  • Year
  • Channel
• This works because Appointment Booked only has two outcomes (Yes/No), so:
  • Total leads = Yes + No

8. Calculate the percentage

• Divide numerator by denominator:
  • Booked leads / Total leads
• Format as a percentage:
  • Use Ctrl + Shift + 5
• This value is what fills each heat map cell.

9. Fix formula behavior before filling the whole heat map

When you drag formulas across a table-driven model, Excel can shift table column references, causing errors.

To prevent that, the video uses two tactics:

• Absolute and mixed references
  • Lock the right parts of your month/year/channel references using $
  • Lock columns when copying down, and lock rows when copying across, based on where each reference lives in the heat map
• Copy and paste instead of drag
  • Copy the corrected formula
  • Paste across and down
  • This avoids table column references auto-shifting (for example, Appointment Booked becoming Appointment Date)

10. Create an Overall by Channel row

• Add a row labeled Overall
• Reuse the same formula pattern, but remove the Month and Year criteria from both:
  • The numerator COUNTIFS
  • The denominator COUNTIFS
• Now it calculates overall Lead-to-Booked Appointment % by channel across the entire dataset
• Format as percentage
• Copy/paste across the channel headers (do not drag).

11. Add a dynamic Target cell to drive the highlighting

• Choose a cell and type a target percentage (example used: 50%)
• Select the heat map range
• Hold Ctrl and also select the Overall row range (so both areas get formatted)

12. Apply Conditional Formatting driven by the Target cell

• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than
• Click the Target cell as the reference
• Confirm the rule
• Now the heat map highlights any cells below your target, and it updates automatically when the target changes.

13. Label the Target cell without breaking the rule

Typing “Target: 50%” directly in the cell breaks formatting because the cell becomes text.

The video’s solution is custom number formatting:

• Keep the cell as a numeric percentage value
• Press Ctrl + 1 to open Format Cells
• Go to Custom
• Add a format that displays a label while remaining numeric, for example:
  • “Target: “0%
• Click OK
• The cell still behaves like a number for conditional formatting, but displays the label for your team.