Menu Close

How to Create Bill of Materials in Excel

A bill of materials is a key document in product development and production. It allows all parties involved in the project to communicate clearly about all necessary components and their respective amounts.

But managing BOMs in spreadsheets is challenging. It’s difficult to know which file is the latest revision, and mistakes can occur.

Parts List

Whether you are assembling a Lego set or working on a complex production plan, a parts list is a fundamental tool. It allows you to keep track of every component, its description, and the quantity required to assemble the product. Moreover, it is an essential part of the production process for planning and estimating material costs. While there is special software for creating BOM, you can also use Microsoft Excel to create and edit it. In addition, you can find a number of ready-made templates for varying business needs.

The main goal of a BOM is to ensure that all components needed to produce a product are available for use during the assembly cycle. This helps to eliminate production delays and extra warehousing expenses. In addition, a good BOM can help you reduce costs by making sure that the right amount of each item is ordered.

While spreadsheets are convenient, they can be difficult to manage. For example, if you have multiple versions of the same spreadsheet BOM floating around, it can be hard to keep track of which one is the most current. This can happen when a team member makes a change to the document and doesn’t update it in the database.

When using a spreadsheet BOM, it is important to remember that the data in the spreadsheet must match the custom properties established in SOLIDWORKS. This means that you must define the name of each field (e.g., final output, SKU, category, UoM, and name) exactly as you would in a SOLIDWORKS document.

Materials List

Whether you’re working in engineering, design, manufacturing or operations and product management, documentation of your bill of materials (BOM) is essential. It can help you document the raw materials needed for a specific project, the cost of each item and sub-assembly and the total amount of money required to produce your product. This can also help you keep track of your inventory and streamline procurement.

While there are several software applications for documenting BOM, spreadsheets remain the most popular choice. Spreadsheets offer versatility, ease of use and accessibility. They can be easily shared with other team members, and they can be modified by multiple users at once. However, they can be difficult to keep track of changes and revisions. This can be especially difficult for companies with a large number of BOM files.

The template listed below is a simple but effective way to manage multi-level BOMs in an Excel file. It can be adjusted to add as many levels of hierarchy as you need and has space for information about the assembly phase, production status and supplier or vendor contact details.

You can also add the appropriate metric or unit of measurement for each part or component. This can be particularly helpful when you’re using external suppliers or manufacturers. Similarly, you can include the purchase price of each item, which will allow your finance team to make informed purchasing decisions.

Assembly List

A BOM is a critical component of the design process that helps to control costs and reduce waste. It is also useful for communicating between teams and other stakeholders, including internal departments, industry experts, project or product owners, and vendors and suppliers. There are many different methods of creating a BOM, such as manual creation, spreadsheets, or using specialized software. Each has its own benefits and disadvantages.

Excel-based BOMs are easy to use and can be customized for your specific requirements. They can help you track all of the parts, assembly levels and quantities required for a particular assembly or production run. However, they can be difficult to maintain and are prone to errors. It is also difficult to determine which version of a BOM is the most recent, especially when multiple people have access to the same file.

Using a template helps to standardize communication between teams, as it requires everyone to follow the same procedures for ordering materials for each production cycle. It can also help to control inventory levels and improve productivity by minimizing production delays.

Specialized software for working with BOMs can be costly, but there are free and affordable options available as well. For example, the Katana PCB Designer allows you to insert a BOM within a drawing view and automatically updates when you add or remove components.


A bill of materials is a document that lists all the raw materials and components required to create a product. This can be used in a variety of ways, including production planning and inventory management. It is important to ensure that all the information contained in a BOM is accurate and up-to-date. Otherwise, it can lead to disputes, delays, and cost overruns.

A well-designed BOM template can be a valuable tool for your business. It can help ensure that your products contain the correct ratios of each component and material, and that they are assembled correctly. It can also improve communication between the manufacturer and suppliers.

Using a bill of materials template allows you to standardize the format and naming conventions for your BOMs. This will simplify your communication with all stakeholders and reduce the likelihood of errors. It will also help you control your inventory levels and ensure a successful production cycle.

The BOM template can also include costing information, procurement details, and other data pertaining to the assembly process. These data can be displayed as rows or columns in the BOM table, and they may contain additional fields such as reference designators or formulas. You can even use calculations and formatting to highlight key information in the BOM. This makes the template easier to read and understand, and it will allow you to make informed decisions about your project’s budget.