Quote-to-Sold Price Realization (%) in Excel
(Find Hidden Discounting)

Monthly Realization Trend, Heat Map, and Rep Breakdown

1. Create the month list

• Add a header cell labeled Month.
  
• Type January and drag down through December.
  
• Center-align the month list and the summary table area to keep the layout clean.
  

2. Create a Sold Month field in the dataset

• Add a new column named Sold Month.
  
• Use a formula that:
  
  • Returns blank if Sold Date is blank.
    
  • Otherwise returns the month name from Sold Date using TEXT(SoldDate,”mmmm”).
    
• Fill the formula down the dataset.
  
• If you have multiple years of data:
  
  • Add a Sold Year field as well, so months do not mix across years.
    

3. Count number of sales by month

• Add a column in your summary table called Sold.
  
• Use COUNTIFS to count deals where Sold Month equals each month:
  
  • Criteria range: Sold Month column
    
  • Criteria: the month cell in the summary table (January, February, etc.)
    
• Double-click the fill handle to copy the formula down for all months.
  

4. Sum quoted and sold values by month

• Add columns in the summary table for:
  
  • Quoted Price
    
  • Sold Price
    
• For Quoted Price, use SUMIFS:
  
  • Sum range: Initial Quoted Net Price
    
  • Criteria range: Sold Month
    
  • Criteria: month cell
    
• For Sold Price, use SUMIFS:
  
  • Sum range: Final Sold Contract Value
    
  • Criteria range: Sold Month
    
  • Criteria: month cell
    
• Format both as currency and remove decimals if you want.
  

5. Calculate the realization percentage by month

• Add a column for Quote-to-Sold Price Realization (%).
  
• Calculate it using totals:
  
  • Sold Price ÷ Quoted Price
    
• Format as percentage.
  
• Copy down for all months.
  

6. Create the combo chart for sold dollars and realization

• Select the Month column and the Sold Price column.
  
• Hold Ctrl and also select the Realization (%) column.
  
• Go to Insert → Recommended Charts.
  
• If it does not build correctly:
  
  • Go to All Charts → Combo.
    
  • Set Sold Price as columns on the primary axis.
    
  • Set Realization as a line on the secondary axis.
    
• Clean up formatting:
  
  • Set percent axis to zero decimals if desired.
    
  • Add data labels only to the line if the chart gets too busy.
    

7. Create the rep list and system type list

• Build a rep list using SORT(UNIQUE()) on the Sales Rep Name column.
  
• Copy and paste values to remove formulas.
  
• Build a system type list using SORT(UNIQUE()) on the System Type column.
  
• Copy and paste values.
  
• Transpose system types across the top:
  
  • Copy the list
    
  • Paste special → transpose
    

8. Calculate realization by rep and system type

• In the rep-by-system matrix, calculate realization as:
  
  • Sum of Sold Amount ÷ Sum of Quoted Amount
    
• Numerator SUMIFS setup:
  
  • Sum range: Final Sold Contract Value
    
  • Criteria: Sales Rep Name equals the rep on the row
    
  • Criteria: System Type equals the type on the column
    
  • Criteria: Sold Flag equals 1
    
• Denominator SUMIFS setup:
  
  • Sum range: Initial Quoted Net Price
    
  • Same criteria as numerator, including Sold Flag equals 1
    
• Divide numerator by denominator and format as percentage.
  
• If dragging breaks references:
  
  • Lock needed references with dollar signs.
    
  • If the table auto-shifts columns, copy and paste the correct formula into the range instead of dragging.
    

9. Apply conditional formatting to build the heat map

• Highlight the realization matrix.
  
• Go to Home → Conditional Formatting → Color Scales.
  
• Choose a green-to-red scale where:
  
  • Higher values show green
    
  • Lower values show red
    

10. Add a sold count matrix for context

• Create a second matrix with the same rep rows and system type columns.
  
• Use COUNTIFS to count sold deals:
  
  • Criteria: Sales Rep Name equals rep row
    
  • Criteria: System Type equals column header
    
  • Criteria: Sold Flag equals 1
    
• Copy across and down.
  
• Use this to validate if a rep’s realization value is based on enough volume.
  

11. Add a dynamic threshold highlight rule

• Create a threshold input cell and type a number such as 5.
  
• Highlight the sold count matrix.
  
• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than.
  
• Reference the threshold input cell so the formatting updates dynamically.
  
• Choose whether you want:
  
  • Less than
    
  • Less than or equal to
    

12. Show “Threshold: 5” without breaking the numeric value

• Click the threshold cell and press Ctrl + 1.
  
• Go to Number → Custom.
  
• Use a custom format like:
  
  • “Threshold: “0
    
• This keeps the cell numeric for conditional formatting, while displaying the label.