Adding millimetres to metres in Excel™: why there is no warning
Put 250 in one cell with the header "mm", and 0.25 in another with the header "m". Add them and Excel™ returns 250.25. It has no idea that one number is in millimetres and the other in metres. (This is an illustrative example.)
Why it happens
A cell holds a number. The unit is a label you attach by habit: a column header, a row label, a number format or a note. Excel™ does not read those labels, so it never complains when they contradict.
Make units hard to lose
These steps apply to Excel™ in Microsoft 365.
- Put the unit in every header: "Length (mm)", "Moment (kN.m)".
- Use a custom number format so the unit shows in the cell. In Format Cells → Number → Custom, enter
0.00" mm". The cell still holds a plain number, but you can see the unit. - Use one unit per column and do not change it halfway down.
- Convert at the edge with
CONVERT, for example=CONVERT(A2,"mm","m"), instead of typing 1000 into formulas. - For constants such as a yield stress, use a defined name that carries the unit, for example
Fy_MPa.
Audit by hand
Read each formula with its units written beside it: "mm + m" is a stop sign. Pay particular attention to formulas that add or subtract, since multiplication and division legitimately mix units.
A checker that reads the labels you already wrote
Unit-aware Calculation Checker reads units you have already written (headers, row labels, units rows, number formats, notes and defined names), follows them through formulas, and reports where two units contradict. A cell with no readable unit stays silent, and the checker never guesses. Checking and explaining the active sheet is free and unlimited. The check runs inside Excel™ and nothing leaves your machine.
See Unit-aware Calculation Checker for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Unit-aware Calculation Checker is not affiliated with or endorsed by Microsoft.