Overhead Expense Analysis Dashboard in Excel (Heatmap, Benchmarks, and Trends)

How to Track Monthly Overhead as a Percentage of Revenue

1. Set up the month list

• Type January and drag down through December
  

2. Create your summary table headers

• Add columns for Month plus each metric you want to summarize:
  
  • Revenue
    
  • Overhead line items (Admin Salaries, Office, IT, etc.)
    
  • Total Overhead
    
  • Overhead as a Percentage of Revenue
    
  • Benchmark
    
  • Sparklines
    

3. Pull monthly revenue with SUMIFS

• Use SUMIFS to sum revenue by month:
  
  • Sum Range: Revenue column
    
  • Criteria Range: Month column
    
  • Criteria: Month name in your summary table (January, February, etc.)
    
• Format as currency: Ctrl + Shift + 4
  
• Center: Alt + H + A + C
  
• Drag down for all months
  

4. Fix references so formulas copy across correctly

• Keep revenue/expense sum range relative so it changes as you copy across
  
• Lock the Month criteria range column using F4 or $
  
• Lock the month label column (so it stays January column when copying across, but changes rows when copying down)
  

5. Pull each overhead line item by month

• Repeat the SUMIFS pattern for every expense category:
  
  • Sum Range changes to each overhead column
    
  • Criteria Range stays Month column
    
  • Criteria stays the month label
    
• Copy across all overhead columns
  
• Drag down for all months
  
• Format overhead amounts as currency: Ctrl + Shift + 4
  

6. Calculate Total Overhead by month

• In the Total Overhead row:
  
  • Sum across the overhead line items for each month
    
• Copy across for all months
  
• Format as currency
  

7. Calculate Overhead as a Percentage of Revenue

• Overhead % formula for each month:
  
  • Total Overhead ÷ Revenue
    
• Format as percent: Ctrl + Shift + 5
  
• Copy across for all months
  

8. Add a dynamic benchmark row

• Enter a benchmark value (example: 20%) in a single cell
  
• In the benchmark row under January:
  
  • Reference the benchmark cell above (so it stays dynamic)
    
• Copy across through December
  

9. Apply heatmap conditional formatting

• Apply color scales to show variation by month
  
• Do it column-by-column for the overhead dollar columns:
  
  • Select January column for a line item range
    
  • Home → Conditional Formatting → Color Scales
    
• Use Format Painter to apply the same scale across other month columns
  

10. Add sparklines for trend-in-a-cell

• Click the first sparkline cell
  
• Insert → Sparklines → Line
  
• Data range: January through December for that row
  
• Turn on High Point and Low Point from the Sparkline tab
  
• Drag sparklines across/down as needed
  

11. Build the combo chart: Revenue + Overhead % + Benchmark

• Select Month + Revenue
  
• Hold Ctrl and also select Overhead % row
  
• Insert → Recommended Charts → Combo
  
• Set:
  
  • Revenue as Columns (Primary Axis)
    
  • Overhead % as Line (Secondary Axis)
    
• Add the benchmark line:
  
  • Copy the benchmark row
    
  • Click the chart
    
  • Paste (Ctrl + V) to add as a new series
    
• Format the benchmark line (color, dashed style if desired)
  

12. Adjust the percent axis for better visibility

• Double-click the percent axis
  
• Set Min and Max (example: 0.15 to 0.20) to show more variation
  
• Update the benchmark value to test different targets and instantly see which months are above or below