Price comparison template
By the Analistable team · Updated · 3 min read
A free workbook for comparing supplier quotes or price lists. Paste every supplier's lines into one Price lists sheet with pack price and pack size; the template works out a unit price, lays suppliers side by side per SKU, and shows the cheapest price, the cheapest supplier and the spread between highest and lowest.
Part of our guide: How to compare two spreadsheets for differences and matches
What the template does
Suppliers quote in different pack sizes, so comparing the headline prices is misleading: a box of 10 at 47.00 beats a box of 5 at 24.50. The template converts every line to a unit price first, then compares. For tracking changes in a single supplier's list over time, use comparing two price lists instead; for merging lists into one catalogue, see combining supplier price lists. Both sit under the comparing spreadsheets guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Price lists | Supplier, SKU, Description, Pack price, Pack size, Unit price | First five columns |
| Comparison | SKU, Description, one column per supplier, Cheapest unit price, Cheapest supplier, Spread | SKUs, descriptions and supplier names in row 1 |
How to fill it in
- Map each supplier's codes to your own SKU before pasting — supplier part numbers rarely match each other.
- Enter the pack price excluding VAT and the number of units in the pack.
- Put supplier names in C1 to E1 on Comparison, spelled as in the Supplier column.
- A blank cell on Comparison means that supplier didn't quote for the item.
How the formulas work
Each supplier cell averages the unit price for that supplier and SKU, and returns blank if there is no quote:
=IFERROR(AVERAGEIFS('Price lists'!$F$2:$F$500,'Price lists'!$A$2:$A$500,C$1,'Price lists'!$B$2:$B$500,$A2),"")MIN ignores the blanks, and INDEX/MATCH returns the supplier name from the header row:
=INDEX($C$1:$E$1,MATCH(F2,C2:E2,0))Spread shows how much you would overpay by picking the dearest option:
=ROUND(MAX(C2:E2)-F2,4)Worked example
| SKU | Item | Northline | Brightpack | Corestock | Cheapest | Supplier | Spread |
|---|---|---|---|---|---|---|---|
| P-01 | A4 copy paper (ream) | 4.9 | 4.7 | 5.1 | 4.7 | Brightpack | 0.4 |
| P-02 | Ballpoint pen | 0.18 | 0.17 | 0.17 | 0.17 | Brightpack | 0.01 |
| P-03 | Toner cartridge | 62 | 64.5 | 58.9 | 58.9 | Corestock | 5.6 |
| P-04 | Sticky notes pad | 0.6 | 0.55 | 0.5833 | 0.55 | Brightpack | 0.05 |
| P-05 | C5 envelope | 0.036 | 0.033 | 0.033 | Corestock | 0.003 | |
| P-06 | Lever arch file | 2.1 | 1.95 | 2.2 | 1.95 | Brightpack | 0.25 |
Brightpack's paper looks dearer per box (47.00) but comes in boxes of 10 reams, so it is the cheapest per ream. On a tie, the first supplier column with the lowest price is shown.
Limits and when to move on
The template compares price only — not delivery charges, minimum order values or lead times. It also needs one shared SKU per item, which is the slow part when suppliers describe the same product differently. When you are comparing many lists or long catalogues, load them into Analistable and ask 'cheapest supplier per SKU after converting to unit price'; the files stay in your browser.
Frequently asked questions
- Can I compare more than three suppliers?
- Yes. Insert columns before Cheapest unit price, name them in row 1, copy the AVERAGEIFS across, and widen the MIN, MAX and INDEX ranges.
- What about volume price breaks?
- Add a row per break with the pack size at that break, or keep one sheet per order volume you care about.
- Why AVERAGEIFS instead of SUMIFS?
- SUMIFS returns 0 for a missing quote, and 0 would always win as the cheapest. AVERAGEIFS errors instead, which IFERROR turns into a blank that MIN ignores.