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.
|Category||Measurement||from_unit / to_unit|
|Temperature||degrees Celsius||C, cel|
|Temperature||degrees Fahrenheit||F, fah|
|Temperature||degrees Kelvin||K, kel|
|Area||square metres||m2, m^2|
|Area||square miles||mi2, mi^2|
|Speed||miles per hour||mph|
|Speed||meters per second||m/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:
Excel can handle prefixes all the way up to 10^24 and 10^-24. The full list is on the official Microsoft help page.
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…