How to Convert Units in Excel With the CONVERT Function

To convert units in Excel, use the CONVERT function: =CONVERT(10,"mi","km") turns 10 miles into 16.09344 kilometres. It handles length, weight, temperature, volume, time and more, as long as you type the right unit codes. This guide lists the codes you'll use most, shows how to fix #N/A errors, and covers the backup method: multiplying by a conversion factor in a locked cell.

How the CONVERT function works#

CONVERT takes a number in one unit and returns it in another. It has three arguments (the parts inside the brackets):

=CONVERT(number, from_unit, to_unit)

So =CONVERT(B2,"mi","km") reads as "take B2, which is in miles, and give it to me in kilometres". CONVERT is built into Excel 2007 and later on Windows and Mac, and in Microsoft 365.

Common unit codes for CONVERT#

The unit codes are short and case-sensitive. These are the ones most people need:

Codes like "km" and "ml" are a metric prefix (k for kilo, m for milli, c for centi) joined to a base unit. Note the two ounces: "ozm" is weight and "oz" is fluid volume.

Here is CONVERT on everyday conversions:

When you type =CONVERT( and the first comma, Excel shows a list of units you can pick from, which helps you avoid typos.

Why does CONVERT return #N/A?#

CONVERT returns #N/A when it doesn't recognise a unit or can't convert between the two. Check these in order:

1. Capitals. "KM" and "Kg" fail. Use "km" and "kg". Temperature codes are capitals: "C" and "F".
2. Different kinds of unit. =CONVERT(5,"ft","kg") fails, because length can't become weight.
3. Spelling. "lb" and "oz" look right but aren't the weight codes. Pounds are "lbm", weight ounces "ozm".
4. Missing quotes. =CONVERT(B2,mi,km) gives #NAME?, because Excel reads mi as a name.

If the number itself is text, such as "10 miles", CONVERT returns #VALUE!. Put the number in one cell and the unit in another.

How to round the converted result#

CONVERT returns every decimal place, which is too many for most reports. Wrap it in ROUND to keep the number of decimals you want:

=ROUND(CONVERT(B2,"lbm","kg"),1)

If B2 holds 150, the result is 68, because 68.0388555 rounds to 68.0. The second argument of ROUND is the number of decimal places. The ROUND function guide shows the syntax, and the article on ROUND, ROUNDUP and ROUNDDOWN explains when to round up or down instead.

ROUND changes the stored value. If you only want the display tidier, use Decrease Decimal on the Home tab instead, and the full value stays in the cell.

Put the unit codes in cells and fill down#

To convert a whole column, keep the unit codes in their own cells and lock them with $. Say E1 holds km and F1 holds mi (typed without quotes in the cells). In C2:

=CONVERT(B2,$E$1,$F$1)

Fill it down (Windows: Ctrl+D; Mac: ⌘D). B2 has no $, so it moves to B3, B4 and so on. $E$1 and $F$1 are absolute references: they stay fixed as you fill. To switch a whole report to other units, change E1 and F1, not every formula.

Convert units with a conversion factor instead#

You can convert units in any spreadsheet by multiplying or dividing by a conversion factor. This works when a unit isn't in CONVERT's list, or when the file must work in other tools. Some factors are exact by definition:

Rather than typing the factor into every formula, put it in one cell and lock it. If E2 holds 1.609344, then =ROUND(B5/$E$2,1) turns a 42.195 km marathon into 26.2 miles. The exercise on this page uses exactly that. The guides on the multiply formula and the divide formula cover the basics, and a conversion factor is really a fixed ratio, like those in how to calculate a ratio.

Temperature is the exception: you can't just multiply, because the scales start at different points. Use CONVERT or the full formula above.

Split a time conversion into whole units#

Some conversions need a whole part and a remainder, such as 135 minutes as "2 hours 15 minutes". Divide for the whole part, and use MOD for what is left:

=INT(B2/60)&" h "&MOD(B2,60)&" min"

With 135 in B2, the result is 2 h 15 min. INT keeps the whole hours and MOD returns the remainder after dividing by 60. The same pattern turns inches into feet and inches, with 12 instead of 60. The guide to MOD and INT has more examples.

Practise it#

Unit conversion is simple maths, so job tests use it to check that you can write a clean formula and lock a reference. Practise your first formulas in a live spreadsheet, then practise absolute references so a factor like $E$2 stays put when you fill down. Each lesson checks your formula instantly, and the exercise on this page is a quick warm-up.