An effective and practical way to handle the materials, parts, and components needed for manufacturing or assembly operations is to create a **Bill of Materials** (**BOM**) in **Excel**. You can quickly manage, calculate, and update your **BOM** using **Excel’s** robust spreadsheet features. This manual will show you how to make a **Bill of Materials** in **Excel** so you can track inventory, evaluate expenses, and organize your production planning. **Excel **can be a helpful tool to build and maintain your **BOM** efficiently.

**Table of Contents**Expand

## What Is a Bill of Materials in Excel?

A complete description of the parts, pieces, raw materials, and sub-assemblies needed to produce or assemble a product is known as a bill of materials (**BOM**). Each item’s specifics are covered in full, including part numbers, descriptions, quantities, and occasionally other specs.

You can generate a **BOM **using **Excel **by arranging the pertinent data in a systematic manner. The columns of a table often contain information such as the part number, description, quantity, unit of measurement, cost, and supplier information. In contrast, each row normally represents a single item or component.

When dealing with a **BOM**, **Excel** has a number of features that might be helpful, including sorting and filtering options, formulae for calculations (such as adding up numbers or figuring out costs), and conditional formatting to draw attention to particular items or values.

You can easily manage and update the list as necessary by keeping a **BOM** in **Excel**. It enables you to keep track of inventories, evaluate expenses, schedule production, and communicate with other parties engaged in the manufacturing process.

Small and medium-sized firms can easily build and manage their **BOM**s using **Excel**. Enterprise resource planning (**ERP**) or specialized product lifecycle management (**PLM**) software may be better appropriate for larger-scale operations or when more sophisticated functionality is needed.

**Download Practice Workbook**

You can download the practice workbook from here.

## How to Make a Bill of Materials in Excel: 2 Types

In this segment, we will discuss **2 **types of **BOM **calculation. One will deal with a single selected product, the other one for multiple selection of products. We have created a **drop-down** list in cell **B5**, with data from our “**BOM**” worksheet. Follow the link if you don’t know that already.

### Type 1: Making Bill of Materials for a Single Product

We will discuss evaluating the **BOM **for a single product first. We will create a drop-down list, and use different formulas of **Excel **to create the **BOM**. Let’s see the procedures.

#### Step 1. Create Basic Dataset for Making Bill of Materials

- Primarily, we need a dataset. We have taken a dataset where there are some product lists with the components needed to make the product. we‘ll utilize these data to make a
**BOM**. observe our dataset in the**BOM**worksheet.

#### Step 2. Prepare Helping Table

- Now, let’s create the helper columns to create the
**BOM**for a single product. - Let’s compile the components for the products in a column. Use the following formula to do that. Copy the formula in the following cells with the
**Fill Handle**.

`=OFFSET(BOM!$C$4,,ROW()-8,,)`

In our dataset, the **Component Quantity **is the total number of components used for a selected product whereas the **Component with Quantity **is the measurement of the no. of individual components of the product.

In the code, the **OFFSET **function in Excel is used to reference a range of cells based on a starting point and specified rows and columns. This formula references the range starting from cell **BOM!$C$4** and adjusts the row offset based on the current row minus **8**. The range referenced will have the same number of rows as the current row and extend horizontally from the starting cell.

- Then, apply the following formula in
**Cell F8**to find the required components for a product. Use**Fill Handle**to copy the formula in the following cells.

`=IFERROR(INDEX(BOM!$C$4:$ZZ$9999,MATCH($B$5,BOM!$B$4:$B$9999,0),MATCH(E8,BOM!$C$4:$J$4,0)),0)`

**Formula Breakdown**

**INDEX(BOM!$C$4:$ZZ$9999, MATCH($B$5, BOM!$B$4:$B$9999, 0), MATCH(E8, BOM!$C$4:$J$4, 0))**

**The INDEX** **function** retrieves the value from a range (**BOM!$C$4:$ZZ$9999**) based on the row and column numbers provided by the **MATCH** functions.

The first **MATCH** function (**MATCH($B$5, BOM!$B$4:$B$9999, 0)**) searches for the value in cell **$B$5** in the range **BOM!$B$4:$B$9999** and returns the row number where a match is found.

