How to Create a Bill of Materials in Excel (Step-by-Step)

You know every screw on the assembly table. What you do not have is a single file purchasing will trust. Learning how to create a bill of materials in excel is less about fancy charts and more about a repeatable sequence: header, columns, hierarchy, costs, then a revision you can defend in a stand-up.

This walkthrough builds a BOM from a blank workbook. If you need column theory first, read bill of material format in Excel. To choose free vs structured downloads, use the pillar bill of materials template excel download. More guides: /blog.

Key takeaway: Create a BOM in Excel by locking a header (product, revision, date), building a Level→Part→Qty→UoM→Cost table with extended-cost formulas, filling top-down, validating UoM/Make-Buy, then logging the revision before you email the file.

Before You Type: Gather Inputs

Pull these into a scratch note first:

Finished product name and internal code
Latest drawing or CAD export of parts
Known suppliers or "TBD"
Rough unit costs (mark estimates)
Whether each line is Make or Buy

Hardware teams at Fictiv also recommend deciding your part-number scheme before the first row — sequential or intelligent prefixes — so you are not renumbering mid-build.

Step-by-Step: Build the Workbook

How to create a bill of materials in Excel

New workbook — Name the first sheet BillOfMaterials. Add a second sheet Revisions and optionally PartList.
Header block (rows 1–5) — Product Name, Product Code, BOM Revision (start at A), Date, Prepared By. Keep this above the table; do not merge into data columns.
Column headers (row 7) — Level | Part Number | Description | Quantity | UoM | Unit Cost | Extended Cost | Make/Buy | Supplier | Notes.
Excel Table — Select headers + empty rows, Ctrl+T. Name the table BOM.
Extended Cost formula — In the first data row: =[@Quantity]*[@[Unit Cost]] (or =D8*F8 if not using structured refs).
Data validation — Dropdown lists for UoM (pcs,kg,m,L) and Make/Buy (Make,Buy).
Fill Level 0 — One row for the finished good if you track it, or start at Level 1 for components only — pick a convention and stick to it.
Add children — Increase Level for sub-assemblies; quantity is always per parent.
PartList sheet — Unique parts with longer descriptions and supplier contacts (optional but useful).
Revisions sheet — Log Rev A, today's date, your name, "Initial release."
Save as — PRODUCTCODE_BOM_vA_YYYY-MM-DD.xlsx.
Share — Send the revision letter in the email subject, not just the attachment.

Filling Rows Without Chaos

Work parent first, then children. If you dump every fastener at Level 1 with no parent context, quantity explosions later will be guesswork.

Keep descriptions short. Put material/grade in Description; put "see drawing REV C note 4" in Notes. Format Part Number as Text before paste so 00012 does not become 12.

For costs, enter unit cost only. Let the formula compute extended cost. Sum Extended Cost at the bottom or with SUBTOTAL on the table for a quick material total — remember this is materials only, not labor.

Warning: Do not delete rows to "clean up" after a design change without bumping the revision. Duplicate the file first, then edit.

Multi-Level Tips (Without VBA)

You can create a multi-level BOM in Excel without macros:

Use Level integers, not only visual indent
Sort by a helper "outline path" column if you need a printable tree (e.g. 1, 1.1, 1.1.1)
For total demand of a leaf part, filter by Part Number and multiply through parents manually on small BOMs

When assemblies hit hundreds of unique parts, Reddit's Excel community often moves quantity explosions to Power Query, Access, or MRP software. That is progress, not failure — Excel did its job as the authoring format.

Guides such as LetsWorkWise cover the same column set for manufacturers; adapt UoM lists to your region but keep the structure.

Faster Path: Start From a Structured Template

If column setup is eating the afternoon, open a pre-labeled workbook. The PlanoNest Bill of Materials Template includes Start Here navigation, BillOfMaterials, PartList, and Revisions sheets as a one-time purchase (see product page for current pricing). Upload to Google Sheets if collaborators prefer comments. Browse related ops templates in /collections/inventory-crm.

Free downloads from Smartsheet or ProjectManager also work as training wheels — compare what you get in our free BOM template article.

Sanity Check Before Production

Every purchased part has a supplier or explicit TBD
Every Make part has an owner (even if only in Notes)
UoM list has no duplicates
Extended Cost still formulas
Revisions sheet matches the header revision letter
File name includes version and date

Imagine a simple desk lamp assembly:

Level 1: Shade assembly (Buy) qty 1
Level 2 under shade: Fabric shade, harp, finial
Level 1: Neck tube (Make) qty 1
Level 1: Base casting (Buy) qty 1
Level 1: Cord set (Buy) qty 1
Level 1: Fastener kit (Buy) qty 1

Enter the shade assembly first, then its children with Level 2 and quantities per shade — not per lamp — if the shade is a sub-assembly you also stock. If you never stock the shade alone, you can flatten to Level 1 lines only. The important part is choosing one story and documenting it on Start Here.

Extended costs should update as you type unit costs. If they do not, your formula column was overwritten.

Collaboration Habits

One editor at a time on the master file, or use Sheets with clear ownership
Comments for questions; Revisions sheet for decided changes
Never "save as Final_Final2.xlsx" without bumping the revision letter
Store the master in a shared drive path that includes the product code

Teaching how to create a bill of materials in excel to a contractor? Send the Start Here sheet and a two-minute loom — not a blank grid.

Quality Pass Before You Call It Done

Ask a colleague who did not build the sheet to:

Find the current revision in under ten seconds
Filter all Buy parts
Explain one Level 2 quantity in their own words

If they stumble, clarify labels before production uses the file.

Template Shortcut

Column setup is the slow part. A structured BOM workbook with Start Here, PartList, and Revisions removes that fixed cost as a one-time purchase — confirm pricing on the product page — so you spend time on part accuracy instead of formatting.

Formulas Worth Pasting

Useful starter formulas once your table is named BOM:

Extended cost structured ref: Quantity times Unit Cost on each row
Material total: SUM of the Extended Cost column
Count of Buy lines: COUNTIF on the Make/Buy column for Buy

Conditional formatting can highlight blank Supplier cells on Buy rows so purchasing gaps show up before you send the file.

Importing From CAD or CSV

If engineering exports a CSV:

Open CSV in a scratch sheet
Map columns to your standard order
Paste values into the BOM table (not whole rows with rogue formatting)
Reapply Part Number as Text
Rebuild Extended Cost formulas if paste killed them

Never let CAD column order silently redefine your how-to-create standard. Your template owns the schema; imports adapt to it.

Handoff Email Template

Subject line pattern: [BOM] PRODUCTCODE Rev B — please build from this file.

Body checklist: revision letter, path to file, what changed since the previous revision, who owns questions. Attach the dated filename. This boring email prevents half of BOM disasters.

Frequently Asked Questions

How do I create a BOM list in Excel quickly? Header → columns → Table → qty×cost formula → fill top-down → revision log.

Do I need VBA for a multi-level BOM? Not for small trees. Use Level numbers; add tools when explosions get heavy.

How should I name BOM files? Product code + revision + date.

Can I start from a template instead? Yes — free or structured one-time downloads both skip blank-sheet setup.

Where does make-or-buy go? Its own dropdown column.