Average Sold Job Value by Month
(with Pivot Tables + Slicers)

Average Sold Job Value ($)

1. Confirm the dataset fields you need

• Your dataset should include columns that let you slice performance, such as:
  
  • Territory
    
  • Lead Source
    
  • Sales Rep
    
  • System Type or System Group
    
  • Equipment Tier
    
  • Sold Job Value
    
  • Sold Timestamp
    
• The Sold Timestamp is key:
  
  • If Sold Timestamp has a date/time, the deal was sold
    
  • If it is blank, the deal was not sold
    

2. Create Sold Month from the Sold Timestamp

• Add a Sold Month column.
  
• Use an IF statement to keep blanks blank:
  
  • If Sold Timestamp is blank, return blank
    
  • Otherwise return the month text from the timestamp using TEXT with mmmm
    

3. Create Sold Year from the Sold Timestamp

• Add a Sold Year column.
  
• Copy the Sold Month formula and change the TEXT format from month to year:
  
  • Replace mmmm with yyyy
    

4. Create a unique Month-Year list for reporting

• Build a unique list using the Sold Month and Sold Year columns together.
  
• Select both columns and use UNIQUE across the two-column range.
  
• Copy and paste values to remove formulas.
  
• Center-align the Month and Year list for readability.
  

5. Create a Date label for charting

• Add a Date column next to your Month and Year list.
  
• Build a label by combining:
  
  • Month + space + dash + space + Year
    
• Fill the formula down the Date column.
  

6. Calculate Jobs Sold by Month-Year

• Add a Jobs Sold column.
  
• Use COUNTIFS with two criteria:
  
  • Sold Month equals the month in your list
    
  • Sold Year equals the year in your list
    
• Fill down to get the number of sold jobs for each Month-Year.
  

7. Calculate Average Sold Job Value ($) by Month-Year

• Add an Average Sold Job Value column.
  
• Use AVERAGEIFS with:
  
  • Average range: Sold Job Value column
    
  • Criteria 1: Sold Month equals the month in your list
    
  • Criteria 2: Sold Year equals the year in your list
    
• Format the result as currency and fill down.
  

8. Build the combo chart with a secondary axis

• Select these three columns in your summary table:
  
  • Date
    
  • Jobs Sold
    
  • Average Sold Job Value ($)
    
• Insert a chart and choose a combo chart.
  
• Configure it so:
  
  • Jobs Sold displays as clustered columns
    
  • Average Sold Job Value ($) displays as a line
    
  • The line uses a secondary axis so the dollar values do not flatten the chart
    
• Add data labels to the line only if you want it cleaner and less noisy.
  

9. Create Pivot Table #1 for average sold value by rep and system type

• Click anywhere in the dataset.
  
• Insert a PivotTable into an existing worksheet area.
  
• Build the pivot:
  
  • Rows: Sales Rep
    
  • Columns: System Type or System Group
    
  • Values: Sold Job Value
    
• Change the Values setting from Sum to Average:
  
  • Value Field Settings → Average
    
• Format as currency and remove decimals if desired.
  

10. Create Pivot Table #2 to count opportunities for context

• Copy the first pivot table and paste it below (so slicers can control both).
  
• Replace the Values field:
  
  • Remove Sold Job Value
    
  • Add Opportunity ID to Values
    
  • Ensure it shows Count of Opportunity ID
    
• Optional: remove Grand Totals if you do not want them.
  

11. Apply conditional formatting the right way

• Avoid applying one color scale across the entire pivot if system types naturally have different price ranges.
  
• Instead, apply color scales within each system type column to compare reps fairly:
  
  • Highlight one column of rep values
    
  • Conditional Formatting → Color Scales
    
  • Repeat for each column, or use Format Painter to apply quickly
    
• Optional: add a rule to flag low sample sizes in the opportunity count pivot:
  
  • Conditional Formatting → Less Than → choose a threshold like 8 or 10
    
  • Choose a format style that stands out
    

12. Add a Pivot Chart and clean it up

• Click inside the average sold value pivot.
  
• Insert PivotChart (a clustered column chart works well).
  
• Remove the PivotChart field buttons:
  
  • PivotChart Analyze → Field Buttons → Hide All
    

13. Add slicers for fast filtering

• Insert slicers (based on what you want to slice by), for example:
  
  • Equipment Tier
    
  • Territory
    
  • Lead Source
    
• Place slicers beside the pivots and chart for a dashboard feel.
  

14. Connect slicers to both pivot tables

• By default, slicers may control only one pivot.
  
• For each slicer:
  
  • Click slicer → Slicer tab → Report Connections
    
  • Check both pivot tables
    
• Repeat for each slicer so the entire view filters together.
  

15. Use the dashboard to spot hidden issues

• Filter by Equipment Tier, Territory, and Lead Source to quickly identify:
  
  • Rep-specific performance gaps
    
  • System-type patterns
    
  • Situations where an average is misleading due to low opportunity counts
    
• Use blanks and low counts as a signal to validate sample size before acting.