How to convert metric to imperial and other measure conversions in Excel.
Converting measurements from one system to another can be done by Excel using the CONVERT function … here’s how it works and then we’ll tell you what CONVERT can’t do or should do better.
As usual, we decided to write about an Office feature thinking it will be straight-forward only to find curious anomalies and problems even in such a supposedly mature product like Excel.
CONVERT() lets you change a measurement in one scale to another scale using in-built conversion factors. Most commonly this is used for imperial/US <> metric conversions but can also be used to convert between scales of the same type (feet to miles or seconds to hours).
The basic format is this:
CONVERT(Number, From_unit, To_unit)
Number is the incoming value – probably a reference to another cell.
From_unit is the measurement scale that applies to the number. It’s a text value that can be typed into the formula or referenced from another cell.
To_unit is the measurement you want converted to. It’s a text value that can be typed into the formula or referenced from another cell.
Sounds imposing but here are some simple and common examples.
=CONVERT(100,”mi“,”km“) converts 100 miles into kilometers
=CONVERT(25,”C“,”F“) converts 25° Celsius into Fahrenheit .
=CONVERT(100,”oz“,”l“) converts 100 fluid ounces (oz) into liters.
Of course any of these are reversible for example:
=CONVERT(100, “km“, “mi“) converts 100 kilometres into miles
The Units
CONVERT has a long list of measurement units to choose from.
In addition to the basic units, there are also multiplier options which apply mostly to the metric measurements. For example ‘m’ is the abbreviation for metre and ‘k’ is the multiplier prefix for ‘kilo’ or x 1000 – put the two together to get ‘km’ the CONVERT abbreviation for kilometre (also the standard abbreviation, the metric system is wonderfully consistent).
Even with the multipliers, the convert function list of measurements is by no means complete as we’ll see later.
Common mistakes
Such a powerful but flexible function means there’s plenty of scope for formula mistakes.
A common one is forgetting the quote marks around the “to” and “from” units.
For example =CONVERT(51234,sec,hr) looks like it will convert 51,234 seconds into hours but it won’t – the double-quotes are missing. =CONVERT(51234,”sec”,”hr”) is correct.
Another mistake is to try converting mismatching measurements.
=CONVERT(100,”tsp”,”c”) will get a #N/A result because it’s trying to convert a 100 teaspoons of liquid measure into a thermodynamic calorie (quite a different animal). Mostly likely it should be =CONVERT(100,”tsp”,”cup”).
Some of the abbreviations aren’t what you expect. ‘oz’ isn’t ounces it is fluid ounces. For ounces in weight/mass use ‘ozm’.
All measurement ‘to’ and ‘from’ units are case-sensitive.
Autocomplete
To avoid many of these errors, Autocomplete can help you type in the right matching conversion factor – it can limit the to_unit suggestions to the ones applicable for the from_unit.
However the suggestions don’t always make a lot of sense in the real world. They are limited to the base measures, not multipliers like km / kilometres.
If you type in “mi” as the ‘from’ unit, Autocomplete will show some applicable distance measures for the ‘to’ unit:
However it doesn’t suggest a common measurement unit like ‘km’ because that’s not an inbuilt measurement, only a construct made using the multiplier ‘k’ prefix. That’s a pity since ‘km’ for kilometre is the most common conversion for miles.
Entering “C” for Celsius now gives you a small list of temperature scales for the ‘to’ unit. That’s a welcome fix from when we last tested Excel.
See Also
- El Patron de los Numeros Primos
- Singular / Plural text in Excel
- CONVERT() could be better
- Excel CONVERT measurement list
