BOM series Product data ~8 min read
Between two revisions

BOM compare: how to compare two bills of materials, in Excel or with a free tool

Published November 20, 2026 — what changed between two revisions of a bill of materials: the comparison key, the four statuses, flat or structured comparison, a worked example, Excel formulas, ERP and PLM tools, and a free comparison tool that runs in your browser.

Part no. The comparison key, never the line number
4 Statuses: added, removed, quantity changed, unchanged
CS14 The SAP BOM comparison transaction

In short: to compare two bills of materials, match them on the item number (plus the parent for a structured comparison), after adding up the quantities of the same item. Each item gets one of four statuses: added, removed, quantity changed or unchanged. A flat comparison tells you what to buy more or less of; a structured comparison tells you where the change happened. In Excel, SUMIF and COUNTIF are enough; a free comparison tool is in section 3.

1. The comparison key and the four statuses

Comparing two BOMs answers one simple question: what changed between the old and the new revision? Purchasing wants to know what to order more or less of, the warehouse what to set aside, production what to change at the workstation. The answer is a list of items, each with a status.

StatusRuleWhat it triggers
AddedAbsent from the old revision, present in the new oneItem creation if needed, procurement
RemovedPresent in the old revision, absent from the new oneWhat to do with stock and open orders?
Quantity changedPresent on both sides, different quantitiesAdjusting requirements, kits and picking
UnchangedSame item, same quantityNothing

Everything rests on the key used to match lines. It must be the item number (part number), never the line number or the find number: inserting a line shifts everything below, and a line-by-line comparison then reports dozens of differences that are not real. An item whose revision changes without a new number keeps the same key: add a "revision" column if you want to see those changes too. One more reason to look after part numbering.

Revision ARevision B1001 × 41001 × 41002 × 261002 × 301003 × 41006 × 4= unchanged− removed+ addedComparison key: the item number≠ quantity changed
Both lists are matched by item number, not by position: each item gets a status, and only the last three rows call for action.

2. Example: two revisions of a cart

Take the cart from the article on the multi-level BOM, in total quantities (summarized BOM). In revision B, the Ø 100 wheels are replaced with Ø 125 wheels fastened with 5 screws instead of 4, and the handle gets two end caps. Result of the flat comparison:

Item no.DescriptionQty AQty BDifferenceStatus
1001Tube 40 × 40440unchanged
1002M8 screw2630+4quantity changed
1003Ø 100 wheel40−4removed
1004Wheel plate440unchanged
1005Tube Ø 25110unchanged
1006Ø 125 wheel04+4added
1007Handle end cap02+2added

The 4 extra screws come from the wheel assemblies: 4 assemblies × 1 extra screw (8 under the frame + 4 × 5 under the wheels + 2 under the handle = 30). Out of seven items, four call for action. Replacing one wheel with another is not a "change" to line 1003: it is a removal and an addition, because they are two different items with different stock and suppliers. The move from A to B is decided and traced in an engineering change order (ECO), and the where-used of the Ø 100 wheel tells you which other products still use it before you clear its stock.

3. Free BOM comparison tool

Paste each revision, one line per item: item number; quantity, optionally followed by ; description. The separator can be a semicolon or a tab (direct copy from Excel). Identical item numbers are added up, spaces and letter case are ignored. The calculation runs in your browser: nothing is sent.

For a structured comparison, put the parent before the item number (for example WHEEL-ASM/1002;5): the key becomes the parent and item pair. The default values are the example from section 2.

4. Flat or structured comparison

A flat comparison works on total quantities per item, across all levels. It is the one purchasing and MRP need: it tells you how much more or less to buy. A structured comparison matches lines on the parent and item pair, level by level. It is the one engineering and manufacturing engineering need: it tells you where the change happened, and therefore which assembly station and which routing are affected.

Revision ARevision BCartCartWelded frameWelded frameM8 screw × 8HandleHandleM8 screw × 2no screwsM8 screw × 10Flat: M8 screws, 10 → 10, no differenceStructured: +2 under the frame, −2 under the handle
Two screws moved from one sub-assembly to another: invisible in a flat comparison, yet they change two assembly stations. Only the structured comparison shows them.

In practice, do both: the structured one to understand and approve the change, the flat one to cost it and procure it. Both assume complete, consistent BOMs; a phantom item must be handled the same way in both revisions, exploded or not, or its components show up as added on one side and removed on the other.

