No-Show Rate % by Marketing Channel in Excel
(Booked Appointments)

How to Calculate No-Show Rate % for Booked Appointments in Excel

1. Extract Month and Year from the Lead Date

• In your dataset, locate the Lead Date and Time field.
• Create a Month column using the TEXT function:
  • Use the Lead Date and Time cell, then format with four M’s to return the month.
• Create a Year column using the same function:
  • Use four Y’s to return the year.
• If your file already has Month and Year created, you do not need to recreate them.

2. Create a Unique List of Month and Year

• Build a unique list using the UNIQUE function:
  • Select the Month and Year columns together.
  • Use the trick to select the range quickly:
    • Click Month, hold Ctrl + Shift, then use Right Arrow and Down Arrow to capture both columns.
• Convert the dynamic array to fixed values:
  • Copy the results, then paste values using Ctrl + Shift + V.
• This removes the dynamic array box outline and keeps your list clean.

3. Create a Unique, Sorted, Transposed List of Marketing Channels

• Create a unique list of Marketing Channels from the table column for Channel.
• Wrap the UNIQUE function with SORT to alphabetize the channels.
• Wrap the result with TRANSPOSE so the channels run across the top row.
• Convert this dynamic array into fixed values:
  • Copy, then paste values using Ctrl + Shift + V.

4. Build the No-Show Rate Formula

• The metric is built as a fraction:
  • Numerator: count of No Shows
  • Denominator: count of Booked Appointments
• In the numerator, use COUNTIFS to count rows where:
  • No Show equals “Yes”
  • Month matches the month in your month list
  • Year matches the year in your year list
  • Channel matches the channel header in your channel list
• In the denominator, use COUNTIFS to count rows where:
  • Appointment Booked equals “Yes”
  • Month, Year, and Channel match the same criteria as above
• Divide numerator by denominator to get the No-Show Rate %.

5. Fix the Cell Referencing So You Can Copy Across and Down

• Update references so they behave correctly when copied:
  • Lock Month and Year columns so they do not shift when dragging left to right.
  • Lock the Channel row so it does not shift when dragging down.
• Use dollar signs to control what stays fixed:
  • Month and Year should keep the column fixed.
  • Channel should keep the row fixed, but allow column movement.
• Important behavior with tables:
  • Dragging formulas may cause table column references to shift.
  • Copy and paste is more reliable than dragging in this setup.

6. Handle Divide-by-Zero Cases Cleanly

• Some month-channel combinations will have:
  • Zero booked appointments, causing division errors.
• Wrap the full formula in IFERROR:
  • If there is an error, output a dash instead of an error value.
• Copy the updated formula across the full matrix:
  • Copy the first completed cell
  • Use Ctrl + Shift + Right, then Ctrl + Shift + Down
  • Paste across the full range

7. Create an Overall No-Show Rate by Channel

• Copy the existing no-show formula into the overall row.
• Remove the Month and Year criteria from both numerator and denominator.
• Keep the Channel criteria so the rate is calculated per channel across the full dataset.
• If the IFERROR output interferes, temporarily remove IFERROR while you validate the logic, then reapply it.

8. Add Conditional Formatting for High No-Show Rates

• Highlight the main heatmap area and the overall row (hold Ctrl to select both).
• Apply Conditional Formatting:
  • Home → Conditional Formatting → Highlight Cells Rules → Greater Than
  • Set the threshold to 10% (0.10)
• This highlights high no-show cells instantly.

9. Label the Threshold Cell as a Target Percentage

• If you want the threshold cell to display like “Target: 10%”:
  • Click the cell and press Ctrl + 1
  • Go to Custom number formatting
  • Add a custom format that includes the word Target and keeps the percentage formatting

10. Add a Quick Revenue Impact Calculator

• Create a small calculation area to estimate revenue impact using assumptions shown in the video:
  • Leads per month from a channel
  • Annual leads
  • No-show rate
  • Improvement goal (example: reduce no-shows by half)
  • Close rate
  • Average deal size
• Multiply through to estimate revenue recovered from reducing no-shows.
• Use this as a quick way to translate the heatmap into dollars.