Comparing prices across multiple suppliers for a list of products is tedious to do manually and easy to get wrong. A price comparison tool in Excel automates the calculation, highlights the best price for each item, and calculates total landed cost so you're comparing apples to apples.
Your Price Matrix
Set up a matrix with products in rows and suppliers in columns. Each cell contains the unit price from that supplier for that product. Leave cells blank if a supplier doesn't carry that product.
Add a header row with supplier names and a column for your standard product description and unit of measure. Keep this consistent — if one supplier lists by case and another by unit, normalize before entering prices.
Finding the Lowest Price
Add a Best Price column using MIN:
=MIN(B2:F2)
Where B through F are the supplier price columns. Add a Best Supplier column using INDEX/MATCH:
=INDEX($B$1:$F$1, MATCH(MIN(B2:F2), B2:F2, 0))
This returns the name of the supplier with the lowest price for each product.
Conditional Formatting to Highlight Best Prices
Select the price matrix (excluding headers). Go to Format, Conditional formatting, and add a custom formula rule:
=B2=MIN($B2:$F2)
Apply a green fill. This highlights the lowest price in each row, making it immediately visible which supplier wins each product.
Total Landed Cost
Unit price alone doesn't tell the whole story — you also need to factor in shipping, minimum order quantities, and payment terms. Add rows for each supplier's shipping rate and minimum order. Calculate total landed cost per unit:
=B2+(B$52/B$53)
Where B52 is shipping cost and B53 is the minimum order quantity for that supplier. This amortizes the shipping cost across the minimum order to get true cost per unit.
Savings Summary
Add a Savings column showing how much you save per unit by choosing the best price vs. your current supplier:
=G2-H2
Where G2 is your current supplier's price and H2 is the best available price. Multiply by annual volume to see total annual savings. This is what makes the comparison actionable.
The Easy Way: Using SheetXAI in Excel
Example 1: You have supplier quotes already in the spreadsheet.
"I have price quotes from 5 suppliers for 30 products on Sheet 1. Build a comparison tool that highlights the best price for each product, identifies the winning supplier, calculates total landed cost including shipping, and shows total savings vs. our current supplier."
SheetXAI reads your quotes, adds the MIN and INDEX/MATCH formulas, applies the conditional formatting, and builds the savings summary.
Example 2: Your pricing data comes from supplier portals or procurement systems.
"Pull current pricing from our three main suppliers and build a comparison matrix showing the best price for each SKU in our catalog."
SheetXAI connects to your systems and builds the full comparison.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Compare Vendors in Excel, How to Build an Inventory Tracker in Excel, and Google Sheets AI Guide.
