Use the CONVERT Function and Forget How Many Metres are in a Mile

How many seconds are in a day?

How many yards in a kilometer?

What about the number of grams in a pound?

If you know some of these simple conversions, then that’s great.

But if you don’t, it doesn’t matter.  Excel has a CONVERT() function which can instantly change a value from one unit into another.

This function will save you plenty of time when working with inconsistent reports that give you measurements in different formats.

How CONVERT() Works

The CONVERT() function isn’t that complicated.  All you need is:

  • the number you want to convert (number),
  • the unit it’s currently in (from_unit), and
  • the unit you want to convert into (to_unit).

And here’s how the formula arguments are meant to be ordered:

CONVERT(number, from_unit, to_unit)

So for example, if I want to convert 50 yards into meters, I would type:

CONVERT(50, “yd”, “m”) = 45.72

Pretty easy, right?  Read on to see a list of common units that work with the CONVERT() function in Excel, and some special notes to be aware of when using it.

If you like formulas that give you quick answers, make sure you’re using the Status Bar to instantly get answers on common formulas!  Find out more here.

List of common measurement types

Below is a list of the common measurement types you’ll use with the CONVERT() function.

To see the full list of measurement types, see the official Microsoft help page.

CategoryMeasurementfrom_unit / to_unit
Weightgramsg
Weightpoundslbm
Weightstonesstone
Weighttonton
Weightimperial tonuk_ton
Distancemeterm
Distanceinchin
Distancefootft
Distanceyardyd
Distancemilemi
Distancelight-yearly
Timeyearyr
Timedayd, day
Timehourhr
Timeminutemin
Timeseconds, sec
EnergyjouleJ
EnergyIT caloriecal
Energythermodynamic caloriec
PowerwattW, w
PowerhorsepowerHP, h
Temperaturedegrees CelsiusC, cel
Temperaturedegrees FahrenheitF, fah
Temperaturedegrees KelvinK, kel
Volumeteaspoontsp
Volumetablespoontbs
Volumecupcup
Volumegallongal
Volumeliterl, L
Volumeounceoz
AreaUS acreus_acre
AreaUK acreuk_acre
Areahectareha
Areasquare metresm2, m^2
Areasquare milesmi2, mi^2
Informationbitbit
Informationbytebyte
Speedknotkn
Speedmiles per hourmph
Speedmeters per secondm/s, m/sec

Three special things to look out for

Before you rush off and start finding out how many teaspoons are in a gallon, have a quick read of the three points below.

1. Put quotes around the measurements

Keep in mind that need to put the quotes around each unit of measurement.  In the example I listed above, CONVERT(50, yd, m) won’t work.

2. Stick to the same system of measurement

This might sounds obvious, but the from_unit and to_unit in the formula need to be the same type of unit.

Don’t bother trying to convert kilometers into seconds – physics doesn’t work that way!*

3. Use SI prefixes as much as you like

A kilometer is one thousand meters.  A millisecond is one thousandth of a second.  A megabyte is one million bytes (roughly speaking).  These prefixes that are added to the front of the units are used across all units of measurement, and the CONVERT() function can handle these units as well, as long as you put the right prefix in front.

Here are some of the common prefixes that you may use:

PrefixMultiplierAbbreviation
giga1,000,000,000
(one billion)
G
mega1,000,000
(one million)
M
kilo1,000
(one thousand)
k
milli1/1,000
(one thousandth)
m
micro1/1,000,000
(one millionth)
u
nano1/1,000,000,000
(one billionth)
n

Excel can handle prefixes all the way up to 10^24 and 10^-24.  The full list is on the official Microsoft help page.

Conclusion

There you go – one more thing that you need to memorise!  Let Excel do all the heavy lifting for you – get the right results quickly, and start being .

Do you use CONVERT() already or is this a new function that you’re just learning about?

* unless you want to start getting into the nitty-gritty of quantum physics, in which case time and distance get a bit complicated…

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *