How to Track Discount Rate Trends
and Spot Profit Leaks in Excel

How to Build the Discount Rate Dashboard in Excel

1. Set up the Month column and headers

1. Type January in the first month cell and drag down through December.
   
2. Center the month column and headers for readability.
   
3. Add these column headers:
   
   • List Price
     
   • Discount
     
   • Revenue
     
   • Percent Discount
     
   • Benchmark
     

2. Calculate monthly List Price using SUMIFS

1. In the List Price column, use SUMIFS to sum list price for the selected month.
   
2. Criteria:
   
   • Sum range: List Price
     
   • Criteria range: Invoice Date Month or a month field you are using
     
   • Criteria: the month name cell (ex: January)
     
3. Format the result as currency:
   
   • Ctrl + Shift + 4
     
   • Reduce decimals to zero if needed.
     
4. Fill down for all months.
   

3. Calculate monthly Discount using SUMIFS

1. In the Discount column, use SUMIFS to sum discount amounts for the month.
   
2. Use the same month criteria setup as List Price.
   
3. Fill down through all months.
   
4. Format as currency.
   

4. Calculate monthly Revenue

1. Calculate revenue as:
   
   • List Price minus Discount
     
2. Fill down for all months.
   
3. Format as currency.
   

5. Calculate monthly Percent Discount

1. Calculate:
   
   • Discount ÷ List Price
     
2. Format as percent (Ctrl + Shift + 5).
   
3. Keep one decimal if desired.
   
4. Fill down for all months.
   

6. Add a Benchmark column that stays dynamic

1. Enter a benchmark value (example: 7%) in the first benchmark cell.
   
2. Copy it down using a formula reference so if you change the benchmark once, the whole column updates.
   
3. Format as percent and keep the same decimal style as Percent Discount.
   

7. Build the combo chart with a secondary axis

1. Highlight the Revenue, Percent Discount, and Benchmark columns.
   
2. Insert a chart:
   
   • Insert → Recommended Charts
     
   • If needed: All Charts → Combo
     
3. Set:
   
   • Revenue as columns on the primary axis
     
   • Percent Discount as a line on the secondary axis
     
   • Benchmark as a line on the secondary axis
     
4. Rename the chart title (example: “Discounting Rate”).
   

8. Use the benchmark copy-paste trick to add a series fast

1. Copy the benchmark column values (Ctrl + C).
   
2. Click the chart.
   
3. Paste (Ctrl + V).
   
4. Excel automatically adds it as a new series.
   

9. Clean up labels so the chart is not noisy

1. Instead of turning on labels for everything, add labels only to the discount rate line:
   
   • Click the line → add Data Labels
     
2. If labels look too large:
   
   • Reduce font size
     
   • Move only a few labels to avoid overlaps
     

10. Apply heatmap formatting to the monthly table

1. Highlight the range for Revenue and apply:
   
   • Home → Conditional Formatting → Color Scales → Green High
     
2. Highlight Percent Discount and apply:
   
   • Color Scales → Red High (because high discounts are bad)
     
3. Apply the same logic to Benchmark if you want it visually aligned.
   

11. Build the Salesperson by System Type matrix

1. Create a unique list of Salespeople using UNIQUE() on the salesperson column.
   
2. Copy and paste values (Ctrl + C, then Ctrl + Shift + V).
   
3. Sort alphabetically if desired.
   
4. Create a unique list of System Types using UNIQUE() on system type.
   
5. Copy and paste values.
   
6. Transpose system types across the top:
   
   • Copy → Paste Special → Transpose
     

12. Calculate Discount Rate by Salesperson and System Type

1. In the matrix, calculate discount rate as:
   
   • SUMIFS(Discount Amount for salesperson + system type) ÷ SUMIFS(List Price for salesperson + system type)
     
2. Use the same criteria in numerator and denominator:
   
   • Criteria 1: Salesperson = row header
     
   • Criteria 2: System Type = column header
     
3. Format as percent.
   

13. Lock references correctly so the formula drags across and down

1. Before dragging, fix references:
   
   • Lock dataset ranges using F4
     
   • Lock the salesperson reference so it changes when you drag down, not across
     
   • Lock the system type reference so it changes when you drag across, not down
     
2. Drag across and down to fill the full matrix.
   

14. Apply conditional formatting to highlight high discounting

1. Highlight the matrix.
   
2. Apply:
   
   • Conditional Formatting → Color Scales
     
   • Use Red High so heavy discounting stands out immediately
     

15. Create the “spot check” threshold with a dynamic target

1. Copy the matrix to a second area (your spot check grid).
   
2. Clear existing formatting rules for that copied range:
   
   • Conditional Formatting → Clear Rules → From Selected Cells
     
3. Add a single “Target” value (example: 7.0%).
   
4. Apply:
   
   • Conditional Formatting → Highlight Cells Rules → Greater Than
     
   • Reference the target cell
     
5. To show the word “Target” without turning the value into text:
   
   • Format Cells → Custom
     
   • Add “Target” as text in quotes while keeping the numeric value usable
     

This gives you a clean threshold view where anything above the target instantly highlights, and changing the target updates everything.