The second **MATCH** function (**MATCH(E8, BOM!$C$4:$J$4, 0)**) searches for the value in cell **E8** in the range **BOM!$C$4:$J$4** and returns the column number where a match is found.

**IFERROR(formula, 0)**

**The IFERROR function** checks if the formula in step **1** returns an error.

If there is an error (e.g., no match found), it returns **0**. Otherwise, it returns the value from the **BOM** sheet.

- Afterward, count the number of components for a product. Use the given formulas.

In cell **G8**.

`=IF(F8>0,1,0)`

In cell **G9**.

`=IF(F9>0,G8+1,G8)`

- Copy the second formula in the following cells using the
**Fill Handle**.

#### Step 3. Find Component and Component Quantity for Single Selected Item

- Later on, use the following formula in cell
**B8**to get the required components for a selected product from list. Use**Fill HAndle**to copy the formula.

`=IFERROR(INDEX(E:E,MATCH(ROW()-7,G:G,0),1),"")`

**Formula Breakdown:**

**INDEX(E:E, MATCH(ROW()-7, G:G, 0), 1)**

The** INDEX** function retrieves the value from column **E** based on the row number provided by the **MATCH **function.

The **MATCH** function **(MATCH(ROW()-7, G:G, 0))** searches for the row number (offset by** 7**) in column **G** and returns the position of the matched row.

**IFERROR(formula, “”)**

The **IFERROR** function checks if the formula in step 1 returns an error.

If there is an error (e.g., no match found), it returns an empty string (**“”**). Otherwise, it returns the value from column** E**.

- Next, find the
**Component Quantity**by using the following formula. Copy the formula in the following cells with the**Fill Handle**.

`=IFERROR($C$5*INDEX(F:F,MATCH(ROW()-7,G:G,0),1),"")`

**Formula Breakdown:**

**$C$5 * INDEX(F:F, MATCH(ROW()-7, G:G, 0), 1)**

The** INDEX** function retrieves the value from column** F** based on the row number provided by the **MATCH **function.

The **MATCH **function **(MATCH(ROW()-7, G:G, 0))** searches for the row number (offset by **7**) in column** G** and returns the position of the matched row.

The retrieved value from column** F** is then multiplied by the value in cell** $C$5**.

**IFERROR(formula, “”)**

The** IFERROR** function checks if the formula in step 1 returns an error.

If there is an error (e.g., no match found), it returns an empty string (**“”)**. Otherwise, it returns the result of the multiplication.

#### Step 4. Calculate Total Price of Product

- Finally, use the following formula to calculate the total price of the product, we assumed the unit price was $25.

`=C5*D5`

### Type 2: Making Bill of Materials for Multiple Products

So far, we have shown the procedure to make **BOM **for a single selected product. Now, we will show the same procedures but for multiple selected products. Let’s see the procedures.

#### Step 1. Modify for Finding Component and Component Quantity for Multiple Selected Products (Helper Table)

- Firstly, use the following formula in
**Cell B6**to enlist the component quantity for each component for multiple selected products. Copy the formula in the following cells with the**Fill Handle**.

`=IFERROR($C16*INDEX(BOM!$C$4:$ZZ$9999,MATCH($B16,BOM!$B$4:$B$9999,0),MATCH(B$4,BOM!$C$4:$J$4,0)),0)`

**Formula Breakdown:**

**$C16 * INDEX(BOM!$C$4:$ZZ$9999, MATCH($B16, BOM!$B$4:$B$9999, 0), MATCH(B$4, BOM!$C$4:$J$4, 0))**

The **INDEX **function retrieves the value from the **BOM** range (**BOM!$C$4:$ZZ$9999**) based on the row and column numbers provided by the **MATCH** functions.

The first **MATCH** function (**MATCH($B16, BOM!$B$4:$B$9999, 0**)) searches for the value in cell **$B16** in the range** BOM!$B$4:$B$9999** and returns the row number where a match is found.

The second **MATCH** function (**MATCH(B$4, BOM!$C$4:$J$4, 0**)) searches for the value in cell **B$4 **in the range **BOM!$C$4:$J$4** and returns the column number where a match is found.

The retrieved value from the **BOM** range is then multiplied by the value in cell **$C16**.

**IFERROR(formula, 0)**

