Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals



Buying Office

Office 365


Office News Wire

Join us!

Our Ebooks

Mobile | PDA



Command Finder

Microsoft Office Bookshop



CONVERT() could be better

Excel's method of converting measurements is good but could be so much better.

View this page on the new web site - click here

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     

View this page on the new web site - click here

Once you start looking into the CONVERT function in Excel, you realize that it’s really an incomplete function. Excel has been around for a long time and yet this important feature has lapses that would make it better, easier and more accessible to users.

As usual, we originally decided to write about this Office feature thinking it will be straight-forward, only to find curious anomalies and problems even in such a supposedly mature product like Excel.


Fixing the AutoComplete options should be on the Redmond bug fix list. Some common ‘multiplier’ measures like kilometre, kilogram etc should appear on the Autocomplete list where applicable. Microsoft should also ensure that the to_unit AutoComplete listing shows only the matching measurements in all cases, not just some.

Missing measurements

The list of measurement units seems impressive, especially when you include the possibilities of the multiplier prefixes. Yet there are notable omissions from the list.

Distance measures are missing such as Furlong (still widely used in horse racing) , Point (American), Point (European), Light Year and Parsec.

The Time category is missing Decade and Sidereal Day.

In fact, astronomical measures are notably missing from CONVERT. Where there no wannbe astronomers in the development team when the measures list was being written?

We’re not suggesting that every obscure measurement system should get a run in CONVERT -- Excel can survive without an inbuilt conversion for the jiffy, cubit or famously obscure furlong per fortnight but there are some that are commonly used or at least used enough to deserve addition.

Missing categories

Not only are there missing measurements but whole categories of measurements left out.

There are no speed measurements – not even basic ones like miles per hour or kilometres per hour – let alone simple conversion between them.

Surely CONVERT should include Kilometer per hour, Kilometre per second, Miles per hour, Miles per second and Speed of Light in Vacuum.

Similarly there are no area measurements like Acre, Hectare, Square Foot, Square Yard, Square Metre and Square Kilometre.

Cubic measures are also AWOL: Cubic Meter, Acre-Foot and Ton.

We suspect that the developers’ thinking was to provide the basic units and let Excel users use them as a base for other measurements. That kind of thinking might have been good enough a decade ago, but now Office users are entitled to expect more.

Sure, in all these cases you can make your own formulas to do the job, especially with ones like mph <> kph however you can say that about the entire CONVERT function which is really simple multiplication by a pre-set series of constants.

Full name alternatives

Readability is important in Excel formulas, especially when many people are using or editing the worksheet over time.

It would be nice if CONVERT not only accepted abbreviations but also full name versions for the ‘to’ and ‘from’ units.

For example instead of =CONVERT(25,"C","F") this, more easily understood, alternative should also work =CONVERT(25,"Celsius"," Fahrenheit").

Simpler functions

For some of the most common conversions there should be obvious stand-alone functions that are simple and easy to read.

Mile2Kilometre() and Kilometre2Mile() is probably the most obvious one but there could be other like:



and, of course, Celsius2Fahrenheit() and Fahrenheit2Celsius()

These shorthand functions would be simple to implement since they would just map internally to the CONVERT function codebase.

Article posted: Tuesday, 29 November 2011

View this page on the new web site - click here

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
» New web site
» Two ways for sorting by Number
» Office for iPad, September updates
» Why is Gene Cernan ignored in Word?
» DropBox prices drop but is it enough?
» Sort by hidden column in Word

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).