Pipeline Creation Rate ($ per Week): Track Future Bookings Before They Happen

Pipeline Creation Rate Setup in Excel

1. Add two helper columns to the dataset

• Create a new column called Pipeline Created Amount.
  
• Create a new column called Qualification Week or Week Start.
  
• Place these helper columns on the far right or far left of the dataset so you can append new rows later and everything updates.
  

2. Build Pipeline Created Amount using a qualified flag

• Find the Qualified Opportunity flag column (Yes or No).
  
• Use an IF formula:
  
  • If Qualified Opportunity equals “Yes”, return the Opportunity Amount.
    
  • If not, return blank.
    
• Fill the formula down for the full dataset.
  
• Quick check: spot verify a few rows to confirm qualified rows pull amounts and non-qualified rows stay blank.
  

3. Build the Week Start column for qualified opportunities only

• The qualification date exists only when the opportunity is qualified, so you need a blank-safe formula.
  
• Use an IF formula:
  
  • If the qualification date cell is blank, return blank.
    
  • Otherwise return the Week Start value from the Week Start column in the file.
    
• Format this Week Start output as a Short Date so it displays cleanly.
  

4. Generate a clean, sorted list of Week Start values

• Create a Week Start list area.
  
• Use UNIQUE on the Week Start helper column to get distinct weeks.
  
• Remove the blank and zero entries:
  
  • Copy the list and paste values.
    
  • Delete the blank and zero rows manually.
    
• If the weeks are not in chronological order:
  
  • Wrap the UNIQUE with SORT so it displays oldest to newest.
    
• Keep this week list as the driver for the weekly summary table.
  

5. Calculate weekly pipeline created dollars using SUMIFS

• Next to the Week Start list, create a column for Pipeline Created ($).
  
• Use SUMIFS:
  
  • Sum range: Pipeline Created Amount column.
    
  • Criteria range: Week Start helper column.
    
  • Criteria: the Week Start value in your week list.
    
• Fill down for all weeks.
  
• Format as currency using Ctrl + Shift + 4.
  

6. Calculate the 4-week moving average

• Add a column called 4-Week Moving Average.
  
• In the first row where four weeks exist, calculate the average of the current week and the prior 3 weeks.
  
• Use absolute and relative references correctly so when you drag down, the 4-week window shifts down by one week each row.
  
• Fill down through the remaining weeks.
  

7. Create the combo chart with bars and a moving average line

• Highlight Week Start, Pipeline Created ($), and 4-Week Moving Average.
  
• Insert a Combo chart:
  
  • Pipeline Created ($) as clustered columns.
    
  • 4-Week Moving Average as a line.
    
• Title it: Pipeline Creation Rate ($ per Week).
  
• Interpretation rule used in the video:
  
  • When the column is above the line, you are trending above the 4-week moving average.
    
  • When the column is below the line, you are trending below the 4-week moving average.
    

8. Adjust chart formatting for readability

• Make the columns thicker if needed:
  
  • Click a column → Format Data Series → adjust border/width settings until it looks clean.
    
• Skip data labels on every bar because it becomes noisy with 53 to 54 weeks.
  
• Rely on hover tooltips to see exact values week by week.
  

9. Build the rep-level pipeline table using UNIQUE and transpose

• Copy your weekly table area to create a second analysis block for reps.
  
• Generate a unique list of reps using UNIQUE on the Assigned Rep column.
  
• Sort it alphabetically.
  
• Copy and paste values to remove formulas.
  
• Transpose the rep list so reps run across the top row of the table.
  

10. Calculate weekly pipeline created per rep using SUMIFS

• In the body of the rep-by-week table, use SUMIFS:
  
  • Sum range: Pipeline Created Amount.
    
  • Criteria 1: Rep name equals the rep header cell.
    
  • Criteria 2: Week Start equals the week value in the left-most column.
    
• Fix relative reference issues:
  
  • Lock the rep row reference so it stays on the header row when dragged down.
    
  • Lock the week column reference so it stays on the week column when dragged right.
    
• If the table references still shift unexpectedly, use the approach shown:
  
  • Build one correct cell, then copy and paste across and down, then fill as needed.
    

11. Create the rep share percentage table

• Copy the rep pipeline dollar table into a new area.
  
• Convert each rep’s weekly value into a percentage of total weekly pipeline:
  
  • Cell ÷ SUM of the row.
    
• Lock the SUM range with F4 so it doesn’t move when you drag formulas.
  
• Format as a percentage using Ctrl + Shift + 5.
  
• Fill across and down.
  

12. Create a row-level heat map for rep performance by week

• Select one row of percentages (one week).
  
• Apply Conditional Formatting → Color Scales so high is green and low is red.
  
• Do not apply the heat map to the entire table at once because that compares across all weeks and hides who won that specific week.
  
• Use Format Painter:
  
  • Double-click Format Painter on the formatted row.
    
  • Click each remaining row to apply the same row-level heat map logic across the table.
    
• Result: each week highlights the top rep and the lowest rep for that specific week.