How to Track EBITDA Margin in Excel
(Monthly + By Service Line)

How to Build the EBITDA Margin Report

1. Set up the dataset fields

• Confirm your dataset includes: Date, Month, Service Line, Revenue, COGS, OPEX.
  
• If Month is not already available, you will create it from the Date column.
  

2. Extract Month from Date using TEXT

• In the Month column, use the TEXT function to pull the month name from the Date cell.
  
• Example pattern shown in the video:
  
  • Use =TEXT(B2,”mmmm”) (date cell reference will vary based on your sheet).
    
• Copy the formula down the column to populate Month for every row.
  

3. Create a Month list for the summary table

• In your summary area, type January and drag down until December to build a full month list.
  

4. Calculate monthly Revenue using SUMIFS

• Use SUMIFS to sum Revenue for each month.
  
• Structure used:
  
  • Sum range: Revenue column
    
  • Criteria range: Month column
    
  • Criteria: the month name cell in your month list (January, February, etc.)
    
• Lock the month criteria cell correctly so it:
  
  • Stays in the same column when copied across
    
  • Changes by row as you move down months
    
• Double-click the fill handle to copy down for all months.
  

5. Copy the Revenue formula across for COGS and OPEX using absolute references

• Dragging the formula across can break references if the Month criteria range shifts.
  
• Fix this by locking:
  
  • The Month criteria range (so it never changes when dragged)
    
  • The Month criteria cell’s column (so January stays January when dragged right, but updates to February when dragged down)
    
• Then copy the corrected formula across to:
  
  • COGS column
    
  • OPEX column
    
• Copy down for all months.
  

6. Calculate EBITDA by month

• Create an EBITDA column in your summary table.
  
• EBITDA calculation shown:
  
  • Revenue minus the sum of COGS and OPEX
    
• Copy the EBITDA formula down for all months.
  

7. Calculate EBITDA Margin

• Create an EBITDA Margin column.
  
• EBITDA Margin calculation shown:
  
  • EBITDA divided by Revenue
    
• Format as a percentage (Ctrl + Shift + 5).
  
• Adjust decimals as desired.
  
• Copy down for all months.
  

8. Build the EBITDA Margin line chart by month

• Highlight the Month list and the EBITDA Margin column.
  
• Hold Ctrl to multi-select the two ranges if needed.
  
• Insert a line chart (Recommended Charts or Line chart option).
  
• Optional: Add Data Labels from the chart elements menu to display the percentages.
  

9. Build EBITDA Margin by Service Line

• Create a unique list of Service Lines:
  
  • Use UNIQUE() on the Service Line column.
    
  • Copy and paste as values (Ctrl + Shift + V) so you can sort later.
    
• Repeat the same summary logic, but replace Month criteria with Service Line criteria:
  
  • SUMIFS Revenue by Service Line
    
  • SUMIFS COGS by Service Line
    
  • SUMIFS OPEX by Service Line
    
• Calculate EBITDA and EBITDA Margin the same way:
  
  • EBITDA = Revenue − (COGS + OPEX)
    
  • EBITDA Margin = EBITDA ÷ Revenue
    
• Format margin as percent.
  

10. Build the EBITDA Margin bar chart by Service Line

• Highlight Service Line names and EBITDA Margin values.
  
• Insert a column chart (Recommended Charts or Column chart option).
  
• Sort the service lines by EBITDA Margin:
  
  • Use Data → Sort (largest to smallest).
    
• If sorting causes formulas to “stick” to the wrong labels (as shown in the video):
  
  • Re-check cell references after sorting and correct them so each row’s label matches its calculated values.
  • Optional: Add Data Labels to show the margin values on bars.