How to Visualize MRR Growth and Trends
with Heatmaps in Excel

How to Track Maintenance Agreement MRR in Excel

This walkthrough shows how to calculate Net MRR for active maintenance agreements, track monthly growth, set a benchmark, and build MRR heatmaps and charts so you can move from revenue spikes to predictable recurring cash flow.

1. Build the Month Column

• In a new table, type Month as the header.
  
• Enter January and drag down until December.
  
• Center the month names for readability.
  

2. Calculate Net MRR by Month for Active Agreements

• Identify these fields in your dataset:
  
  • Net MRR column (the value after discounts)
    
  • Status column (to filter Active vs inactive)
    
  • Month column (or a month field derived from date)
    
• In the first month row (January), use SUMIFS to sum Net MRR where:
  
  • Status = Active
    
  • Month = January
    
• Your logic should be:
  
  • Sum Range: Net MRR
    
  • Criteria 1 Range: Status
    
  • Criteria 1: Active
    
  • Criteria 2 Range: Month
    
  • Criteria 2: January
    
• Format the result as currency:
  
  • Ctrl + Shift + 4
    
• Fill the formula down through December.
  
• Center the values once complete.
  

3. Add Monthly MRR Growth

• Create a Growth column next to Net MRR.
  
• For February and onward, calculate month-over-month growth using either method:
  
  • Method A: (Current Month – Prior Month) / Prior Month
    
  • Method B: (Current Month / Prior Month) – 1
    
• Format Growth as a percentage:
  
  • Ctrl + Shift + 5
    
• Fill down through December.
  

4. Add a Benchmark Growth Target

• Create a single benchmark cell, for example:
  
  • Benchmark = 5%
    
• Format it as a percentage.
  
• This becomes your target line on the growth chart.
  

5. Create the MRR and Growth Combo Chart

• Highlight the Month, Net MRR, and Growth columns.
  
• Insert a Combo Chart:
  
  • Net MRR as columns or line on the primary axis (dollars)
    
  • Growth as a line on the secondary axis (percent)
    
• If Growth looks flattened, assign it to the secondary axis so the scale is correct.
  
• Format the Growth axis as percent with zero decimals.
  

6. Add the Benchmark Line to the Chart

• Copy the benchmark value cell.
  
• Click the chart and paste.
  
• Excel adds it as a constant horizontal benchmark line.
  
• Format the benchmark line:
  
  • Solid line
    
  • Optional dashed style
    
  • Neutral color for visibility
    

7. Add Data Labels the Clean Way

• If labeling everything is too noisy:
  
  • Click the series you want (MRR or Growth)
    
  • Add labels to only that series
    
• This gives quick visibility without cluttering the chart.
  

8. Build a Heatmap of Net MRR by System Type and Month

• Create the same Month list down the left.
  
• Across the top, build a unique list of System Types:
  
  • Use UNIQUE on the System Type column.
    
  • Copy and paste values to remove formulas.
    
  • Sort A to Z if you want.
    
• Transpose the system types so they run horizontally across the top:
  
  • Copy
    
  • Paste Special → Transpose
    

9. Calculate Net MRR by System Type and Month

• In the first cell of the matrix (January + first system type), use SUMIFS again:
  
  • Sum Range: Net MRR
    
  • Criteria 1: Status = Active
    
  • Criteria 2: Month = the row’s month
    
  • Criteria 3: System Type = the column’s system type
    
• Use correct locking so dragging works:
  
  • Lock the Net MRR range so it never shifts.
    
  • Lock the criteria ranges.
    
  • Lock the month column reference so dragging across does not break it.
    
  • Lock the system type row reference so dragging down does not break it.
    
• Format as currency and fill across and down to complete the matrix.
  

10. Apply Conditional Formatting Heatmap

• Highlight the full system type matrix.
  
• Apply Conditional Formatting → Color Scales.
  
• Two common options:
  
  • Full-table heatmap: shows overall highest and lowest values.
    
  • Column-based heatmap: shows best and worst months within each system type.
    
• Pick the style that matches how you want to interpret performance.
  

11. Create a Stacked Column Chart by System Type

• Use the system-type matrix to visualize total MRR composition by month.
  
• Insert → Stacked Column Chart.
  
• This shows how each system type contributes to total maintenance agreement MRR over time.
  
• Rename the chart title to something clear like:
  
  • Monthly Recurring Revenue by System Type
    

12. Extend the Analysis

Once the foundation is built, you can reuse the same structure to slice MRR by:

• Residential vs commercial
  
• City, state, or region
  
• Agreement type
  
• Payment method
  
• Acquisition channel
  

Same formulas, same approach, just different dimensions.