Skip to content

CONVERT() could be better in Excel

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

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.

AutoComplete

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:

Pound2Kilogram()

Ounce2Gram()

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.

About this author