EXCEL AND LISTS
Compare price lists in Excel: find every price change from your supplier
It is Friday afternoon and the wholesaler has sent the new price list: 3,000 rows, nothing marked. You put the old list next to it, scroll, check a few samples and still cannot shake the feeling that an increase slipped through somewhere. At that size, comparing two price lists in Excel row by row is hard to get right. You will only find out when a job earns less than you costed it for.

That costs you twice: hours spent comparing, and margin when a price increase quietly finds its way into your quotes. This article shows how to compare price lists in Excel, find every price change and put a euro figure on it per year, so you can approve new prices with confidence.
You will learn how to make prices comparable first, which formulas you need and how to weight changes by annual quantity. An example from an electrical contractor shows where the traps are.
The short answer: how do you compare two price lists in Excel?
Comparing two price lists means matching every item in the old list to the same item in the new list by item number, converting both prices to the same unit and calculating the change in percent. New and discontinued items are listed separately.
- Make prices comparable
Convert both lists to the same unit price. Watch the price unit, the pack size and whether prices are net or gross.
- Match by item number
Use XLOOKUP or VLOOKUP to fetch the new price for every item in the old list.
- Calculate the change
Work out the change in euros and in percent, and highlight large jumps in colour.
- Weight by annual quantity
Multiply the change by the quantity you buy. That shows which increases actually cost money.
- Review and approve
Have a second person check changes above a threshold before prices go into your inventory system.
Why you make prices comparable first: units, net and gross
Most wrong results happen before the first formula is written. The price unit states the quantity a price applies to. Cable is often priced per 100 metres, screws per 1,000. If the supplier changes the price unit, a barely changed price suddenly looks like a 99 % drop. False alarms like that cost time and trust in your own analysis. A few minutes of checking up front saves you both.
| Check | Typical problem | Fix in Excel |
|---|---|---|
| Price unit | price per 100 in the old list, per piece in the new one | calculate a unit price: price divided by price unit |
| Pack size | carton of 50 instead of carton of 25 | convert to a unit price and compare the unit as well |
| Net or gross | gross list price versus your net price after discount group | convert both lists to your purchase price |
| Number format | 12,40 instead of 12.40, price stored as text | convert to a number with =NUMBERVALUE(A2,",",".") |
| Validity | list only applies from a later date | note the valid from date, archive the old list |
VALUE only partly helps with number formats, because it follows the regional settings of your computer. With NUMBERVALUE you state the decimal and thousands separators yourself. It is also available in Excel 2016 and Excel 2019.
In German and Austrian electrical and plumbing wholesale, price lists often arrive as Datanorm files, an exchange format for item and price data that is widespread in German speaking countries. Trade software usually reads them directly. Discounts are often kept separately in discount groups. An increase can then hide in a changed discount group while the list price stays the same. In that case, compare your net prices, not just the list prices.
Step by step: compare prices in Excel and highlight changes
Turn both lists into tables with Ctrl+T and name them “Old” and “New” under Table Design > Table Name. The formulas assume both tables have the columns ItemNo, Price and PU (price unit), and that Old also has AnnualQty. Then add these columns:
- Unit price: in Old, a column OldPrice with =[@Price]/[@PU]; in New, a column UnitPrice with the same formula.
- New price: in Old, a column NewPrice with =XLOOKUP([@ItemNo],New[ItemNo],New[UnitPrice],"discontinued"). XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024. In Excel 2019 and earlier, use VLOOKUP with FALSE as the last argument and catch missing items with IFNA. The article on how to compare two Excel lists explains the matching in detail.
- Change in percent: in Old, a column Change with =IF(ISNUMBER([@NewPrice]),ROUND([@NewPrice],4)/ROUND([@OldPrice],4)-1,""), formatted as percent. Rounding stops tiny calculation differences from showing up as changes.
- Highlight in colour: select the Change column and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. With 0.03 you colour every increase above 3 %. A second rule with Less Than and the value 0 shows reductions.
- New items: in the New table, =COUNTIF(Old[ItemNo],[@ItemNo])=0 shows every item that has been added. More on that in finding missing records.
What does the price increase really cost? Weight by annual quantity
A 12 % increase on an item you buy twice a year hardly matters. A 3 % increase on your most purchased cable does. Take the annual quantity from the purchase statistics in your inventory system, in the same unit as the unit price, and calculate per item: =IFERROR(([@NewPrice]-[@OldPrice])*[@AnnualQty],0)
The sum of this column is your extra cost per year at the same volume. Divide it by your previous purchase volume, =SUMPRODUCT(Old[OldPrice],Old[AnnualQty]), and you get the weighted price increase. With that figure you go into supplier talks well prepared, and your costing is accurate again.
Set thresholds for approval
Not every change needs a follow-up. Agree once, in writing, what is accepted automatically and what someone has to look at. An example of such rules:
- Changes below 3 % and below €100 extra cost per year are accepted.
- Increases of 3 % or more, or €100 or more in extra cost, are reviewed by purchasing before import.
- Increases of 10 % or more are raised directly with the supplier.
- Reductions of 20 % or more suggest a changed price unit or a typo and are always checked.
The limits depend on your product range and margins. What matters is that they are fixed. Then it is not the mood of the day that decides which change gets noticed, and the check still works when the colleague in charge is on holiday.

