How to Analyze Revenue Mix by System Type
and Month in Excel

How to Calculate Revenue Mix by System Type in Excel

This tutorial shows how to analyze revenue mix to understand what your sales team is selling and what your field teams are installing. You’ll calculate revenue by system type, convert it into percent of total, analyze it by month, and visualize everything using heat maps and charts.

1. Create a Unique List of System Types

• Use the UNIQUE() function on the System Type column (for example, Column J).
  
• The function removes duplicate system types automatically.
  
• Copy the results and paste values using Ctrl + Shift + V to remove formulas.
  
• Delete the zero that appears from blank rows.
  

This gives you a clean list of system types such as split systems, furnaces, RTUs, and mini splits.

2. Calculate Total Revenue by System Type

• Revenue is stored in the Full Invoice Amount column (for example, Column M).
  
• Use the SUMIFS() function to calculate revenue for each system type.
  

Key logic:

• Sum the invoice amount
  
• Where system type equals the selected system
  
• Drag the formula down to calculate revenue for each system type.
  
• Format as currency using Ctrl + Shift + 4.
  
• Remove decimals if needed.
  

3. Calculate Percent of Total Revenue

• Divide each system’s revenue by total revenue.
  
• Format the result as a percentage using Ctrl + Shift + 5.
  

Important detail:

• Lock the total revenue cell using absolute referencing (F4) so the denominator does not change as you drag the formula down.
  

Once done correctly, the percentages will add up to 100%.

4. Create the Revenue Mix Chart

• Highlight System Type and Percent of Total.
  
• Go to Insert → Recommended Charts.
  
• Select a Line Chart (or another chart type if preferred).
  
• Rename the chart to Revenue Mix by System Type.
  

Optional formatting:

• Change colors
  
• Add data labels
  
• Adjust layout to match your dashboard style
  

5. Sort Revenue Mix Correctly

• Sorting directly on charts can cause Excel to mismatch labels.
  
• Instead, sort the underlying table using Sort → Largest to Smallest on the revenue or percentage column.
  

This ensures chart labels remain accurate.

6. Build Revenue Mix by Month

• Copy the system type list and transpose it across columns (Paste Special → Transpose).
  
• This creates a matrix with:
  
  • System types as rows
    
  • Months as columns

7. Calculate Monthly Revenue Mix Percentages

• Use SUMIFS() with two criteria:
  
  • System Type
    
  • Month
    

Numerator:

• Revenue for a specific system type in a specific month
  

Denominator:

• Total revenue for that same month
  
• Lock references correctly:
  
  • Lock system type row when dragging horizontally
    
  • Lock month column when dragging vertically
    
• Format as percentages.
  

8. Create a Heat Map by Month

• Highlight the monthly percentage matrix.
  
• Go to Conditional Formatting → Color Scales.
  
• Choose a color scale (for example, green for higher percentages).
  

This instantly shows:

• Strong months
  
• Weak months
  
• Revenue concentration risks
  

9. Adjust Heat Map Logic by System Type

• Clear existing conditional formatting rules.
  
• Apply color scales one column at a time instead of the entire range.
  
• Use Format Painter to quickly apply the rule across columns.
  

This highlights:

• Best and worst months per system type, not overall.
  

10. Create Revenue Mix by System Type and Month Chart

• Highlight the monthly matrix.
  
• Go to Insert → Recommended Charts.
  
• Select a Stacked Column Chart.
  
• Rename the chart to Revenue Mix by System Type and Month.
  

This visualization shows how each system contributes to revenue over time.

11. Calculate Average Revenue Mix

• Use SUMIFS() to calculate total revenue by system type.
  
• Divide by total revenue across all systems.
  
• Format as a percentage and apply conditional formatting.
  

This shows your long-term average revenue mix, independent of seasonality.

12. Build a Dynamic Spot-Check Heat Map

• Enter a target percentage (for example, 10%).
  
• Apply Conditional Formatting → Highlight Cell Rules → Less Than.
  
• To label the target without breaking calculations:
  
  • Press Ctrl + 1 → Format Cells → Custom
    
  • Use a format like: “Target “0%
    

Now you have a dynamic heat map for quick checks against benchmarks.

Result

You now have a complete revenue mix analysis that shows:

• Revenue by system type
  
• Percent of total revenue
  
• Monthly revenue mix
  
• Heat maps for fast insights
  
• Charts that connect sales activity to field execution
  

This framework can be reused for:

• Revenue stream
  
• Customer type
  
• Region
  
• Technician
  
• Product category