Financing Adoption Rate (%) in Excel:
Track It by Month, Tech, and Job Type

Build the Financing Adoption Rate Table and Charts

1. Identify the fields you will use from the dataset

• Financing Selected flag (Y = financed)
  
• Outcome or Deal Status (filter to Won)
  
• Closed Month (based on Close Date, not Lead Date)
  
• Sales Rep or Tech Name
  
• Job Type
  

2. Create the month list for the monthly summary table

• In your summary area, type January and drag down through December
  
• If you have multiple years of data, create a Month-Year field using an IF + TEXT setup:
  
  • If the Closed Timestamp cell is blank, return blank
    
  • Otherwise, return the month name using TEXT with “MMMM”
    

3. Build the monthly counts for deals won

• Add three column headers next to the month list:
  
  • Won
    
  • Financed
    
  • Financing Adoption Rate (%)
    
• In the Won column, use COUNTIFS with two criteria:
  
  • Outcome = “Won”
    
  • Closed Month = the month cell in your summary table
    
• Copy the formula down for all months
  

4. Build the monthly counts for financed deals

• Copy the Won COUNTIFS formula into the Financed column
  
• Add one more criterion:
  
  • Financing Selected = “Y”
    
• Copy the formula down for all months
  

5. Calculate Financing Adoption Rate (%) by month

• In the Financing Adoption Rate (%) column, divide:
  
  • Financed ÷ Won
    
• Format as Percentage using Ctrl + Shift + 5
  
• Copy down for all months
  

6. Add an optional benchmark line that can be changed anytime

• Add a Benchmark column (or a single benchmark cell) and enter a value like 30%
  
• Populate the benchmark down the rows by referencing the same benchmark cell so it stays dynamic
  
• This lets you change one value and instantly update the benchmark line in the chart
  

7. Create the combo chart with two axes

• Select the month labels plus:
  
  • Won (column)
    
  • Financed (column)
    
  • Financing Adoption Rate (%) (line)
    
  • Benchmark (line, optional)
    
• Insert a Combo Chart
  
• Set:
  
  • Won and Financed as clustered columns on the primary axis
    
  • Adoption Rate (and Benchmark if used) as a line on the secondary axis
    
• Rename the chart title to something like “Financing Adoption Rate by Month”
  

8. Clean up the chart so it is readable

• Make the benchmark line thinner and styled (for example dashed) so it does not dominate the chart
  
• Add data labels only to the adoption rate line (not the columns) to reduce clutter
  
• Move labels slightly if they overlap
  

9. Build the Tech by Job Type matrix

• Create a unique list of Tech or Sales Rep names using UNIQUE
  
• Sort the list A to Z using SORT, then paste values to remove formulas
  
• Create a unique list of Job Types using SORT + UNIQUE
  
• Paste values, then transpose the job types across columns using Paste Special → Transpose
  

10. Calculate the financed deals numerator in the matrix

• In the first matrix cell, use COUNTIFS with four criteria:
  
  • Sales Rep = the rep name in the row header
    
  • Job Type = the job type in the column header
    
  • Outcome = “Won”
    
  • Financing Selected = “Y”
    
• Lock row and column references correctly so the formula copies across the matrix:
  
  • Lock the rep column when dragging left to right
    
  • Lock the job type row when dragging top to bottom
    
• Copy across and down
  

11. Convert the numerator into Financing Adoption Rate (%)

• Take the numerator COUNTIFS and divide it by a denominator COUNTIFS that counts all won deals without the financing criterion:
  
  • Same criteria, but remove Financing Selected = “Y”
    
• Format as Percentage (Ctrl + Shift + 5)
  
• Copy across and down
  

12. Handle divide-by-zero correctly

• Recognize the difference:
  
  • Zero numerator means deals existed but none were financed
    
  • Divide-by-zero means there were no won deals in that rep and job type combination
    
• Wrap the formula with IFERROR to display a dash instead of an error for divide-by-zero cases
  

13. Add conditional formatting for coaching and visibility

• Create a threshold cell (example 25% or 30%)
  
• Apply Conditional Formatting: Highlight cells less than the threshold and reference the threshold cell so it stays dynamic
  
• Apply a second Conditional Formatting rule for cells containing a dash and fill them gray so “no data” is visually distinct
  

14. Optional formatting trick for a benchmark label inside one cell

• If you want a cell to display something like “Benchmark: 15%” while still remaining a numeric value:
  
  • Use Format Cells → Custom number format
    
  • Add the word Benchmark and keep the underlying value numeric so rules and charts still work