Tree diagrams use graphics to indicate the materials comprising a project or product and the relationships between those materials. A bill of materials, or BOM, performs the same function but with text instead of images. One benefit of creating a BOM in Excel is that you can hide a project's details to reveal its larger components. Excel's Sort and Subtotal functions make this abstraction possible. The Subtotal function automatically creates an interactive outline of your materials list, and you can use the outline's controls to reveal or hide specific materials.
Create a new workbook in Excel, and then type the column headings for your bill of materials. The headings should reflect all attributes of a part that are relevant to the completed project. For example, type a "Categories" heading to be able to classify a part according to its function or appearance. Other headings include Quantity, Part ID, Part Name and Part Description. The following list contains additional headings that could pertain to your project. The sample headings used for this project are Part Name, Subassembly1 and Subassembly2.
Part Supplier Cost Time (needed to apply part to project) Function (of the part in the project) Subassembly
Type the entry for each part in the rows under the headings you entered. Use the sample table below for this project, and press "Tab" wherever you see a comma. This data represents the tags of a hypothetical HTML page and contains the headings in the top row. By studying this sample data – its subassembly content in particular – and creating a BOM for the data, you'll be able to create your own BOM after completing this project.
Part Name, Subassembly1, Subassembly2 paragraph1, body, div1 anchor1, div1, paragraph1 span1, div1, paragraph1 anchor2, div1, paragraph2
Click and drag a selection region around the data, then move your mouse over the left or right edge of any of the alphabetic columns occupied by the data. For example, if one of your BOM columns is under Excel's "H" column, move your mouse over the left or right edge of the "H" heading.
Double-click your mouse when the cursor changes to a cross with double-headed arrow. Excel resizes each column to fit its widest content.
Right-click the selected data and then click the "Custom Sort" command from the Sort submenu. Click "Subassembly1" from the Sort By control, then click the "Add Level" button so you can sort by a second field. Click "Subassembly2" from the "Then by" control. Click "OK" to close the Sort dialog. Sorting your materials list by subassembly lets you clearly see all materials for each subassembly.
Click the "Subtotal" button on the Data tab, then click the "Subassembly1" item from the At Each Change control. This action tells Excel to enable outlines based on the Subassembly1 field. Click the "Count" function from the Use Function control and tick the "Subassembly" check box under the Add heading.
Click "OK" to close the dialog. Excel displays a tally of all rows in your materials list that have the same subassembly. This behavior makes it easier to see each subassembly's materials. Excel also displays outlining buttons next to the worksheet. Click the set of "-" buttons closest to the worksheet to hide individual parts in the list while revealing the subassemblies. Click all "+" buttons to view your completed bill of materials.
- Information in this article applies to Excel 2010. It may vary slightly or significantly with other versions or products.
- Jupiterimages/Comstock/Getty Images