Find Your Highest Revenue Channels with
Average Ticket Analysis

How to Build an Average Ticket Heat Map by Channel

1. Confirm the Required Columns in Your Dataset

• Lead Date or Lead Timestamp
• Channel
• Revenue (not gross profit)
• You will create Month and Year from the date field

2. Create Month and Year Helper Columns

• Create a Month column using the TEXT function
  • Use the lead date or timestamp column
  • Format with four Ms to return the month name
• Create a Year column using the same TEXT function
  • Format with four Ys to return the year
• These fields are used to build your month-by-month layout

3. Build a Unique List of Month-Year Combinations

• Select the Month and Year columns together
• Use UNIQUE() to generate a distinct list (January 2025, February 2025, etc.)
• Use keyboard selection shortcuts to highlight the range quickly
  • Ctrl + Shift + Right Arrow
  • Ctrl + Shift + Down Arrow
• Copy and paste values with Ctrl + Shift + V to remove the formula output

4. Create a Sorted, Horizontal List of Channels

• Generate the unique list of channels from your Excel table
  • Use structured references like TableLeads[Channel]
• Wrap the channel list in SORT() to alphabetize it
• Wrap the result in TRANSPOSE() so channels run left-to-right across the top row
• Copy and paste values with Ctrl + Shift + V
• Adjust column widths to make the header row readable

5. Calculate Average Ticket for Each Month-Year and Channel

• Use AVERAGEIFS() to average the Revenue column with criteria for:
  • Channel equals the column header channel name
  • Month equals the month in your month-year list row
  • Year equals the year in your month-year list row
• Important setup rule:
  • The first argument in AVERAGEIFS must be the numeric column you want to average (Revenue)

6. Exclude Zero Revenue so You Only Average Sold Jobs

• Add one more criteria to the same AVERAGEIFS formula:
  • Revenue greater than zero
• This prevents open or unsold jobs (recorded as 0) from pulling down the average ticket size
• You should see the value change immediately after adding this condition

7. Fix Cell References so the Formula Can Be Filled Correctly

• Add dollar signs to lock references properly:
  • Channel header reference should lock the row so it stays on the header row when filling down
  • Month and Year references should lock the column so they stay tied to the month-year fields when filling across
• The goal:
  • Channels change as you move left-to-right
  • Month and Year change as you move top-to-bottom

8. Copy and Paste the Formula Across the Matrix

• Because structured table references can shift unexpectedly when dragging, do this instead:
  • Copy the first completed cell
  • Select the full target range
  • Paste to fill the matrix
• This prevents Excel from “walking” your table references into the wrong columns

9. Handle No-Data Months with IFERROR

• Wrap the AVERAGEIFS formula in IFERROR()
• Output a dash or “No Data” when there are no closed deals for that channel in that month
• Copy and paste the updated version across the full matrix

10. Create an Overall Average Ticket by Channel

• Copy the AVERAGEIFS logic to a separate section for channel-level results only
• Remove the Month and Year criteria so it averages by channel across the full dataset
• Keep the Revenue greater than zero condition
• Copy across for all channels

11. Add a Target Threshold and Highlight Underperformance

• Create a target value cell (example: 750)
• Apply Conditional Formatting:
  • Highlight Cells Rules
  • Less Than
  • Reference the target cell so it updates dynamically when the target changes
• Optional: Create a second conditional rule for very high values (example: 3000)

12. Add a Label to the Target Cell Without Breaking the Number

• If you type “Target 1000” directly into the cell, conditional formatting breaks because it becomes text
• Keep the cell as a number and use Custom Formatting instead:
  • Ctrl + 1
  • Number Format → Custom
  • Add a text label like “Target: ” while keeping the underlying value numeric
• This keeps your rules dynamic and your target readable