Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

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.

High bay warehouse with pallets and cartons whose purchase prices are checked when comparing supplier price lists
Photo: CHUTTERSNAP on Unsplash

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.

  1. 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.

  2. Match by item number

    Use XLOOKUP or VLOOKUP to fetch the new price for every item in the old list.

  3. Calculate the change

    Work out the change in euros and in percent, and highlight large jumps in colour.

  4. Weight by annual quantity

    Multiply the change by the quantity you buy. That shows which increases actually cost money.

  5. 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.

What to check before comparing
CheckTypical problemFix in Excel
Price unitprice per 100 in the old list, per piece in the new onecalculate a unit price: price divided by price unit
Pack sizecarton of 50 instead of carton of 25convert to a unit price and compare the unit as well
Net or grossgross list price versus your net price after discount groupconvert both lists to your purchase price
Number format12,40 instead of 12.40, price stored as textconvert to a number with =NUMBERVALUE(A2,",",".")
Validitylist only applies from a later datenote 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:

  1. Unit price: in Old, a column OldPrice with =[@Price]/[@PU]; in New, a column UnitPrice with the same formula.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

Hands at a desk recalculating price changes on a calculator next to a keyboard
Photo: Towfiqu barbhuiya on Unsplash

An example from an installer's purchasing

Extract from the comparison in the example, net prices
ItemOld priceNew priceChangeAnnual qtyExtra cost per yearFinding
10-4711 sheathed cable, per 100 m€68.20€71.90+5.4 %120 × 100 m€444.00above threshold, review
22-1050 junction box€1.12€1.15+2.7 %2,400€72.00below threshold
31-0207 switch€4.80€4.800.0 %600€0.00unchanged
40-9001 cable ties€9.50 per 100€0.10 per piece+5.3 % after conversion50 × 100€25.00price 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.

Sources and further reading

FAQ

Quick answers.

How do I compare two price lists in Excel?

Match items by item number, not by row position. First convert both lists to the same unit price. Then use XLOOKUP or VLOOKUP to bring the new price into the old list and calculate the change with =New/Old-1 as a percentage. List new and discontinued items separately so nothing gets lost.

What is the Excel formula for a price increase in percent?

Divide the new price by the old one and subtract 1: =New/Old-1. Then format the cell as a percentage. For an old price of €68.20 and a new price of €71.90, that gives an increase of 5.4 %. Always compare prices in the same price unit, or the result means nothing.

How can I highlight price changes in colour in Excel?

Calculate the change in its own column and use conditional formatting. Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, for example with 0.03 for every increase above 3 %. A second rule with Less Than 0 highlights reductions in a different colour, so both directions stand out.

Is there an Excel template for a supplier price comparison?

You do not need a ready made template. One row per item, one column per supplier with the converted unit price and =MIN() for the lowest price are enough. Add columns for terms such as early payment discount, freight, minimum order and lead time, because the lowest unit price is not always the cheapest purchase.

Why does my comparison show price cuts of almost 100 %?

Usually the supplier changed the price unit, for example from per 100 pieces to per piece. The price itself has barely moved. Convert both lists to a unit price by dividing the price by the price unit. The comparison then shows the real change, which is often only a few percent.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.