Since most small manufacturers manage their bill of materials (BOM) in Excel, it's tempting to try and comes up with a system directly in Excel for creating part numbers. People dream up and implement all sorts of innovative methods for creating part numbers in Excel: macros, visual basic scripts, and integrations to Access databases. But part numbering has several issues which tend to make simple solutions gather complexity as they evolve: concurrency of sequences, flexibility of formats, enforcement of rules to name but a few of the issues.

While it may be an interesting mental exercise to solve these types of problems in Excel, it's also a waste of time. If you're a small manufacturer, your time resources are usually tightly constrained. Your time should be spent designing and manufacturing your products; it should not be spent solving ancillary problems that are already solved and where the solutions are available for free.

Such is the case with part numbering.

Yes, you could spend an increasing number of your valuable hours creating and managing a part numbering generation scheme in Excel. After spending a lot of time, you might come up with a solution as good as the one offered for free from the part-numbering.com service. Or you might not.

The smart business solution is to sign up for a free part-numbering.com account and get back to designing your product.

When starting out as a small company, there is often a temptation to solve the "part numbering problem" by using the part number assigned to a part by the manufacturer, or vendor, of the part directly in the Bill of Materials (BOM). Basically, adopting their part numbers as your own.

Do not do this!

To make your BOM management easier, you should consider the parts, and part numbers, on your BOM to be abstract entities. As abstract entities, they are merely a representation of the actual part on the BOM; not the actual part itself. When you use a manufacturer or vendor part number directly in the BOM, this abstraction is broken. This makes it harder to manage your BOM if you want to change sources, or source a part from multiple vendors; use functionally equivalent parts from multiple manufacturers; or perform part substitution on the BOM.

Additionally, changing a part number on a BOM can be a hassle, you only want to change part numbers when this really needs to be done. Yet manufacturers or vendors can change part numbers for a variety of reason that have no effect on you or your BOM. Do you really want to update all of your BOMs every time a manufacturer gets bought by another, or when they decide to use a new ERP system?

If you want to keep track of vendor and/or manufacturer part numbers, these should be managed as additional properties associated with the part, external to the BOM. Then when the manufacturer or vendor part number changes, or sourcing information itself changes, the additional property information can be updated in one place and none of your BOMs need to be updated.

As soon as we added the ability to manage part properties in the part-numbering.com service, the requests started coming in to allow filtering by property values when browsing parts. Well, that day has come; you can now filter by part property values when browsing parts.

When browsing parts, if your account has properties configured and in use, there will be an additional Properties... section at the bottom of the filtering widget. By default, and because there can be a lot of properties in an account, this section is not expanded. If you want to filter by a property, you need to expand this section by clicking on the icon. Here is how it looks:

Once the properties section is expanded, it will show all of the properties in your account that are in use. Here is an example of how this may look:

To filter by a particular property, expand the section for that property. What is shown in the property section will be dependent on the type of property. Here is an example of how the property sections may look:

In this example, the Description property is a text property and Engineer is a selection, or multi-selection, property.

In both cases, you can filter to find parts that have the property associated with them but for which no value has been entered. These values are found by selecting the is unset option.

To find text properties with specific values set, enter the text to be searched for in the text input below the contains label. To find selection properties with specific values set, select the values in use from the select box shown below the is equal to label. In the case of select elements, if multiple values are selected, the parts that are returned will contain at least one of the selected values.

As with the other filters, when filtering occurs by a property this is indicated by changing the color of the property label so that it is red. Here is an example of the how the filtering widget may look when filtering by property values is taking place:

To remove a single property filter, close the section by which you no longer want to filter and click on the Filter Parts button. Only opened property sections are used as filters, so closing a section will result in that filter being removed. To remove all of the property filters at once, close the entire Properties... section and click on the Filter Parts button.

We've made it easier to see the property information associated with a part. You can now hover over the link for a part number and you'll be shown the property information in a shortcut panel. Here is how it looks in action:

To make the shortcut panel go away, remove the mouse from the link or from the shortcut panel.

The shortcut panel will show all of the properties associated with the part. But if the content of a property is longer than 200 characters, it will be truncated. You can still click on the part number to see all of the detailed information.

"It has been said that democracy is the worst form of government, except all the others that have been tried." — Winston Churchill

