Monthly Lead Growth % in Excel for HVAC Sales

Monthly Lead Growth Build Guide

1. Set up the only columns you need

• Confirm your dataset includes:
  
  • Lead date and time
    
  • Marketing channel
    
• You can keep other columns, but they are not required for this build.
  

2. Convert your dataset to an Excel Table

• Click anywhere inside your dataset.
  
• Press Ctrl + T to convert it into a table.
  
• This makes formulas easier to manage and keeps ranges dynamic.
  
• Check the table name by selecting the table, then go to Table Design and confirm the name (example used: Table Leads).
  

3. Create Month and Year helper columns from the lead date

• Add two new columns in the table:
  
  • Month
    
  • Year
    
• Use TEXT() to extract each value from the lead date field:
  
  • Month: TEXT([LeadDate],”mmmm”)
    
  • Year: TEXT([LeadDate],”yyyy”)
    
• These fields will be used to group and count leads month over month.
  

4. Build a unique, chronological Month-Year list

• Create a unique list of Month and Year combinations:
  
  • Use UNIQUE() across the two helper columns (Month and Year) to return distinct Month-Year rows.
    
• Ensure the output is in chronological order (January 2025, February 2025, …, January 2026, etc.).
  
• Copy the results and paste values:
  
  • Ctrl + C, then Ctrl + Shift + V
    

5. Build a unique, alphabetized Channel list and transpose it across columns

• Create a unique list of marketing channels using UNIQUE() on the Channel column.
  
• Wrap it in SORT() to alphabetize:
  
  • SORT(UNIQUE([Channel]))
    
• Copy and paste values:
  
  • Ctrl + C, then Ctrl + Shift + V
    
• Transpose the channel list so it runs left-to-right across the top:
  
  • Copy the vertical list
    
  • Right-click where you want it across the top
    
  • Paste Special → Transpose
    

6. Count leads by Month, Year, and Channel using COUNTIFS

• In the body of the table (where Month-Year rows intersect with Channel columns), use COUNTIFS() to count the number of leads (rows) that match:
  
  • Channel
    
  • Month
    
  • Year
    
• Use the table references (example table name: Table Leads).
  
• Important: COUNTIFS criteria order does not matter, but the criteria ranges and criteria must match correctly.
  

7. Fix relative referencing so you can drag formulas correctly

• When dragging formulas:
  
  • Left to right across channels: you want the channel header reference to change, but not the month/year row reference.
    
  • Top to bottom down months: you want the month/year reference to change, but not the channel header reference.
    
• Use absolute and mixed references with dollar signs:
  
  • Put the dollar sign before the column letter to lock the column.
    
  • Put the dollar sign before the row number to lock the row.
    
• If you see table-structure issues while dragging (table columns shifting unexpectedly), the video’s solution is:
  
  • Copy the working formula cell
    
  • Paste across the needed range, instead of dragging.
    

8. Calculate Monthly Lead Growth % using the lead counts

• For each month after the first, calculate growth as:
  
  • Current month leads ÷ prior month leads − 1
    
• Format the result as a percentage.
  
• Remove decimals if you want cleaner readability.
  

9. Optional cleanup

• If you do not want the lead counts displayed (only growth %), you can delete the lead count area.
  
• The growth % formulas will still work if you built them in a way that does not depend on visible helper cells.
  

10. Add a benchmark and flag underperformance with conditional formatting

• Create a benchmark cell (example used: 2%).
  
• Highlight only the growth % cells (not headers).
  
• Apply conditional formatting:
  
  • Home → Conditional Formatting → Highlight Cell Rules → Less Than
    
  • Point it to the benchmark cell
    
• Result:
  
  • Any month-channel growth below the benchmark is highlighted.
    

11. Label the benchmark value without breaking the number format

• Do not type text directly into the benchmark cell (it turns it into text and breaks comparisons).
  
• Instead, keep it numeric and apply a custom number format:
  
  • Press Ctrl + 1
    
  • Go to Custom
    
  • Add a label using quotes so it still behaves as a number (example: add the word “Benchmark” while keeping it numeric)
    

12. Use the output to spot misleading spikes

• The video highlights that big percentage increases can look impressive but may come from a small baseline.
  
• Example pattern discussed:
  
  • A drop, then a large percent increase, can still leave total leads below earlier months.
    
• This is why you review both:
  
  • The month-over-month growth %
    
  • The underlying lead counts (when needed)