Tracking Lead-to-Quote Conversion by Month

From Raw Lead Data to a Combo KPI Chart

1. Identify the Required Columns in Your Dataset

• Confirm you have a Lead Date/Time field (either of the date/time columns shown in the dataset).
• Confirm you have a Quote Given field (Yes/No).

2. Extract Month from the Lead Date

• Create a new column for Month.
• Use the TEXT function to return the month name:
  • =TEXT([Lead Date Cell],”mmmm”)
• This returns values like January, not month numbers.

3. Extract Year from the Lead Date

• Create a new column for Year.
• Use the TEXT function to return the year:
  • =TEXT([Lead Date Cell],”yyyy”)

4. Create a Unique List of Month and Year Combinations

• Create a helper column that combines Month and Year into a single value (example: January 2025, February 2025, etc.), or use the Month and Year columns together to generate the list.
• Use the UNIQUE function to remove duplicates and generate one row per month-year period.
• Use keyboard shortcuts to grab the full range quickly:
  • Highlight the Month and Year output area, then use Ctrl + Shift + Down, then press Enter.
• Convert the results to static values:
  • Copy the range (Ctrl + C) and paste values (Ctrl + Shift + V).

5. Convert Your Dataset to an Excel Table

• Click anywhere inside your raw dataset.
• Press Ctrl + T to convert the dataset to a Table.
• Confirm the table has headers and note the Table name under Table Design.

6. Count Monthly Leads Using COUNTIFS

• Create a column called Leads.
• Since each row represents one lead, count leads per month and year using COUNTIFS:
• Criteria you’ll use:
  • Month column equals the month in your summary row
  • Year column equals the year in your summary row
• Fill the formula down across all months.

7. Count Monthly Quotes Given Using COUNTIFS

• Create a column called Quotes Given.
• Copy the Leads COUNTIFS formula and add a third criterion:
  • Quote Given column equals Yes
• Fill the formula down.

8. Calculate Lead-to-Quote Conversion %

• Create a column called Lead-to-Quote Conversion %.
• Divide Quotes Given by Leads:
  • =QuotesGiven / Leads
• Format as a percentage:
  • Ctrl + Shift + 5
• Fill down.

9. Create a Target Percentage That Can Change Dynamically

• Add a Target % cell above or beside your table.
• Reference that same cell down the Target column so it stays dynamic:
  • In the first Target row: =[Target Cell]
  • Fill down so every row updates if the target changes.

10. Create a Month-Year Label for the Chart X-Axis

• Create a label that combines Month and Year in a readable format (example: Jan – 2025 or January – 2025).
• Concatenate:
  • Month + ” – ” + Year
• This becomes your chart’s horizontal axis.

11. Insert the Combo Chart

• Select the Month-Year label column and the Leads column.
• Hold Ctrl and also select:
  • Lead-to-Quote Conversion %
  • Target %
• Go to Insert → Recommended Charts.
• If Excel does not automatically build it correctly:
  • Go to All Charts → Combo.

12. Configure the Combo Chart Axes Correctly

• Set Leads as Clustered Column on the primary axis.
• Set Lead-to-Quote Conversion % as a Line on the secondary axis.
• Set Target % as a Line on the secondary axis.

13. Format the Benchmark Line

• Click the Target line (benchmark).
• Change it to:
  • Black color
  • Dotted line style
  • Smaller line weight so it does not dominate the chart

14. Add Data Labels Only to the Conversion Line

• Do not label the whole chart (it becomes noisy).
• Click the conversion line only (the actual Lead-to-Quote % line).
• Turn on Data Labels for that line only.
• Increase the label font size if needed and match the label color to the line color so it is easier to read.

15. Update the Target to Stress Test Performance

• Change the target from something like 20% to 25%.
• Watch how many months fall below the benchmark to quickly identify performance gaps.