Compare Supplier Price Lists in Excel with Pack and Unit Checks

To compare supplier price lists in Excel, match the same product, normalize each price to the same unit, then separate real price changes from pack-size, currency and coverage changes. A supplier price comparison using a lookup alone can pair the right SKU while comparing the wrong amounts. The workflow below gives you a worksheet structure, formulas and a synthetic example where a higher pack price produces a smaller unit-price increase.

Use copies of the old and new files. You need a stable supplier item code, numeric prices, price-basis quantities, comparable units and currency. The formulas target Excel versions that support XLOOKUP; the worked arithmetic was verified independently with Python Decimal, rather than through an Excel user-interface test.

Define the product key and the price basis

Begin with the supplier's stable code. If several suppliers use the same short code, include supplier identity in the key. If the file contains product variants, confirm that the key distinguishes the size, material or other variant that changes what is being purchased.

Keep codes as text, especially where leading zeros matter. Do not replace missing codes with similar descriptions automatically. A description can change while the product remains the same, and two products can share similar descriptions. Put uncertain matches into a review list instead of assigning a price increase to an unproven pair.

Define the base unit for each product. A case of ten pieces can be compared with a case of twelve only after calculating price per piece. A kilogram price and a per-piece price need a substantiated conversion before they are comparable. Keep the source pack description alongside the normalized quantity so a reviewer can inspect the conversion.

Record what the amount includes. Tax, freight, discounts, minimum-order quantities and effective dates can change the purchasing decision. This example compares listed product prices only, with no freight or tax. Do not label its result as landed cost or a complete procurement saving.

Create two source tables with the same field meanings

Put each source into an Excel table named Old and New. Use these fields:

Give every import a source filename and effective date. Preserve the original files so an unexpected value can be checked against what the supplier actually sent. If the new file covers only selected categories, record that scope before interpreting missing products.

In each source table, calculate UnitPrice:

=[@PackPrice]/[@UnitsPerPack]

Validate UnitsPerPack before using the formula. A blank or zero denominator should enter a review queue; it should not become a zero price. Also review nonnumeric prices, negative amounts and repeated keys according to your purchasing rules.

Use a duplicate check in both tables:

=COUNTIF(Old[SKU],[@SKU])

For a row in Old, the expected count is one. Use New[SKU] when checking the new source. Resolve duplicates before relying on a lookup because a returned value can hide additional matching records.

Match the files while retaining missing-item status

Use the new list as the working set for changed and added items. Add OldMatchCount to New:

=COUNTIF(Old[SKU],[@SKU])

A count of zero identifies a new or unmatched item; a count greater than one identifies an ambiguous old-source match. Only a count of one is eligible for the ordinary before/after calculation.

For matching rows, retrieve the old unit price:

=IF([@OldMatchCount]=1,
    XLOOKUP([@SKU],Old[SKU],Old[UnitPrice],"",0),
    "")

Retrieve OldCurrency and OldUnit using the same exact-match pattern with Old[Currency] and Old[Unit] as the return arrays. Microsoft's XLOOKUP documentation describes the function's arguments and exact-match behavior. It also notes that XLOOKUP is unavailable in Excel 2016 and Excel 2019. If you use one of those versions, choose a supported lookup or Power Query approach and verify it against the same fixture.

Check missing items in the opposite direction. In Old, count matches against New[SKU]. A zero count means the old item is absent from the supplied new file. It does not prove that the supplier discontinued the product; the new export could be incomplete or have a different coverage scope.

Calculate change only for comparable rows

Before calculating the percentage, require one old match, a valid new unit price, matching currencies and comparable base units. If any condition fails, show a named review reason rather than a numerical change.

Once those conditions pass and OldUnitPrice is greater than zero, calculate:

=([@UnitPrice]-[@OldUnitPrice])/[@OldUnitPrice]

Format the result as a percentage. Keep the raw numeric calculation for sorting and thresholds. When the old price is zero, the percentage is undefined under this formula; show the absolute change and an explicit zero-baseline status instead of a misleading infinite or blank percentage.

Set a review threshold appropriate to the purchasing context and label it as your policy. A flagged change needs inspection of product identity, unit basis and the source values. A large change may be a real price revision, but it may also result from a new pack basis or an incorrect quantity.

Keep currency mismatches out of the percentage calculation. If your workflow needs conversion, specify the exchange-rate source and date and separate supplier price change from exchange-rate effects. This tutorial does not supply exchange rates or calculate a cross-currency increase.

Check the worked supplier revision

The following records are fictional and use comparable piece units:

In this fixture, C appears only in Old and D appears only in New. Report C as missing from the new source and D as new to the comparison. Do not assign either a percentage change because there is no approved before/after pair.

For A, comparing pack totals gives a 32% increase: (132 − 100) ÷ 100. Comparing normalized unit prices gives 10%: ((132 ÷ 12) − (100 ÷ 10)) ÷ (100 ÷ 10). The pack quantity changed as well as the price, so those percentages answer different questions.

The unit-price and percentage calculations were executed with Decimal arithmetic on the synthetic inputs. A produced 10%, B produced zero and E was classified as incomparable. This verifies the fixture's mathematics; it does not verify a supplier's records, an Excel installation or the completeness of your export.

Before applying the workflow to real files, enter the fixture into your own workbook and confirm the same results. Then try a duplicate code, an unknown unit, a zero quantity and a zero old price. The expected result is an explicit review status for each invalid or incomparable condition, rather than a plausible-looking change value.

Review before updating a purchasing system

Split the report into changed, unchanged, new, missing and incomparable records. Keep the old and new raw prices, pack quantities and normalized values visible. A reviewer should be able to explain why a row entered its category without opening a hidden formula chain.

Do not make the comparison itself an automatic update instruction. Confirm the supplier, source coverage, effective date and approved product mappings before exporting changes for another system. Preserve the reviewer decision and the values it approved. If a later file corrects a price, you need to know which revision was actually applied.

If an existing spreadsheet or utility handles your occasional revision, use that route. PriceListIQ's supplier comparison guide also discusses identifier matching and price units; the basic problem already has useful answers. The worksheet here adds a checked pack-size example and an explicit incomparable category, without claiming that all existing tools miss those issues.

Build a repeatable approval workflow with Atoms

A recurring purchasing process may need more than a workbook: named uploads, saved mappings, comparison history, exception ownership and an approved export. Describe those requirements through the AI App Builder, including the exact unit-price rules and fixture results.

Use synthetic files first. Verify that a currency mismatch stays incomparable, duplicates block ordinary matching and a missing product does not become an automatic deletion. Then test saved approvals, export contents and access to supplier files. A generated screen or local calculation cannot establish those runtime behaviors.

If you are replacing the broader tracking workbook, the spreadsheet migration guide explains how to define stable IDs, field ownership and repeat-import behavior. Preserve the purchasing system's authority until the approved update route has been tested.

Conclusion

Match the product, normalize the price basis and preserve review reasons before interpreting a supplier revision. Keep new and missing items separate from changes. To make the process repeatable, give Atoms the comparison rules and checked fixture, then verify the saved review and approved export before using live supplier data.