New vs Existing Customer Lead Mix %
(Monthly KPI Dashboard in Excel)

Calculate Monthly New vs Existing Lead Mix

1. Identify the required columns in your dataset

• Confirm you have a column for Customer Type with values like New and Existing.
• Confirm you have a Lead Date column (timestamp is fine since you will only use month and year).

2. Convert the dataset into an Excel Table

• Click anywhere inside your dataset.
• Press Ctrl + T.
• Confirm the table range and headers, then click OK.
• Using a table helps formulas auto-fill and makes structured references easier.

3. Create Month and Year helper columns from Lead Date

• Add a Month column using the TEXT() function with month formatting.
  • Use four Ms to return the month name.
• Add a Year column using the same TEXT() function with year formatting.
  • Use four Ys to return the year.
• Because it’s a table, Excel will auto-fill the formula down.

4. Build a unique chronological list of Month and Year

• Create a unique list of month-year combinations so you do not see repeated months.
• Use UNIQUE() on the Month and Year columns together.
• Copy the result and paste values with Ctrl + Shift + V to remove the formula.

5. Set up your summary table structure

• In the header row, create these columns:
  • New
  • Existing
  • Total Leads
  • Optional: Date (for chart axis labels)

6. Create a Date label for the chart axis

• Add a Date column that concatenates Month and Year into a single label:
  • Month + space + dash + space + Year
• This becomes the bottom axis label in the chart.

7. Calculate the New lead percentage using COUNTIFS

• Use COUNTIFS() to count rows where:
  • Customer Type = “New”
  • Month = the month in the summary row
  • Year = the year in the summary row
    
    
• Convert to a percentage by dividing by the total leads for that same month-year.
• Format as a percentage using Ctrl + Shift + 5.

8. Calculate the Existing lead percentage

• Option A:
  • Use =1 – New% to get Existing%.
• Option B:
  • Copy the New formula and change the criteria from “New” to “Existing”.
• Confirm both New and Existing sum to 100% for each month.

9. Calculate Total Leads per month

• Use COUNTIFS() with only:
  • Month = the month in the summary row
  • Year = the year in the summary row
• This returns the count of total leads for that month-year.

10. Insert a Combo Chart using stacked columns and a line

• Highlight the Date column plus:
  • New %
  • Existing %
  • Total Leads
• Go to Insert → Recommended Charts.
• If the recommended chart is not correct:
  • Go to All Charts → Combo.
  • Set New % = Stacked Column
  • Set Existing % = Stacked Column
  • Set Total Leads = Line
  • Turn on Secondary Axis for Total Leads

11. Fix the primary axis so it caps at 100%

• Double click the left axis (percentage axis).
• Set the maximum to 1.0 (100%) so it does not extend to 120%.

12. Format the Total Leads line and add labels

• Click the Total Leads line:
  • Change its color (in the video, it’s changed to black).
  • Increase the line thickness.
• Add Data Labels to the line so the number of leads appears on the chart.
• If labels are hard to read:
  • Click a label until all labels are selected.
  • Add a background fill color to the label boxes.
  • Adjust font size if needed.

13. Update the chart title

• Rename the chart to something like:
  • New vs Existing Customer Lead Mix % by Month