Revenue Booked per Rep per Week ($):
Track Sales Consistency

Revenue Booked per Rep per Week ($)

1. Confirm the required fields in your dataset

• Deal Status
  
• Booked Revenue
  
• Closed Week Start Date
  
• Rep Name
  

2. Build the sorted list of week start dates

• In a blank area, generate the list using a combined formula:
  
  • SORT(UNIQUE([Closed Week Start Date]))
    
• Copy the results and paste values (Ctrl + C, then Ctrl + Shift + V)
  
• Delete the zero row that appears due to blanks being treated as zeros
  
• Keep the list ordered oldest to newest
  

3. Build the sorted list of sales reps and transpose it across the top

• Generate the rep list with:
  
  • SORT(UNIQUE([Rep Name]))
    
• Copy and paste values (Ctrl + C, then Ctrl + Shift + V)
  
• Copy that vertical list again
  
• Paste Special, then Transpose so rep names run left to right across the header row
  

4. Calculate weekly booked revenue by rep using SUMIFS

• In the first data cell of the matrix, use SUMIFS to return booked revenue where:
  
  • Deal Status equals “Won”
    
  • Closed Week Start Date equals the week listed on that row
    
  • Rep Name equals the rep listed in that column
    
• Format as currency (Ctrl + Shift + 4) and remove decimals if desired
  

5. Lock the references so the formula fills correctly

• Adjust references so when you copy across and down:
  
  • The week reference stays locked to the week column when copying left to right
    
  • The rep reference stays locked to the rep row when copying down
    
• Use dollar signs to control what moves vs what stays fixed
  
• Do not rely on dragging if your table references behave weirdly
  
  • Copy the formula and paste across instead
    
  • Then double-click the fill handle to fill down
    

6. Add a Team Total column

• Create a Team Total at the end of each week row
  
• Use a simple SUM across all rep columns for that week
  
• Double-click to fill down
  

7. Add a 4-week rolling average

• Create a 4-Week Rolling Average column next to Team Total
  
• Start the calculation on week 4
  
• Use AVERAGE() across the prior four Team Total values
  
• Fill down so it continuously rolls forward week by week
  

8. Apply conditional formatting to the rep-by-week grid

• Select only the rep matrix (not the Team Total or Rolling Average)
  
• Conditional Formatting → Color Scales
  
• Choose a scale where high values are green and low values are red
  
• This creates an instant heat map for rep consistency and performance
  

9. Build the chart: columns for Team Total and a line for the rolling average

• Select only:
  
  • Week Start Date
    
  • Team Total
    
  • 4-Week Rolling Average
    
• Insert → Recommended Charts
  
• Choose a combo chart:
  
  • Team Total as columns
    
  • Rolling Average as a line
    
• Apply a darker style if you want stronger contrast
  

10. Improve readability of the chart columns

• Click a column series
  
• Format Data Series
  
• Add a solid border and increase line width (for example ~2.5 pt)
  
• This makes the weekly bars easier to distinguish
  

11. Read the chart correctly

• When the weekly column is above the rolling-average line, that week outperformed the last four-week average
  
• When the column is below the line, performance dipped versus the recent baseline