Arena Blog

10 simple steps to free your BOMs from Excel

linked-in icon twitter-icon facebook-icon

Excel lock

Don't let Excel keep your BOM on lockdown.

Imagine where you want your business to be in five years. Can you get to that place if you’re still managing your BOM revisions and changes in Excel?

For most manufacturers, the answer is no. Excel is a shortsighted business tool—not well equipped to manage change and collaboration with internal teams, let alone a global supply chain.

When you’re ready to move out of Excel, here are 10 simple tips for getting your data into a more long-term BOM management tool—all while protecting the integrity of your existing data.

Start by getting your existing product record in order.

1. Establish a consistent naming convention across all cells.

Your computer does not know that “AVX Corp.” in your master supplier list is the same part source as “AVX.” So avoid confusion down the road and clear it up now.

2. Simplify the look of your spreadsheet.

Colored text and cells may look nice but they make your spreadsheet more complicated than it needs to be, increasing the odds that you will lose data in the migration process. Rather than fancy formatting, use a separate column to draw attention to important information.

3. Protect your data from accidental corruption with data validation lists.

Validation List BOM

Validation Lists, set up under Data in the Menu bar, help protect your data from typos.

You can limit the choices for entry in a given cell by setting up validations lists, which let you dictate a pre-determined set of entries that are acceptable for a given cell. You can then restrict the cell to only accept entries offered in that list.

4. Double check your number formats.

You can indicate what type of data belongs in each cell (dates, currency values, percentages, etc.) Choosing the right format protects your data from problems like the loss of leading zeros.

5. Get rid of any placeholder values.

If you don’t know a particular part number, leave it blank. A mock entry can accidentally be interpreted as a valid value.

Once you’ve cleaned house, you’re ready to start your data migration.

6. Determine which data you want to move.

We recommend you only prepare live data for the move. You can keep archived data in Excel or move it over at a later date.

What happens when you manage BOMs in Excel?

  • Spreadsheets become too complex to track and data entry becomes disorganized

  • Money is lost as engineering and manufacturing waste time finding and correcting errors in spreadsheets

  • Collaboration between departments on the product record is stifled as confusion arises over the latest spreadsheet version

  • CMs may receive a faulty or outdated BOM record

  • Inconsistent data entries result in scrap and rework cost overruns

7. Start small and work your way up.

Prepare your smallest dataset for migration first—this makes it easier for you to uncover and repair kinks in your current data entry processes.

8. Double and triple check your data is clean and stable.

Are you using a consistent method for part naming and descriptions? Are your numbers in their correct formats? Checking that the data is clean will help you avoid problems with bad data in your new system.

9. Squash any lingering typos.

Because of the nature of data entry in Excel, there is no simple way to catch typos. But here’s one trick that may help: identify any values that occur several times throughout your spreadsheet and run a simple ‘find’ search using a portion of the entry that is the least likely to have been mistyped (such as the first few values). For each finding, manually check to make sure the rest of the value in the cell is correct.   

Besides mistyped values, beware of forgetting commas between reference designators.

10. Think like a computer.

Make sure all your values are entered consistently and labeled clearly. Avoid relying on subjective or visual cues like tabs, fancy formatting or indents.

I hope these simple tips for a smooth data migration will help you prevent miscommunications and inefficiencies down the road. And better management of the product record means you’re one step closer to actualizing your ultimate business vision.

About the Author

Alyssa Sittig

Alyssa played an instrumental role in the development of the Arena Blog and social media channels from 2011 to 2012. Joining the team with a background in public policy, Alyssa ...

Read More 


How every department contributes to new product introduction (NPI) A couple of weeks ago I shared some big picture questions to ask when launching a new product.While a lot ...
Read more

How Jameco Electronics connects to customers online From the first analog devices to today’s highly technical digital components, electrical engineering has always been about pushing the boundaries ...
Read more

Documenting Fixtures and Jigs—Why It’s Forgotten, and Why It Matters I wrote a bit on Tuesday about the importance of documenting part information during the design process, and today I’d ...
Read more