A step-by-step guide to computing quote-to-order conversion in Excel: joining quotes to orders on the quote reference with COUNTIFS, a fallback match on customer, item and a date window, conversion by count and by value, conversion per customer with a minimum count, conversion by turnaround band, orders with no quote, and the check that every quote is in exactly one state. Includes the exact formulas and the point at which the spreadsheet stops being enough.
Quote conversion is quotes that became orders over quotes sent. In Excel the work is the join between two files. This guide gives the formulas for the join, the rates and the check.
Sheet Quotes, one row per quote line:
| Column | Content |
|---|---|
| A | Quote reference |
| B | Customer |
| C | Item |
| D | Quote date |
| E | Quoted value |
| F | Request received, date and time |
| G | Quote sent, date and time |
Sheet Orders, one row per order line:
| Column | Content |
|---|---|
| A | Order number |
| B | Customer |
| C | Item |
| D | Order date |
| E | Order value |
| F | Quote reference, where carried |
On Calc: validity window in days B1, as-of date B2, minimum quotes per customer B3.
Quotes!H2:
=COUNTIFS(Orders!F:F,A2,Orders!C:C,C2)>0
Quotes!I2, only where the exact join failed:
=AND(NOT(H2),COUNTIFS(Orders!B:B,B2,Orders!C:C,C2,Orders!D:D,">="&D2,Orders!D:D,"<="&(D2+Calc!$B$1),Orders!F:F,"")>0)
The last criterion restricts the fallback to orders with no quote reference, so an order already claimed by another quote is not matched twice.
J2:
=IF(H2,"Converted, exact",IF(I2,"Converted, probable",IF(D2+Calc!$B$1>=Calc!$B$2,"Open","Lost")))
Decided quotes:
=COUNTA(Quotes!A2:A50000)-COUNTIF(Quotes!J:J,"Open")
Converted:
=COUNTIF(Quotes!J:J,"Converted*")
Rate is converted over decided. Show the exact and probable counts separately beside it. By value:
=SUMIFS(Quotes!E:E,Quotes!J:J,"Converted*")/(SUM(Quotes!E:E)-SUMIFS(Quotes!E:E,Quotes!J:J,"Open"))
Customers in Calc!A10 down. Decided quotes, B10:
=COUNTIFS(Quotes!B:B,A10)-COUNTIFS(Quotes!B:B,A10,Quotes!J:J,"Open")
Rate, C10:
=IF(B10>=$B$3,COUNTIFS(Quotes!B:B,A10,Quotes!J:J,"Converted*")/B10,"under minimum")
Sort ascending among customers above the floor. The bottom rows, high quote counts and single-digit conversion, are the price-checkers.
Turnaround in hours, Quotes!K2:
=(G2-F2)*24
Band, L2:
=IF(K2<=8,"Same day",IF(K2<=24,"Next day",IF(K2<=72,"Within 3 days","Longer")))
Conversion per band with COUNTIFS on column L. Working hours would be more exact; elapsed hours is enough to see the pattern.
On Orders, G2:
=AND(F2="",COUNTIFS(Quotes!B:B,B2,Quotes!C:C,C2,Quotes!D:D,"<="&D2,Quotes!D:D,">="&(D2-Calc!$B$1))=0)
These are unquoted orders: repeat business and list-price sales. They are not conversions and are not added to the rate. Their share of order value is worth showing on its own.
=COUNTIF(Quotes!J:J,"Converted, exact")+COUNTIF(Quotes!J:J,"Converted, probable")+COUNTIF(Quotes!J:J,"Open")+COUNTIF(Quotes!J:J,"Lost")-COUNTA(Quotes!A2:A50000)
Zero.
Requotes counted as new quotes. Three revisions of one quote, one order: 33 percent. Keep the latest revision per reference, or strip the revision suffix into its own column.
Loose join. Customer-only matching counts every repeat order as a conversion.
Expired quotes left open. The denominator shrinks and the rate climbs.
Whole-column COUNTIFS on large files. Two hundred thousand quote lines against a million order lines will take minutes to calculate. Limit the ranges or use Power Query to merge the tables.
A quarter's quote log is a good spreadsheet job. Weekly, across branches and estimators, with line-level partial conversions and the fallback join audited, is where it strains; see the four signs a spreadsheet is no longer enough. For what the rate should be, see what is a good quote conversion rate; for the arithmetic by hand, quote conversion on ten quotes. Covirage runs the same join on the same two files every week, with the match method shown per quote and the check built in.
Use the fallback: same customer, same item, order date between the quote date and the quote date plus the validity window. It over-matches for customers who order the same item regularly, so show exact and probable matches as separate counts, and ask for the reference to be made mandatory at order entry. That one field is worth more than any formula here.
Work at line level: one row per quote line, matched to order lines on reference and item. Conversion by value then reflects partial wins properly. A quote-level view, won if any line was ordered, flatters the rate; state which you use.
Ten quotes in the period is a reasonable floor. Below it, show the counts and leave the rate out. One quote lost is zero percent and means nothing; fifty quotes with four orders is a conversation.