How to Convert Units in Excel
Excel converts units with the CONVERT function: =CONVERT(number, "from_unit", "to_unit"). To turn 10 miles into kilometres, write =CONVERT(10,"mi","km"), which returns 16.09344.
Two things catch people out: the unit codes are case-sensitive — "g" is grams but "G" is the giga prefix — and CONVERT refuses to mix categories, so length-to-weight returns #N/A rather than a wrong number. That strictness is a feature.
Most people convert units in Excel by typing a multiplier into a formula, which works until someone rounds the factor or forgets which direction it applies. CONVERT is built in, uses exact factors, and documents itself — the formula says "mi" and "km", so anyone reading it later knows what it does.
The basic syntax
=CONVERT(number, "from_unit", "to_unit")All three arguments are required, and the units must be text in quotes:
| Formula | Result |
|---|---|
=CONVERT(10,"mi","km") | 16.09344 |
=CONVERT(1,"in","cm") | 2.54 |
=CONVERT(70,"kg","lbm") | 154.3236 |
=CONVERT(100,"C","F") | 212 |
=CONVERT(1,"gal","l") | 3.785412 |
Point it at a cell rather than a literal for real work:
=CONVERT(A2,"mi","km")Then fill down. Because the factors are built in, there is no lookup table to maintain and nothing to get out of date.
The unit codes you will actually use
The codes are short and not always guessable. These are the common ones:
| Category | Codes |
|---|---|
| Distance | "m" metre · "mi" mile · "in" inch · "ft" foot · "yd" yard · "ang" angstrom · "Nmi" nautical mile |
| Weight/mass | "g" gram · "lbm" pound mass · "ozm" ounce mass · "stone" · "ton" · "u" atomic mass unit |
| Temperature | "C" Celsius · "F" Fahrenheit · "K" Kelvin |
| Volume | "l" litre · "gal" US gallon · "qt" quart · "pt" pint · "tsp" · "tbs" · "oz" fluid ounce · "uk_gal" imperial gallon |
| Area | "m2" square metre · "ft2" square foot · "ha" hectare · "uk_acre" · "us_acre" |
| Time | "sec" · "mn" minute · "hr" · "day" · "yr" |
| Energy/power | "J" joule · "cal" · "eV" · "HP" horsepower · "W" watt |
Three that trip people up:
"lbm", not"lb". Excel distinguishes pound-mass from pound-force ("lbf"). Plain"lb"returns#N/A."ozm"is weight;"oz"is volume. A fluid ounce and an ounce of mass are different quantities, and this is the classic source of silently wrong recipe conversions.- Gallons differ.
"gal"is the US gallon (3.785 l); the imperial gallon is"uk_gal"(4.546 l). A 20% error hides here.
Microsoft's CONVERT function reference lists every supported code.
Case sensitivity, and why "G" is not grams
This is the mistake that produces wrong answers rather than errors, so it is worth its own section.
Unit codes are case-sensitive, and Excel also accepts metric prefixes attached to metric units. "k" is kilo, "m" is milli, "M" is mega, "G" is giga.
So "kg" is kilo + gram, exactly as you would hope. But:
"g"is a gram;"G"is read as the giga prefix and is not a valid unit on its own."mm"is milli + metre."Mm"is mega + metre — a thousand kilometres.- Prefixes only apply to metric units.
"kmi"is not a thousand miles; it is an error.
If a conversion returns a number that is out by a factor of a thousand or a million, check the capitalisation before checking anything else.
Errors and what they mean
| Error | Cause | Fix |
|---|---|---|
#N/A | Unknown unit code, or wrong case | Check the code — "lbm" not "lb", "mn" not "min" |
#N/A with valid units | The two units are in different categories | You cannot convert length to weight; check both belong to the same group |
#VALUE! | First argument is not a number | Text that looks numeric — use VALUE() or fix the import |
The second row is worth appreciating rather than resenting. =CONVERT(10,"kg","m") returns #N/A instead of a plausible-looking number, so a category mistake fails loudly instead of quietly poisoning a spreadsheet.
To keep a sheet tidy when some rows may fail:
=IFERROR(CONVERT(A2,$B$1,$C$1),"check units")A reusable converter sheet
Put the units in cells rather than hard-coding them, and you have a small tool instead of a one-off formula.
- In B1, the from-unit (e.g.
mi). In C1, the to-unit (e.g.km). - In A2 downwards, your values.
- In B2:
=IFERROR(CONVERT(A2,$B$1,$C$1),"check units")and fill down.
The absolute references ($B$1, $C$1) are the important part — without the dollar signs, filling down walks the unit references down the sheet and every row converts something different. Add Data Validation lists to B1 and C1 and nobody can mistype a code.
When CONVERT will not help
CONVERT handles physical quantities with fixed relationships. It does not do:
- Currency. Exchange rates change, so there is no fixed factor. Excel's Currencies data type or a rate in a cell is the answer.
- Cooking volume to weight. A cup of flour and a cup of honey have different masses, because density is ingredient-specific.
- Data storage in the ambiguous sense. Excel offers
"bit"and"byte", but the base-1000 versus base-1024 question is a definitional one — our guide to MB, GB and TB explained covers why a "1 TB" drive shows as 931 GB. - Gauge versus absolute pressure. An offset, not a factor — the same reason temperature needs special handling.
Temperature is the interesting exception CONVERT does handle. Celsius and Fahrenheit have an offset as well as a scale, so no single multiplier works; CONVERT knows this and applies the right transformation, which is why =CONVERT(100,"C","F") correctly gives 212 rather than multiplying by a ratio.
Checking your work
Sanity-check any new conversion against a value you already know. One inch is 2.54 cm exactly, a marathon is 42.195 km, water freezes at 0 °C and 32 °F. If a formula disagrees with a fact you are sure of, the formula is wrong.
For one-off conversions, or to verify a spreadsheet result, our unit converter covers the same categories in a browser, and single-pair tools such as miles to kilometers show the exact factor being applied so you can compare directly.
Frequently asked questions
How do I convert units in Excel?
Use the CONVERT function: =CONVERT(number, "from_unit", "to_unit"). For example =CONVERT(10,"mi","km") returns 16.09344. Reference a cell instead of a literal number and fill down to convert a whole column.
Why does my CONVERT formula return #N/A?
Either a unit code is wrong or the wrong case, or the two units belong to different categories. Excel deliberately refuses to convert length to weight. Watch for "lbm" rather than "lb", and "mn" rather than "min" for minutes.
Are Excel unit codes case-sensitive?
Yes, and it matters. "g" is grams while "G" is read as the giga prefix; "mm" is millimetres while "Mm" is megametres. If a result is out by a factor of a thousand, check capitalisation first.
What is the difference between "oz" and "ozm" in Excel?
"ozm" is an ounce of mass and belongs to the weight category; "oz" is a fluid ounce and belongs to volume. Mixing them up produces conversions that look plausible and are wrong, which is why recipe conversions go astray.
Can Excel convert currency with CONVERT?
No. CONVERT only handles quantities with fixed physical relationships. Exchange rates change constantly, so use Excel's Currencies data type or store a rate in a cell and multiply.
Does CONVERT work in Google Sheets?
Yes. Google Sheets implements a compatible CONVERT function with the same syntax and largely the same unit codes, so formulas generally transfer. Verify any unusual codes if you move a sheet between them.
Conclusion
CONVERT replaces hand-typed multipliers with exact, self-documenting formulas, and it fails loudly when you ask for something incoherent. The two things to remember are that codes are case-sensitive and that "oz" and "ozm" are different quantities.
Set the units up in their own cells with absolute references and you have a reusable converter rather than a formula you rewrite every time.
Comments