We recently posted a new article on our website that has a number of useful suggestions for companies that use Excel to manage their bills of materials (BOMs). Following these recommendations will set you up for a faster, smoother and cleaner transition when you’re ready to move to an automated BOM management system (yes, full disclosure, like Arena BOMControl), but they’ll also help you NOW—and for as long as you continue to use Excel for BOM management.
Four recommendations for better Excel BOMs
- Be consistent. Use the same columns in the same order in every Excel bill of materials. Use a standard format for part numbers, manufacturer names, file titles and other types of data.
- Use standard templates. Get in the habit of hiding (not deleting) columns that aren’t needed in a particular BOM and creating separate spreadsheets for doing analyses that require additional columns. Give each column a single purpose, and label every piece of data in your Excel BOM spreadsheet.
- Have part numbering and part naming conventions – and a single location to store them. Develop and document a standard way to number and name ALL parts, and then manage those part numbers and names in a single location, like an item master or master parts list.
- Minimize repeated data. Include only as much data as is needed for each BOM to perform its core function of capturing the relationships between parts and assemblies. Store additional part data in the item master instead of multiple Excel BOMs, so updates only need to be made in one place.
Following these suggestions will help reduce confusion, duplication and errors in your Excel spreadsheet BOMs. For more details, read the full article and help your organization prepare for growth.