Excel's CONVERT function for metric and more

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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 but not always.

If you type in “mi” as the ‘from’ unit, Autocomplete will show the applicable distance measures for the ‘to’ unit:

1632 Excel   Convert autocomplete for length - Excel's CONVERT function for metric and more

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 commonly used.

Autocomplete is strangely inconsistent, at least in our Excel 2010 tests. Entering “C” for Celsius should give you a small list of temperature scales for the ‘to’ unit but instead shows the entire list of measurements – most of which can’t possibly be appropriate and will reward you with a cell error.

1632 Excel   Convert autocomplete for temperature - Excel's CONVERT function for metric and more

You have to scroll down to find the temperature measurement units.

subs profile e1563205311409 - Excel's CONVERT function for metric and more
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address