BOM
Formula errors in your BOM.
A BOM in Excel often runs on formulas: looking up in another list, referring to another tab. If something goes wrong there, #REF! or #N/A appears in the cell, and that line simply goes along to the release.
Published 21 September 2026
01 What it is
#REF! in the BOM, briefly explained.
A formula error is a cell in which Excel cannot show a value. The best known are #REF! (the reference no longer exists) and #N/A (the lookup returned nothing).
In a BOM that means: a part number, a quantity or a description is missing. The line is still there, so it counts towards your number of lines and looks complete.
How you notice it
- Cells with #REF!, #N/A, #VALUE! or #NAME?
- A position number without a part number
- A quantity that is zero or empty while the part is drawn
- A BOM that looks different in another folder than it does for you
In a check
This shows up as error in the check of the BOM. You judge what has to be done about it.
02 Example
What it looks like.
Example BOM GRP-2210 (demo)
- Row 14, part number
- #REF!
- Row 14, description
- Support profile 40x40
- Row 21, quantity
- #N/A
- Consequence
- Two lines without usable data
Rows 14 and 21 go into the workshop as empty lines. A check that comes across these lines cannot compare them with the model and reports them separately, instead of silently skipping them.
How it comes about
- A row or column deleted that a formula referred to
- A lookup value that does not occur in the source list, often because of a space or a different spelling
- A linked file that has been moved or renamed
- A BOM from the PDM system that was updated in Excel without taking the source along
03 Approach
Find, fix, prevent.
How to find it
Formula errors are easy to find, but only if someone deliberately looks for them. In a long list they do not stand out, certainly not if the column is narrow.
- 1 Search the Excel for #REF!, #N/A, #VALUE! and #NAME?.
- 2 Do not only look at the visible columns: hidden columns can carry the error too.
- 3 Check whether the source list that is referred to is still in the same place.
- 4 Paste the result as a value if the source no longer exists, and fill in the line by hand.
Fix
- Restore the reference instead of emptying the cell: an empty cell is just as unusable, but stands out less.
- Check the spelling of the part number in both lists, including spaces and leading zeros.
- Deliver the BOM without links to files outside the release folder.
Prevent
- Export the BOM from your CAD or PDM system and do not edit it by hand afterwards.
- Use one source list for part numbers, not a copy per project.
- Let the release check read the BOM as it is, including formula errors.
With BOMproof
Every release, without having to think about it.
BOMproof puts your STEP model, your PDF drawings and your Excel BOM side by side and points out every deviation: with a pin in the 3D model and a balloon on the drawing. You tick off what is correct.
04 Frequently asked questions
#REF! in the BOM.
01 Why do I only see #REF! when opening?
As long as the source file is open or was in the old place, Excel simply calculates the formula. Only for someone else, with other folders, does the reference drop out.
02 What does BOMproof do with such lines?
Lines with #REF! or #N/A are not included in the comparison with the model, but they are reported. That way you know there are lines that could not be checked.
03 Is an empty cell just as bad?
For the workshop it is. The difference is that a formula error shows that something went wrong, while an empty cell looks as if nothing is supposed to be there.
05 Often seen together
Other errors from the same corner.
You never want to hunt for this error by hand again.
Upload your model, your drawings and your BOM and let BOMproof check them against each other. Book a demo with your own design.