An example from an installer's purchasing
| Item | Old price | New price | Change | Annual qty | Extra cost per year | Finding |
|---|---|---|---|---|---|---|
| 10-4711 sheathed cable, per 100 m | €68.20 | €71.90 | +5.4 % | 120 × 100 m | €444.00 | above threshold, review |
| 22-1050 junction box | €1.12 | €1.15 | +2.7 % | 2,400 | €72.00 | below threshold |
| 31-0207 switch | €4.80 | €4.80 | 0.0 % | 600 | €0.00 | unchanged |
| 40-9001 cable ties | €9.50 per 100 | €0.10 per piece | +5.3 % after conversion | 50 × 100 | €25.00 | price unit changed, review |
How such prices then flow into quotes is shown in our example from the trades.
How do you compare several suppliers in Excel? The price comparison sheet
Sometimes the question is not old versus new, but three quotes for the same need. You do not need a template for this. Create one row per item and one column per supplier with the converted unit price, for example columns C to E, with the supplier names in row 1.
- Lowest price: =MIN(C2:E2)
- Cheapest supplier: =XLOOKUP(MIN(C2:E2),C2:E2,$C$1:$E$1) returns the name from the header row.
- Compare terms as well: discount, early payment discount, freight, minimum order and lead time belong in their own columns. The lowest unit price is not always the cheapest purchase.
Common mistakes when comparing price lists
- Comparing rows side by side: new and discontinued items shift every row. Always match by item number, never by row position.
- Missing the price unit: a switch from per 100 to per piece creates fake reductions of almost 99 %. Always convert to a unit price.
- Simple average: the mean of all changes says little about your costs. Weight by annual quantity.
- Duplicate item numbers: if an item appears twice with different prices, XLOOKUP returns the first match without any warning. Check the numbers with COUNTIF beforehand.
- Taking prices without approval: otherwise a supplier's typo goes straight into your quotes. Set a threshold above which a second person reviews.
When is Excel enough, and when does an office helper pay off?
For one list a year, Excel is enough. It gets hard with several suppliers using their own formats, and often only one person in the company knows exactly how the process works. When that person is on holiday, the new list waits. A cautious estimate: four suppliers send new lists every quarter, and each takes about three hours to prepare, compare and import. That is 48 hours a year. At an assumed internal cost of €38 per hour, meaning wages plus overheads, as preset in the cost calculator, that comes to €1,824. A price jump missed in open quotes comes on top. Check your own figures with the office work cost calculator.
The free price list comparison does the comparison itself. You set your threshold there, 5 % by default, and download the result as an Excel file with previous price, new price, difference and change in percent. Two things stay with you. The tool does not convert price units, so a switch from per 100 to per piece shows up as a steep reduction. And the average it shows is a simple average across all items in both lists. For the extra cost, add the annual quantity to the Excel file and multiply the difference by the quantity, as described above.
If several lists arrive every quarter, an office helper, a small program we set up for exactly this task, can fetch the files from the mailbox, convert units, weight by annual quantity and present only the changes above your threshold for approval. The decision stays with you: prices only go into your inventory system once you approve them. It works with the programs you already use, so nobody has to learn new software. More on the page about automating Excel work. The number of suppliers and lists decides whether this pays off. Enter it in the free workflow check and you get a personal, free assessment within 1–2 working days. Small projects are done 3–7 days after the workflow is agreed, at a fixed price from €1,990 excl. VAT.
