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.
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.
| 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.
Read more
More about BOMs
Ready for your first automated check?
Book a 30-minute demo. Bring one of your own designs and see live how BOMproof checks it.