How to Track Accessory Attach Rate (%)
by Month and Rep

Build the Accessory Attach Rate Report in Excel

1. Create the Close Month Column

• Identify the Close Date column in your dataset.
  
• Create a new column called Close Month.
  
• Build a formula that returns a blank if Close Date is blank.
  
• Otherwise, return the month name from the Close Date using a TEXT function with four M’s.
  
• Fill the formula down the entire dataset so every closed deal has a Close Month value.
  

2. Build the Monthly Summary Table

• Create a small table with these headers:
  
  • Month
    
  • Eligible Deals
    
  • Accessories Attached
    
  • Accessory Attach Rate
    
  • Benchmark
    
• Under Month, type January and drag down through December.
  
• If your dataset includes multiple years, add a Year column so months do not mix across years.
  

3. Calculate Eligible Deals by Month

• Locate the dataset column for Eligible Sold Deals (binary 1 or 0).
  
• Use SUMIFS to total eligible deals for each month:
  
  • Sum range: Eligible Sold Deals flag
    
  • Criteria range: Close Month
    
  • Criteria: the month cell in your summary table (example: January)
    
• Because the values are 1 and 0, summing them gives you the count of eligible deals.
  

4. Calculate Accessories Attached by Month

• Locate the dataset column for Accessory Attached Flag (binary 1 or 0).
  
• Use SUMIFS again:
  
  • Sum range: Accessory Attached Flag
    
  • Criteria range: Close Month
    
  • Criteria: the month cell (January, February, etc.)
    
• Fill down for all months.
  

5. Calculate Accessory Attach Rate Percentage

• In the Accessory Attach Rate column, divide Accessories Attached by Eligible Deals.
  
• Format as a percentage using Ctrl + Shift + 5.
  
• Fill down for all months.
  

6. Add a Benchmark Line That Stays Dynamic

• Enter a benchmark value in the first benchmark cell (example: 40%).
  
• In the cells below, reference the benchmark cell above so the benchmark stays consistent and easy to update.
  
• This makes the benchmark line dynamic across the whole table.
  

7. Build the Combo Chart With a Secondary Axis

• The chart uses:
  
  • Columns for Eligible Deals (volume)
    
  • A line for Attach Rate (percentage)
    
  • A line for the Benchmark (percentage)
    
• Build it like this:
  
  • Select Month plus Eligible Deals.
    
  • Hold Control and also select Attach Rate plus Benchmark.
    
  • Insert → Recommended Charts → Combo.
    
• Fix the flattened lines by assigning Attach Rate and Benchmark to a secondary axis.
  
• Make sure Attach Rate is set to Line, not Column.
  

8. Clean Up the Benchmark Line for Readability

• Click the benchmark line and reduce the line width.
  
• Change it to a dashed style if you want it visible but not noisy.
  
• This keeps it from covering labels and other chart elements.
  

9. Add Percentage Data Labels to the Attach Rate Line

• Click the Attach Rate line.
  
• Add Data Labels.
  
• Format labels as percentages and adjust size, weight, and color as needed.
  

10. Create a Rep by Job Type Setup for Deeper Analysis

• Create a unique list of Rep Names using SORT and UNIQUE.
  
• Create a unique list of Job Types using SORT and UNIQUE.
  
• Paste values, then transpose Job Types across the top so your grid becomes:
  
  • Rows: reps
    
  • Columns: job types
    

11. Build the Rep by Job Type Calculation Using SUMIFS

• Calculate total accessory revenue by rep and job type with SUMIFS:
  
  • Sum range: Accessory Revenue
    
  • Criteria 1: Rep Name
    
  • Criteria 2: Job Type
    
• Lock references so Rep changes as you drag down and Job Type changes as you drag across.
  
• If structured references shift unexpectedly, copy and paste formulas instead of dragging.
  

12. Avoid the Common Trap With Averages

• Do not take a straight average of accessory revenue when zeros exist.
  
• Zeros often represent lost deals, no-shows, or no accessory sold.
  
• Use AVERAGEIFS with one extra criteria so accessory revenue must be greater than zero:
  
  • Add Accessory Revenue as an additional criteria range
    
  • Add a criteria of greater than zero
    
• This prevents zeros from dragging down averages and can change rep rankings dramatically.
  

13. Build the Heat Map the Right Way

• Do not apply one color scale to the entire grid.
  
• Apply conditional formatting column by column so each job type is scored against its own range:
  
  • Select one job type column
    
  • Conditional Formatting → Color Scales (high = green, low = red)
    
  • Double click Format Painter and apply it to each remaining job type column
    
• This produces a fair comparison within each job type.