Bill of Material Format in Excel: Columns That Work
Set a bill of material format in excel that production can trust: Level, part number, qty, UoM, costs, make-or-buy, and revision fields without merged-cell chaos.
Someone forwarded a "final" BOM where Level lives in column H, quantities are text, and half the part numbers have leading spaces. That is not a data problem yet — it is a bill of material format in excel problem. Format is the contract between engineering, purchasing, and whoever opens the file at 7 a.m. on the build day.
This article locks the column schema: what belongs in which order, when to use single-level vs indented multi-level, and how to keep units and costs consistent. For the bigger free-vs-DIY-vs-structured decision, start with our pillar on bill of materials template excel download. For a full build walkthrough, see how to create a bill of materials in Excel. More guides: /blog.
Key takeaway: A reliable bill of material format in excel puts Level → Part Number → Description → Qty → UoM → Unit Cost → Extended Cost (formula) → Make/Buy → Supplier in a flat table, avoids merged data cells, and logs revisions on a separate sheet.
Why Format Beats Decoration
AI overviews and template roundups often say a BOM needs Part Number, Description, Level, Quantity, Unit of Measure, Unit Cost, and Extended Cost. That list is correct — and incomplete. Format also means:
- One header row, frozen
- No merged cells in the data grid
- Consistent data types (numbers for qty/cost, text for part IDs)
- Stable column order so CSV export to MRP does not shuffle meaning
Skip the long free-vs-paid debate here — this page assumes you already picked a sheet and need the columns right.
Recommended Column Order
Use this left-to-right order unless your ERP already dictates otherwise. Keeping the core block identical across products makes training faster.
Recommended BOM column order
| Col | Field | Type | Notes |
|---|---|---|---|
| A | Level | Integer | 0 = top assembly, 1 = child, 2 = grandchild… |
| B | Part Number | Text | Preserve leading zeros; format as Text |
| C | Description | Text | Material, size, grade — short |
| D | Quantity | Number | Per parent, not per job lot |
| E | UoM | Text | Controlled list via data validation |
| F | Unit Cost | Number | Currency format |
| G | Extended Cost | Formula | =D*F |
| H | Make / Buy | Text | Dropdown |
| I | Supplier | Text | Or "TBD" |
| J | Notes | Text | Alternates, tolerances |
Put product header metadata above the table or on a Start Here sheet: product name, product code, BOM revision, date, prepared by. Do not merge those cells into the data columns.
Single-Level vs Multi-Level (Indented) Format
Single-level
Every component for the finished good appears once, flat. Good for simple products or early prototypes. Weak for "how much of part X do we need across all sub-assemblies?" without extra formulas.
Multi-level / indented
Children sit under parents. You can show hierarchy with indentation or a Level number. Prefer Level numbers. Reddit threads on multi-level BOM explosions repeatedly hit the same wall: indentation-only sheets break when sorted. A Level column survives filter, sort, and Power Query.
When rolling quantities, remember: child quantity is per parent. Exploding to top-level demand multiplies through the tree — advanced Excel or a proper MRP tool helps once the tree is deep. For small assemblies, a helper column with running parent keys is enough.
Units, Costs, and Make-or-Buy Rules
Units of measure. Pick a controlled vocabulary (pcs, kg, m, L). Ban free typing. Data validation dropdowns prevent pc / pcs / each fragmentation that wrecks pivots.
Costs. Unit cost is an input; extended cost is a formula. If you paste values over formulas, your quote will lie. Highlight formula columns in a light fill so people leave them alone.
Make vs Buy. Hardware guides from Fictiv treat this as a first-class field. It drives who owns lead time. Keep it as a two-value dropdown, not a paragraph.
Suppliers. One primary supplier column is enough for Excel. Put alternates in Notes or a PartList sheet — see the PlanoNest BOM template PartList approach (one-time purchase; pricing on the product page).
Formatting Rules That Prevent Pain
Lock a clean BOM format
- Convert the data range to an Excel Table (Ctrl+T) so formulas auto-fill.
- Freeze the header row; freeze column A if Level must stay visible.
- Format Part Number as Text before pasting from CAD exports.
- Turn on Wrap Text for Description only — keep other columns single-line.
- Add data validation for UoM and Make/Buy.
- Protect the Extended Cost column or whole sheet structure after review.
Warning: Do not color-code rows as your only hierarchy signal. Colors do not export cleanly and disappear for color-blind readers. Use Level.
Construction teams may add waste % or install phase; manufacturing teams may add reference designators. Add those fields to the right of the core block so the shared format stays readable. Industry-specific habits are covered in our construction and manufacturing guides.
Revision Format (Do Not Skip)
A format without revision control is a rumor. Add a Revisions sheet with columns: Revision, Date, Author, Summary, Linked filename. Bump the revision in the header whenever parts change. Fictiv's advice to log "what changed" in a revision tab is the minimum bar for shared builds.
If you need a workbook that already separates BillOfMaterials, PartList, and Revisions, the structured bill of materials template excel download from PlanoNest is built for that workflow — still a spreadsheet, not a PLM, but clearer than a single messy tab.
Related inventory tools: /collections/inventory-crm.
Header Metadata vs Line Items
Separate document metadata from line items. Metadata belongs in a title block or Start Here sheet: product name, product code, BOM revision, date, owner, approved-by. Line items belong in a rectangular table. Mixing approval signatures into column K of the parts grid makes every filter painful.
When you export CSV for an ERP trial import, export only the table range. Title-block rows confuse importers.
Part Number Formatting Pitfalls
CAD and ERP systems love leading zeros. Excel loves to help by converting 00045 into 45. Before pasting:
- Pre-format the Part Number column as Text
- Or paste with a leading apostrophe during cleanup, then standardize
- Never use General format for IDs that must round-trip to suppliers
Intelligent part numbers (SCRW-M3-10) are readable; sequential numbers are simpler to generate. Either works — inconsistency does not.
Print and Share Layout
For shop-floor printouts:
- Hide Unit Cost if the floor should not see margins
- Keep Level, Part Number, Description, Qty, UoM visible
- Set print area to the table; landscape often fits more columns
- Include revision and date in the header/footer
A bill of material format in excel that looks fine on a 27-inch monitor can clip on A4. Test print once before the build.
Cross-Link to Templates
If you would rather not rebuild columns, start from a labeled workbook such as the PlanoNest BOM template (one-time purchase; live pricing on the product page) and enforce the column order above. Return to the pillar guide when you are still choosing free vs structured sources.
Frequently Asked Questions
What is the standard bill of material format in excel? Teams converge on Level, Part Number, Description, Quantity, UoM, Unit Cost, Extended Cost, Make/Buy, Supplier, and Revision metadata — not a single mandated ISO spreadsheet.
Should I use indentation or a Level column? Use a Level column. Indentation alone fails under sort/filter.
How do I format extended cost?
=Quantity*UnitCost. Never hard-type the total.
Can construction and manufacturing share one format? Yes for the core columns. Add industry fields on the right.
Where should I put revision history? On a dedicated Revisions sheet with version, date, author, and change note.
