How to Measure Quote-to-Sale Conversion by Month

How to Build a Monthly Quote-to-Sale Conversion Report

1. Confirm the required columns in your dataset

• Lead date or timestamp
• Quote given field
• Sold or closed field
• Optional: other marketing columns can stay, but these are the only ones needed for this build

2. Create Month and Year helper columns

• Add a Month column using the TEXT function on the lead date field
  • Use: TEXT([Lead Date],”mmmm”)
• Add a Year column using the TEXT function on the same lead date field
  • Use: TEXT([Lead Date],”yyyy”)
• These will be used for monthly grouping

3. Build a unique Month-Year list for the reporting table

• Use UNIQUE() on the Month and Year columns to remove duplicates
• Quick-select the full month and year range with Ctrl + Shift + Down Arrow
• Copy and paste values using Ctrl + C, then Ctrl + Shift + V
• Create a Date column for charting by referencing the month-year rows so Excel treats it like a clean axis

4. Count quotes by month using COUNTIFS

• In your summary table, create a Quotes column
• Use COUNTIFS() with these criteria:
  • Quote Given = Yes
  • Month = the month in the current summary row
  • Year = the year in the current summary row
• Fill the formula down for all months

5. Count sales by month using COUNTIFS

• Create a Sales column next to Quotes
• Use the same COUNTIFS() logic, but include Sold = Yes as the criteria
• Fill the formula down

6. Calculate Quote-to-Sale Conversion %

• Create a Conversion % column
• Divide Sales by Quotes for each month
• Format as percentage with Ctrl + Shift + 5
• Fill down for all months

7. Add a Target line that stays dynamic

• Add a Target % cell (example used in the video: 60%)
• Under it, reference the target cell so you can fill down and keep it consistent across every month
• This creates a target series that updates instantly if you change the target value

8. Insert the combo chart with a secondary axis

• Select the Quotes column first
• Hold Ctrl and also select:
  • Conversion % column
  • Target % column
• Go to Insert → Recommended Charts
• If the percent line is on the same axis as the quote counts, switch to:
  • All Charts → Combo
• Set:
  • Quotes as clustered columns
  • Conversion % as a line
  • Target % as a line
• Turn on Secondary Axis for both Conversion % and Target % so they scale correctly

9. Clean up the chart formatting

• Rename the chart title to something like “Quote-to-Sale Conversion % by Month”
• Format the target line so it is easy to compare against the conversion line:
  • Change the target line color to black
  • Make the target line thinner so it does not dominate the chart

10. Add data labels only to the conversion line

• Avoid adding labels to everything, it gets noisy
• Click the conversion line only → Add Data Labels
• If labels are hard to read:
  • Format the label background fill to improve contrast
  • Keep it simple so the chart stays clean