How to Track Install vs Service Lead Percentage
by Month in Excel

How to Build the Install vs Service Lead % Model

1. Confirm the Required Columns in Your Dataset

• You only need two fields to build this KPI model:
  • Lead Date
  • Job Type

2. Create Month and Year Helper Columns from Lead Date

• Add a helper column for Month using the TEXT function
  • Reference the lead date cell
  • Use four M’s so it returns the full month name
• Add a helper column for Year using the same TEXT function
  • Use four Y’s to return the full year

3. Build a Unique List of Month and Year Combinations

• Use the UNIQUE function on the two helper columns together
• Select both columns quickly using keyboard shortcuts (right and down selection)
• Copy the results and paste values using Ctrl + Shift + V

4. Create a Date Label Column for the Chart X-Axis

• Add a new column called Date
• Combine Month and Year into a single label using:
  • The ampersand operator
  • A space-dash-space separator in quotes
  • The year value

This creates labels like “January – 2025” for charting.

5. Count Install Leads by Month and Year

• Use COUNTIFS to count rows where:
  • Job Type equals “Install”
  • Month equals the month in your summary table
  • Year equals the year in your summary table
• Ensure “Install” is in quotation marks
• Confirm your table ranges are correct

This gives you the number of install leads for each month.

6. Count Total Leads by Month and Year

• Use COUNTIFS again to count rows where:
  • Month equals the month in your summary table
  • Year equals the year in your summary table

This returns the total leads for the month and becomes your denominator.

7. Calculate Install Lead %

• Divide Install leads by Total leads
• Format as a percentage using Ctrl + Shift + 5
• Copy the formula down for all months

8. Calculate Service Lead %

• Copy the Install % formula structure
• Replace “Install” with “Service”
• Divide by the same Total lead denominator
• Format as percentage and copy down

9. Add Number of Leads Column

• Use the denominator (Total Leads) as its own column
• This will be used for the secondary axis in the chart

10. Insert the Combo Chart

• Highlight the summary table starting from Date across all metrics
• Go to Insert → Recommended Charts
• Click All Charts → Combo

11. Configure Chart Types and Axes

• Change Install % and Service % to Stacked Column
• Change Number of Leads to Line
• Set the Line to Secondary Axis

This separates percentage scale from lead volume.

12. Final Chart Formatting

• Update the title to: Install vs Service Lead Mix % by Month
• Set the percentage axis maximum to 100%
• Add data labels to the line (lead volume) only
• If labels are hard to read:
  • Select all labels at once
  • Add background fill to the labels
  • Adjust font size as needed