Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Excel's CONVERT function for metric and more

How to convert metric to imperial and other measure conversions in Excel.

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     


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. We’ve listed them in a separate table.

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:

Excel - Convert autocomplete for length image from Excel

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.

Excel - Convert autocomplete for temperature image from Excel

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

Article posted: Monday, 28 November 2011

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Using Conversations in Outlook
» OneDrive for Business alters files
» About Outlook Conversations
» Keep using your device on the plane
» Excel Online – changing date format
» Office Online update


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).