How to Analyze Average Ticket Size by Month
and Job Type in Excel

How to Analyze Average Ticket Size by Month and Job Type in Excel

This tutorial shows how to analyze average ticket size over time and by job type so you can identify pricing gaps, upsell opportunities, and potential revenue leakage.
You’ll build two visuals:
• Average ticket size by month
• Average ticket size by job type

1. Prepare the Month Field

• If your dataset already includes a Month column, you can use it directly.
  
• If you only have a date column, extract the month using the TEXT() function.
  
• Use =TEXT(DateCell,”mmmm”) to return the full month name.
  
• Double-click the fill handle to copy the formula down the column.
  

This ensures all records are properly grouped by month.

2. Create the Month List

• Type January into a cell.
  
• Drag the fill handle downward until December appears.
  
• Excel automatically fills all months in sequence.
  

This list will drive the monthly calculations.

3. Calculate Average Ticket Size by Month

• Locate the Final Invoice Amount column in your dataset.
  
• Use the AVERAGEIFS() function to calculate the average ticket size for each month.
  
• Set the average range to the invoice amount column.
  
• Set the criteria range to the month column.
  
• Set the criteria to the month name from your list (January, February, etc.).
  
• Format the results as currency using Ctrl + Shift + 4.
  
• Remove decimals if desired.
  
• Drag the formula down to calculate all months.
  

This produces your monthly average ticket size values.

4. Create the Average Ticket Size by Month Chart

• Highlight the Month list and the Average Ticket Size column.
  
• Go to Insert and select a Line Chart.
  
• Rename the chart to Average Ticket Size.
  
• Adjust font size and colors as needed for readability.
  
• Add data labels if you want the dollar values displayed on the chart.
  

This visual shows how ticket size changes throughout the year.

5. Create a List of Job Types

• Use the UNIQUE() function on the Job Type column.
  
• Copy the results and paste values using Ctrl + Shift + V.
  
• Sort alphabetically if needed.
  
• Transpose the list from a column into a row using Paste Special → Transpose.
  

This prepares the job types for multi-criteria averaging.

6. Calculate Average Ticket Size by Month and Job Type

• Use AVERAGEIFS() again with multiple criteria.
  
• Set the average range to Final Invoice Amount.
  
• Add a criteria for Job Type (Install, Maintenance, Service).
  
• Add a criteria for Month.
  
• Use absolute and relative references correctly:
  
  • Lock columns that should not change when dragging formulas.
    
  • Lock rows only when necessary so job types change horizontally and months change vertically.
    
• Drag the formula across and down to fill the table.
  
• Format as currency and center-align values.
  

This creates a full matrix of average ticket size by month and job type.

7. Create the Average Ticket Size by Month Chart

• Highlight the monthly averages.
  
• Insert a Line Chart.
  
• Title the chart Average Job Ticket Size.
  
• Apply formatting (dark theme, font color, labels) as desired.
  

This chart highlights seasonal trends and pricing opportunities.

8. Calculate Overall Average Ticket Size by Job Type

• Use AVERAGEIFS() again, but remove the month criteria.
  
• Calculate the overall average ticket size for each job type.
  
• Format results as currency.
  
• Align values for clean presentation.
  

This isolates ticket size differences between installs, maintenance, and service.

9. Create the Average Ticket Size by Job Type Chart

• Highlight the job type averages.
  
• Insert a Column Chart.
  
• Rename it Average Ticket Size by Job Type.
  
• Adjust formatting so differences between job types are easy to see.
  
• Add data labels if needed.
  

This chart quickly shows which job types drive the highest ticket sizes.

Result

You now have two clear visuals:
• Average ticket size trends over time
• Average ticket size by job type

Together, these insights help you identify pricing gaps, improve upsell strategies, and focus on the job types that generate the most revenue per visit.