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.
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.
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.
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:
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.
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:
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
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.
Bill of materials (BOM) series — the site's thirteen articles on the BOM, from the concept to the ERP:
- Bill of materials (BOM): definition, types, roles of PDM, PLM and ERP
- Multi-level, single-level, indented or summarized BOM
- Where-used: find every assembly that uses a part
- Phantom BOM and phantom assembly
- BOM vs routing: bill of materials and routing
- BOM compare (this page)
- eBOM vs mBOM: the difference, with an example
- BOM to ERP integration: the 4 architectures
- BOM management software: how to choose
- Free multi-level BOM Excel template
- Part numbering and properties: where a correct BOM starts
- ECR / ECO: controlling BOM changes
- PDM / PLM glossary: eBOM, mBOM, revision, vault…