The** IFERROR **function checks if the formula in step 1 returns an error.

If there is an error (e.g., no match found), it returns **0**. Otherwise, it returns the result of the multiplication.

- Next, calculate the total requirement of a component for all selected products. Use the following formula in
**Cell B5**.

`=SUM(B6:B13)`

#### Step 2. Determining Component Name and Component Quantity

- Then, insert the component names in cell range
**K5:K12**.

- Afterward, insert the following formula in cell
**L5**to get the component quantity required. Copy the formula in the following cells with the**Fill Handle**.

`=HLOOKUP(K5,$4:$5,2,0)`

- In the formula,
**The HLOOKUP****function**searches for the value in cell**K5**within the range of rows**4**to**5 ($4:$5)**. It returns the value from the second row of the matched column. The final argument of**0**specifies an exact match.

#### Step 3. Determine Total Price of Product

- Simply, apply the following formula to get the total price of products.

`=C16*D16`

In the formula, we multiplied **Unit Price **with **Quantity **to get the **Total Price**.

## The Advantages of Making a Bill of Materials in Excel

Managing and organizing your manufacturing or assembly operations can be made easier by creating a **Bill of Materials** (**BOM**) in **Excel**. These benefits include:

**Accessibility: Excel**is extensively used and available, making it simple for both people and companies to utilize. The majority of computers come preinstalled with**Excel**, enabling effortless collaboration and sharing of BOMs between teams or suppliers.**Flexibility:**Excel offers a framework that allows you to design your**BOM**in accordance with your unique requirements. You are free to arrange and format the**BOM**as you see fit, adding or removing columns, using calculations, and emphasizing key information with conditional formatting.**Calculation and Analysis:**You may carry out calculations and analysis on your**BOM**using the built-in formulae and functions of**Excel**. Quantities, prices, sums, and other crucial parameters can all be calculated automatically. This makes accurate cost estimation, inventory management, and budgeting possible.**Sorting and Filtering: Excel’s**sorting and filtering features make it simpler to arrange and examine your**BOM**. Components can be arranged alphabetically, sorted by quantity or price, and you can exclude certain parts based on criteria like supplier or part number.**Updates and revisions:**You might need to update your**BOM**when your manufacturing process changes.**Excel**offers a simple method for editing and revising documents. You may alter prices, add new components, change quantities, and swiftly disseminate changes across the entire BOM.

## Frequently Asked Questions

**Can I create reports or summaries in Excel using the BOM?**

**Ans:** Definitely. Excel has powerful reporting features. You can use the BOM data can to create detailed reports and summaries using formulas, pivot tables, and charts. This enables you to assess costs, monitor inventory levels, spot patterns, and come to wise judgments based on the available information.

**Is it feasible to input data into Excel for the BOM from other programs or systems?**

**Ans: **Excel supports Data import from a variety of sources. Excel allows you to import data from databases, other software programs, and CSV files. This improves the accuracy and effectiveness of your BOM management by enabling you to incorporate data from supplier databases, inventory management systems, or other pertinent sources.

**Exist any restrictions or difficulties while using Excel for a BOM?**

**Ans:** Excel has some restrictions even though it is a flexible tool. For improved functionality and scalability, large-scale operations with vast BOMs and complex production processes may need specialist software. Manual data entering and updating can also be time-consuming and error-prone. To minimize potential problems, it’s crucial to set up reliable data management procedures and make sure that you make backups frequently.

Although **Excel** can be a useful tool for organizing BOMs, it’s important to evaluate your unique requirements and take into account whether other software solutions would be more appropriate for your purposes.

## Things to Remember

- Be careful regarding the Cell reference used in the formulas.
- Use the
**Fill Handle**to avoid the repetition of writing the same formula.

## Conclusion

For effective administration, a **Bill of Materials** (**BOM**) created in **Excel **offers accessibility, flexibility, calculating abilities, and collaborative tools.** Excel **is a useful tool for small to medium-sized enterprises to streamline inventories, costs, and production planning even though it may have limitations for complex procedures. For larger operations, you can solve issues by analyzing unique demands and taking specialized software into account. Hopefully, this article will help you to make a bill of materials easily. Feel free to comment if you have queries or suggestions.