You sent the same RFQ to five suppliers and got back five quotes that look nothing alike. One prices per case, one per unit. One buries freight in a footnote, another lists it as a line. One is a tidy PDF, two are scans, and the last is a photo someone took of a printout. Before you can say which bid is actually the cheapest, all of it has to land in one spreadsheet with matching columns. Here is how to build that comparison in Excel without keying every line by hand, and how to make sure the number you award on is the real one.
How do I compare vendor quotes in Excel?
Put every quote into one sheet with a shared row for each item and one column per vendor, so the prices sit side by side. The fastest path is to convert each quote into rows first, normalize the units so you are comparing like for like, then use a MIN or INDEX-MATCH formula to flag the lowest bid per line and conditional formatting to highlight it. The work that decides whether this takes ten minutes or two hours is getting the quotes into clean, numeric cells in the first place.
The structure that holds up is a single comparison tab. Down the left, a column of item descriptions or part numbers that every vendor quoted. Across the top, one column per supplier. Each cell holds that supplier's unit price for that row. Add a couple of helper columns on the right for the lowest price and the winning vendor, and you have a sheet you can sort, total, and defend in a sourcing review.
How do I set up a vendor quote comparison spreadsheet?
Start with a clean column of item identifiers, then add one price column per vendor and keep the layout identical for every supplier. Use a unique key per line, a part number where you have one, so a lookup can match rows even when vendors list items in a different order. Freeze the top row and the description column so the labels stay visible as you scroll, and keep one row per item rather than merging cells, because merged cells break sorting and formulas.
The detail people skip is matching rows across vendors. If Supplier A lists item 12 on row 4 and Supplier B lists the same item on row 9, comparing row to row gives you nonsense. Use the part number as a lookup key with INDEX and MATCH (or XLOOKUP) to pull each vendor's price into the right row, so the comparison lines up on what the item actually is, not on the order it happened to appear in the quote.
How do I normalize prices when vendors quote different units?
Convert every price to the same unit before you compare, usually a true per-unit price. If one vendor quotes $4.50 for a case of 25 and another quotes $1.25 for a pack of 5, divide each by its pack size (=4.50/25 and =1.25/5) so both become a price per single unit. Comparing the headline numbers without this step is the most common way buyers pick the wrong vendor, because the cheaper-looking quote is often just a smaller pack.
Do the same for any unit mismatch: per hour versus per day, per pound versus per kilogram, per thousand versus per each. Add a small helper column that records each vendor's quoted unit and pack size, then a formula column that converts to your standard unit. Once every price is expressed the same way, the comparison is honest and a sort by price actually means something.
What formula finds the lowest vendor price in Excel?
Use MIN across the vendor price columns for each row, for example =MIN(C2:E2) if columns C through E hold the three vendors' prices. That returns the lowest price for that item. To also name the winning vendor, pair INDEX with MATCH: =INDEX($C$1:$E$1, MATCH(MIN(C2:E2), C2:E2, 0)) returns the header (the vendor name) of whichever column held that low price. Copy both formulas down the column and every line shows its cheapest bid and who offered it.
To compare totals rather than line by line, SUM each vendor's column for an apples-to-apples grand total, but only after the prices are real numbers. If a price came in as text, MIN and SUM quietly ignore it or return zero, and you award on a total that is missing lines. Run a quick =COUNT() on each vendor column and check it equals the number of items; if it is short, some cells are text and need fixing before you trust the result.
How do I highlight the lowest price in Excel?
Use conditional formatting with a formula rule so the cheapest cell in each row turns green automatically. Select the vendor price cells, choose Conditional Formatting, New Rule, Use a formula, and enter a rule like =C2=MIN($C2:$E2) with the fill color set to green. Lock the row range with dollar signs on the columns so the rule compares across vendors but moves down row by row. Now the lowest bid in every line is visible at a glance, with no extra columns.
This is faster to read in a sourcing meeting than a wall of numbers, and it updates instantly if a vendor sends a revised price. You can layer a second rule to flag the highest price in red, or use a color scale across each row to show how far apart the bids are, which often reveals an outlier worth questioning before you award.
Should I choose a vendor on price alone?
No. The lowest unit price is rarely the lowest total cost. Build the comparison on landed, total cost of ownership: add freight, taxes, tooling or setup charges, minimum order quantities, payment terms, and lead time, because a quote that is two cents cheaper per unit but ships in eight weeks with a large minimum can cost far more than the headline says. Put each of these in its own column so the real total, not just the unit price, drives the decision.
For sourcing that is not purely commodity, weight non-price factors too: quality history, on-time delivery, warranty, and supplier risk. A simple weighted score, where price is one criterion scored 1 to 5 alongside the others, keeps the decision defensible when someone asks why you did not just take the cheapest bid. Document the weighting in the sheet so the award stands up to review.
How do I compare quotes that come in as PDFs?
Convert each quote PDF into rows first, then drop them into the comparison. A copy and paste from a PDF collapses the line items into one column and turns prices into text, which is exactly what breaks MIN, SUM, and sorting. Instead, run each quote through a converter that reads the table structure and keeps the numbers numeric, so the descriptions, quantities, and unit prices land in their own cells ready to compare. The PDF to Excel for procurement tool is built for this, and you can batch all the bids through at once when a sourcing event closes.
Scanned and photographed quotes need OCR before they become rows, which the converter handles automatically; just check the preview, since OCR can misread a faint digit on a low-quality scan. The same approach works for turning a purchase order PDF into Excel once you have chosen a vendor. If your bids arrive as text in the body of an email rather than as attachments, an email parser can pull those into a spreadsheet too. Once the award is made, tools like purchase order management software help raise and track the PO, and accounts payable automation handles matching the eventual invoice back to it.
Build the comparison on numbers you can trust
A vendor comparison is only as good as the data underneath it. Normalize the units, compare on total cost rather than headline price, and let formulas and conditional formatting do the flagging so a sort actually surfaces the best value. The one step that makes all of it reliable is getting the quotes into clean numeric cells, which is why converting the PDFs properly comes first. Drop your quotes into the procurement converter at the top of the page, confirm each one foots in the preview, and build the comparison on figures you verified instead of figures you retyped.