5. Comparing two BOMs in Excel

Put revision A in a sheet named RevA and revision B in a sheet named RevB (item number in column A, quantity in column B, headers in row 1). In a third sheet, list the unique item numbers of both revisions in column A: copy both columns one under the other, then Data > Remove Duplicates (in Excel 365, =UNIQUE(VSTACK(RevA!A2:A6,RevB!A2:A7)) does it in one go). Then, in row 2, copied down:

ColumnExcel or LibreOfficeFrench-language Excel
B: quantity A=SUMIF(RevA!A:A,A2,RevA!B:B)=SOMME.SI(RevA!A:A;A2;RevA!B:B)
C: quantity B=SUMIF(RevB!A:A,A2,RevB!B:B)=SOMME.SI(RevB!A:A;A2;RevB!B:B)
D: difference=C2-B2=C2-B2
E: status=IF(COUNTIF(RevA!A:A,A2)=0,"added",IF(COUNTIF(RevB!A:A,A2)=0,"removed",IF(D2<>0,"qty changed","unchanged")))=SI(NB.SI(RevA!A:A;A2)=0;"ajoutée";SI(NB.SI(RevB!A:A;A2)=0;"supprimée";SI(D2<>0;"quantité modifiée";"inchangée")))

SUMIF adds up duplicate item numbers automatically. The status tests whether the item is present (COUNTIF) rather than a zero quantity, so that a line present with quantity 0 is not taken as removed. A filter on column E then isolates what changed. These formulas were checked in LibreOffice Calc on the example from section 2 (statuses and differences identical to the table). For a structured comparison, add a key column =parent&"/"&item in each sheet and compare on that column. The free BOM Excel template gives the total quantities to compare.

6. In the ERP, PLM and CAD

  • ERP: in SAP, transaction CS14 compares two BOMs (two alternatives, two usages, or the same BOM at two validity dates). Other ERPs offer comparable functions; failing that, export both exploded BOMs and compare them as in section 5.
  • PLM: comparing structures between two revisions is a core PLM function; it is used to prepare the change order and to check that an mBOM follows the eBOM changes.
  • CAD and PDM: export the assembly BOM at each revision with the same columns and the same type (parts only, or indented), otherwise the comparison mixes different things.

7. False differences, and how to avoid them

CauseSymptomFix
Comparing by line numberEverything shifts after an insertionCompare by item number
Item numbers written differently"1002" removed and "1002␣" addedTRIM and UPPER before comparing
Leading zeros lost"00123" turned into 123 in one file, not the otherImport the column as text
Duplicates not added upQuantity differences that don't existAdd up per item (SUMIF)
Different units2 (m) vs 2,000 (mm)Compare the unit too, or convert
Phantom exploded on one side onlyComponents added and sub-assembly removedSame explosion rule on both sides

Printable version: the method as a PDF (A4, no sign-up): statuses, example, Excel formulas and false differences.

8. FAQ

How do you compare two BOMs?

Match the two lists on a key, the item number (plus the parent for a structured comparison), after adding up the quantities of the same item. Each item then gets a status: added, removed, quantity changed or unchanged. The line number and the find number are not keys: they change as soon as a line is inserted.

How do you compare two BOMs in Excel?

Put each revision in a sheet (item number in column A, quantity in B), list the unique item numbers of both sheets, then calculate for each one the quantity in each revision with SUMIF, the difference by subtraction, and the status with COUNTIF: absent from the old revision = added, absent from the new one = removed, non-zero difference = quantity changed.

Which SAP transaction compares two BOMs?

Transaction CS14 (BOM comparison) compares two SAP BOMs, for example two alternatives, two usages or the same BOM at two validity dates. PLM systems, for their part, offer a structure comparison between two revisions.

Should BOMs be compared flat or by structure?

Both, for two different questions. The flat comparison, on total quantities, tells you what purchasing and stock must change. The structured comparison, item by parent, tells you where the change happened: a part moved from one sub-assembly to another is invisible in a flat comparison but shows up in the structure.

Why does a BOM comparison find false differences?

Most often because of item numbers written differently (spaces, letter case, leading zeros dropped by the spreadsheet), duplicates not added up, different units (pieces vs metres) or phantom items exploded on one side and not the other. Clean and add up both lists before comparing them.

Read next