AppGarden Unit-aware Calculation Checker logo AppGarden

Unit-aware Calculation Checker

Adding millimetres to metres in Excel™: why there is no warning

By Paul Kemp · Published

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.

  1. Put the unit in every header: "Length (mm)", "Moment (kN.m)".
  2. 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.
  3. Use one unit per column and do not change it halfway down.
  4. Convert at the edge with CONVERT, for example =CONVERT(A2,"mm","m"), instead of typing 1000 into formulas.
  5. 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.