If you are using Excel to manage your bill of materials (BOM), you're in good company. While Excel is widely acknowledged as the worst possible tool to use for managing BOMs, it is also widely acknowledged as the most prevalent tool used to manage BOMs. Go figure.

If you are managing your BOM in Excel, here are some tips to help make your current, and future, job easier:

Keep your Excel files on a networked drive
One of the biggest down-sides of using Excel for BOM management, as opposed to a PLM software package, is that it's a lot harder to ensure that everyone is working from the same set of data. If Excel files are being sent around between engineers using e-mail, it's only a matter of time before different versions of the same BOM start to circulate. Once that happens, the real trouble starts. While you can never completely eliminate the possibility of this occurring when you are using Excel, you can help reduce this possibility by using a networked drive so that your Excel BOM files are all stored in one, centralized, location.

Create a system to keep multiple edits from occurring
Most PLM systems take care of merging changes made to BOMs, maintaining version history of the BOM, and keeping changes made by different users from unintentionally overwriting one another. With Excel as your BOM management tool, you lose this safety net. If multiple people are editing the same BOM at the same time, changes are going to get lost and/or overwritten. To help reduce the chance of this happening, come up with a system in your organization for "checking out" files so that they are only edited by one person at a time. This can be as simple as yelling out to your co-workers that you are working on a certain BOM; maintaining a list of "checked out" files on a white board in your office; or modifying the file names on a networked drive to indicate they are "checked out". Whatever the method, find one that works for your organization so that multiple people are not editing the same BOM at the same time.

Use version control on your files
While this is a little more advanced, consider using a version control system on your BOM Excel files. This is the standard tool used by software developers to help manage changes to documents when multiple people may make those changes at the same time. In the case of Excel, some of the benefits of a version control system are lost because the files are binary; as a result, merging can typically not be done. But the use of a version control system will allow users to easily revert to a previous version of the BOM, and it will alert users when they are committing changes that conflict with changes already made.

Be consistent
Think long term—someday, when your company grows, you will be using a PLM system instead of Excel for managing your BOMs. When that day comes, you will somehow need to get all of the data in your Excel spreadsheets into your PLM system. To keep that future transition from being a complete nightmare, maintain consistency in your BOMs today:

• File formatting
  Determine how your BOM files will look and then stick to that format. For example, you could decide that each Excel file will contain a single worksheet, with a single-level BOM, with header information about the assembly contained at certain cells in the spreadsheet, and specific columns for each part in the assembly. This is one way to maintain a BOM in Excel. Whatever format you choose, stick with it. If you decide you need to change your format, whatever the reason, make sure that all of your existing files are updated so that consistency is maintained. This will save a lot of trouble in the future.
• File naming
  Maintain a consistent manner of naming your files. Not only will this make upgrading to a PLM system easier at a later date, it will also make your job easier today.
• Maintain consistent part numbers
  PLM systems are notoriously picky about part numbers, they need to be unique and follow consistent formatting. Using the part-numbering.com service will help you maintain consistent part numbers.
• Column Names
  If you label the columns in your BOM spreadsheet, make sure those labels are consistent. For example, consider a column labeled "Part Number". Do not create BOMs with this column label changed to "Part #", or "Part Num". This happens a lot when BOMs are managed in Excel. As people are looking at the BOM, it is very easy for them (as human beings) to know what is meant. But for a computer, this is a lot more difficult, and having multiple names for the same column like this can make loading your data into another system, like a PLM system, much more difficult.
• Use Templates
  To help avoid consistency issues in naming and formatting, consider the use of templates.

Minimize the information stored in spreadsheets
Most parts have a lot of ancillary information that goes along with them; part name, description, costs, supplier information, etc. It is very tempting to store this information directly in the BOM in Excel so that it is immediately available when the BOM is being viewed. The problem with this is that the same part is likely to exist in multiple BOMs; when the information changes, which is inevitable, it needs to be updated in multiple places. It is hard to know all of the places where this information exists. And if one file is not updated when the information is changed, the information for that part is now in an ambiguous state. This can lead to errors occurring when the information in the spreadsheet is used. And it can make the future transition into a PLM system much harder. A better strategy is to maintain part information that is likely to change in an external source such as, ahem, the part-numbering.com service which allows property management.