Booked Revenue by Channel %
(Marketing-Sourced) in Excel

Booked Revenue by Channel % (Marketing-Sourced)

1. Confirm you have the required columns in your dataset

• You only need three columns to build this:
  
  • Lead date (timestamp or normal date is fine)
    
  • Channel
    
  • Revenue (booked revenue, with zeros for lost or not-yet-closed deals)
    

2. Create Month and Year helper columns from the lead date

• Add a Month column using the TEXT function on your lead date field
  
  • Format pattern uses four M’s to return the month name
    
• Add a Year column using the TEXT function on your lead date field
  
  • Format pattern uses four Y’s to return the year
    

3. Build a unique Month and Year list for the left side of the table

• Use UNIQUE on the Month and Year columns together to create distinct month-year rows
  
• Use keyboard selection to grab the full Month and Year range quickly
  
  • Start on Month, then use Ctrl + Shift + Arrow keys to select across and down
    
• Copy the results and paste values using Ctrl + Shift + V
  
• Center-align the table for readability
  

4. Create a Month-Year display label for the chart axis

• Add a helper column that combines Month and Year into a single label
  
  • Example output: January – 2025
    
• Concatenate Month + ” – ” + Year
  
• Drag or double-click to fill down
  

5. Create the channel headers across the top

• Build a single formula that does three things in one go
  
  • UNIQUE to get distinct channels
    
  • SORT to make them alphabetical
    
  • TRANSPOSE to place them across columns instead of down rows
    
• Copy and paste values so the headers become static
  

6. Calculate booked revenue by channel for each month

• Use SUMIFS to calculate revenue for a specific Month, Year, and Channel
  
• SUMIFS setup follows this structure
  
  • Sum range: Revenue column
    
  • Criteria 1: Channel equals the header channel
    
  • Criteria 2: Month equals the row month
    
  • Criteria 3: Year equals the row year
    
• Confirm the first output matches expectations for one channel in one month before filling the whole grid
  

7. Convert revenue to percent of total booked revenue for the month

• Your numerator is the SUMIFS result for Month + Year + Channel
  
• Your denominator is the total booked revenue for that Month + Year
  
  • Use the same SUMIFS logic but remove the Channel criteria
    
  • Keep only Month and Year criteria so it sums all channels for that month
    
• Divide numerator by denominator to get the percentage
  
• Format as percent using Ctrl + Shift + 5
  

8. Fix formula references so you can drag across and down

• When you drag across columns, you want the channel reference to move
  
• When you drag down rows, you want the channel reference to stay locked on the same header
  
• Apply dollar signs to lock references correctly
  
  • Lock the channel header row so it stays fixed when dragging down
    
  • Lock the month and year columns so they stay fixed when dragging across
    
• If you see errors after dragging, it is almost always a missing lock on one reference
  
  • Add the missing dollar sign, then copy and paste the corrected formula across the grid
    

9. Add total booked revenue per month for the chart line

• Create a revenue total column using the denominator SUMIFS
  
  • Sum revenue by Month and Year only
    
• Format as currency using Ctrl + Shift + 4
  

10. Create the stacked column chart with a revenue line on a secondary axis

• Highlight the full output table starting with the Month-Year label column
  
• Insert → Recommended Charts
  
• Go to All Charts → Combo
  
• Set every channel series to Stacked Column
  
• Set the booked revenue total series to Line and enable Secondary Axis
  
• This fixes the scale problem of mixing percentages with currency values
  

11. Clean up the chart for readability

• Cap the percent axis at 100% by setting the maximum bound to 1.0
  
• Make the revenue line darker and thicker so it is easy to see
  
• Add data labels only to the line if the chart feels too noisy
  
• Optionally reduce noise by trimming the time range or bundling smaller channels into broader groupings