How to Track Pipeline Coverage Ratio (Next 30 Days ÷ Target) in Excel

Next 30 Days Pipeline Coverage Ratio vs Target

1. Set up your raw data and calculated columns

• Confirm your dataset includes fields like expected close date, sales rep, status, estimated revenue, probability, and a calculated weighted value.
  
• Keep calculations off to the right of the dataset so your table can expand with new rows and formulas stay intact.
  

2. Create the “Expected Month” from the expected close date

• Add a new column called Expected Month.
  
• Use the TEXT function to convert the expected close date into a month label:
  
  • TEXT(ExpectedCloseDateCell, “mmmm”)
    
• Fill down the column so each opportunity is assigned to a month bucket.
  

3. Build the month list for the summary table

• Create a small table with a Month column.
  
• Type January and drag down through December.
  

4. Add the three summary columns

• Add headers next to Month:
  
  • Expected
    
  • Target
    
  • Coverage
    

5. Calculate Expected revenue by month using SUMIFS

• Use SUMIFS to sum Weighted Pipeline Value for each month.
  
• Add criteria for:
  
  • Expected Month equals the month in your summary table
    
  • Status equals “Open”
    
• Format as currency (Ctrl + Shift + 4).
  
• Fill the formula down for all months.
  

6. Pull Target by month using VLOOKUP

• Create or reference a small target table where:
  
  • Column 1 = Month
    
  • Column 2 = Monthly Target
    
• Use VLOOKUP to bring the target into the summary table:
  
  • Lookup value = month cell (example: January)
    
  • Table array = target table range
    
  • Column index = 2
    
  • Match type = 0 (exact match)
    
• Format as currency and fill down.
  

7. Calculate Coverage

• Coverage = Expected ÷ Target
  
• Format as percent (Ctrl + Shift + 5).
  
• Fill down.
  

8. Create the combo chart with a secondary axis

• Select Month and Expected.
  
• Hold Ctrl and also select Coverage (include the header cell so Excel labels it correctly).
  
• Insert → Recommended Charts → Combo.
  
• Set:
  
  • Expected as columns on the primary axis
    
  • Coverage as a line on the secondary axis
    
• If the line looks “flattened” at the bottom, it means it is still on the primary axis. Reassign it to the secondary axis and confirm the right-side axis appears.
  

9. Clean up chart labeling for readability

• Add a clear chart title (example: Pipeline Coverage Ratio by Month).
  
• If labels are too noisy, remove all labels first.
  
• Add data labels only to the coverage line so the percent is easy to read.
  

10. Build the rep contribution heat map layout

• Create another section with:
  
  • Months listed vertically (January to December)
    
  • Sales reps listed horizontally across the top
    
• Generate the rep list dynamically:
  
  • Use UNIQUE on the Sales Rep column
    
  • Wrap it in SORT to alphabetize
    
• Copy and paste values to remove formulas (Ctrl + C, then Ctrl + Shift + V).
  
• Transpose the rep list so it runs across columns (Paste Special → Transpose).
  

11. Calculate each rep’s contribution to monthly coverage

• In the first rep-month cell, build a ratio:
  
  • Numerator: SUMIFS of Weighted Pipeline Value with criteria:
    
    • Status = “Open”
      
    • Sales Rep = rep header cell
      
    • Expected Month = month row cell
      
  • Denominator: the monthly target for that same month (using the same VLOOKUP logic)
    
• Format as percent.
  

12. Fix absolute references so you can drag across and down

• Lock references so:
  
  • The rep reference changes when dragging left/right but stays fixed when dragging down.
    
  • The month reference changes when dragging down but stays fixed when dragging left/right.
    
  • The target table range does not shift when dragging.
    
• Use dollar signs or F4 to cycle locking until dragging works correctly.
  

13. Fill the full heat map

• Copy the corrected formula across all reps and down all months.
  

14. Add totals

• Use AutoSum to total rows or columns:
  
  • Highlight range and press Alt + =
    

15. Apply conditional formatting by row

• To compare performance within each rep row properly:
  
  • Highlight one rep’s row (excluding totals).
    
  • Home → Conditional Formatting → Color Scales (green high, red low).
    
• Use Format Painter (double click it) to quickly apply the same row-based formatting to each rep row.