Skip to content
BOMproof

Free download

Bill of materials template for Excel

A BOM in Excel that calculates the quantities itself: per line you enter the level and the quantity per assembly, the totals and the flat list for purchasing follow automatically. With a filled-in example and an explanation per column.

Download the template Excel (.xlsx), no macros, no sign-up needed

What is in it

Three worksheets

  • 01

    BOM

    The indented list with level, item, part number, description, type, material or standard, quantity, unit, revision, make or buy and supplier. Excel calculates the total per line.

  • 02

    Flat (purchasing)

    Per part number the total over the whole product. Put each part in the list once, the quantities are added up from the BOM.

  • 03

    How to use

    What to enter per column, how to add lines and what to watch out for. Handy when someone else takes over the list.

Example

What the example looks like

A frame with two roller assemblies. The roller assembly occurs twice, so you need twice as many of every part below it. The template does that multiplication itself.

Example of an indented bill of materials with totals
Level Item Part number Description Type Material or standard Qty Unit Total
0 – VB-1000 Frame with rollers Assembly – 1 pcs 1
1 1 VB-1100 Roller assembly Assembly – 2 pcs 2
2 1 VB-1101 Roller Make part S235JR 1 pcs 2
2 2 VB-1102 Shaft Make part C45 1 pcs 2
2 3 6204-2RS Ball bearing Bought part – 2 pcs 4
1 2 VB-1200 Frame, weldment Make part S235JR 1 pcs 1
1 3 ISO 4762 - M8 x 30 - 8.8 Socket head cap screw Standard part ISO 4762 - M8 x 30 - 8.8 8 pcs 8
1 4 ISO 7089 - 8 - 200 HV Washer Standard part ISO 7089 - 8 - 200 HV 8 pcs 8
1 5 MAT-K40 Square tube 40 x 40 x 3 Material S235JRH 2.4 m 2.4

Qty is the quantity per one of the assembly above. Total is what you need for one product: for the ball bearing 2 per roller × 2 roller assemblies = 4.

How it calculates

Level and quantity, the rest follows

Every line gets a level: 0 for the product, 1 for what sits directly below it, 2 for the parts of a subassembly. The total is the quantity on that line times the total of the nearest assembly above it one level lower.

That is why the total stays right when you change a subassembly from 2 to 3 pieces: everything below it is recalculated. That is exactly the mistake most often made by hand.

What to watch out for

  • Parts sit directly below their assembly, in the order of the drawing.
  • Do not overwrite the Total column; copy an existing line when you add one.
  • Use the same notation for standard parts everywhere, for example ISO 4762 - M8 x 30 - 8.8.
  • Enter material by length in metres and fill in the unit.

Questions

About the template

01 Is the template really free?

Yes. You do not have to sign up and you may adapt it and share it within your company.

02 Does it also work in LibreOffice or Google Sheets?

The formulas are ordinary functions without macros, so they also work in other spreadsheet programs that open Excel files. After opening, check that the totals in the example are right.

03 Can I put my BOM from CAD into this template?

Yes. Export the BOM from your CAD package to Excel and paste the columns into the template. If you only have a STEP file, get the parts and quantities out of it with the free STEP to Excel tool.

04 Why a level column and not numbered items like 1.2.3?

With a level per line the template can calculate the totals without you having to link items. The item stays the balloon number on the drawing.

Ready for your first automated check?

Book a 30-minute demo. Bring one of your own designs and see live how BOMproof checks it.