Most Arena customers make the move to Arena from Excel. In other words, they manage their bills of materials (BOMs) in Excel until they start managing their BOMs in Arena. We help these companies get their data into Arena, so we’ve seen a LOT (tens, maybe even hundreds, of thousands) of Excel BOMs over the years and learned a great deal about what works best when it comes to managing BOMs and other product data in Excel.
We shared four strategies for better Excel BOM management in a recent post. Here are ten more tips, which we recently published in conjunction with a fictionalized account of a real company that relies on an intricate web of Excel spreadsheets to manage its product data.
Keep it simple. Use a simplified, predetermined set of numbers to identify vendors and parts.
Take care with your conventions. Excel can interpret numeric strings as dates, which can wipe out your data in a flash.
Secure your data. Minimize the number of people authorized to make changes to a spreadsheet, use locked workbooks, protect vital data with passwords, make sure shared files are read-only and back up your data frequently.
Watch out when importing data. Excel makes it too easy to overwrite data, so avoid importing data into Excel unless you can guarantee that the source file and destination spreadsheet template are 100% identical—even if you have to enter data twice instead.
Avoid hidden worksheets. While hidden worksheets provide a measure of protection, formulas remain active and anyone can reveal and edit an unprotected worksheet.
Sort all your data. Always select the entire spreadsheet when sorting, because that’s the only way to ensure that you’ve included every cell.
Don’t spread your data too far. Spreading related data across too many Excel worksheets or workbooks makes analyzing data and generating reports more difficult.
Check yourself. Whenever you modify a spreadsheet, generate a BOM or enter data, inspect and verify your work carefully before you release it to others.
Keep good records. Maintain a separate log file for auditing, and keep separate digital and physical records for compliance.
Plan ahead. Lay the groundwork now for expansion to other tools such as BOM management and ERP by standardizing your Excel spreadsheets and using import-friendly templates whenever you can.
Watch for a follow-up post with recommendations that will help when it’s time to make a move: 10 tips for preparing your BOMs and product data to be transferred out of Excel.