When dealing with international data, engineering specifications, or scientific research, you frequently encounter conflicting units of measurement. You might receive a spreadsheet where temperatures are recorded in Fahrenheit, but your final report requires Celsius. Or perhaps you need to calculate shipping costs, but the product weights are listed in pounds instead of kilograms.
Many users manually look up the conversion rates online and create complex multiplication formulas (e.g., =A2 * 0.453592 for pounds to kilograms). This manual approach is error-prone and difficult for others to verify. Instead, you can use Microsoft Excel’s built-in CONVERT function to seamlessly translate data between different measurement systems without memorising a single mathematical ratio.
Understanding the CONVERT Syntax
The CONVERT function acts as a universal translator for data. It requires three specific arguments to work.
=CONVERT(number, from_unit, to_unit)
- number: The original value you want to convert (or the cell reference containing that value).
- from_unit: A specific text code representing the original unit of measurement (e.g., “lbm” for pound mass).
- to_unit: A specific text code representing the target unit of measurement (e.g., “kg” for kilograms).
How to Use the CONVERT Function
Imagine you have a list of distances in Column A measured in miles, and you need to convert them to kilometres in Column B.
- Click on the first empty cell in Column B (e.g., B2).
- Type the following formula:
=CONVERT(A2, "mi", "km") - Press Enter.
Excel will instantly calculate the exact equivalent distance in kilometres. You can then drag the fill handle down the column to convert the entire list automatically.
Common Unit Codes
The only trick to using the CONVERT function is knowing the exact text abbreviations Excel requires. If you type “miles” instead of “mi”, the function will throw a #N/A error. When you start typing the formula in modern versions of Excel, a helpful drop-down menu will appear suggesting the correct codes.
Here are some of the most useful codes you should know:
Weight and Mass
- Gram:
"g" - Kilogram:
"kg" - Pound Mass:
"lbm" - Ounce Mass:
"ozm"
Distance and Length
- Metre:
"m" - Kilometre:
"km" - Inch:
"in" - Foot:
"ft" - Mile:
"mi"
Temperature
- Celsius:
"C" - Fahrenheit:
"F" - Kelvin:
"K"
Time
- Year:
"yr" - Day:
"day" - Hour:
"hr" - Minute:
"mn" - Second:
"sec"
Important Limitations
The CONVERT function is incredibly powerful, but it adheres to strict categorical rules. You can only convert units within the same category. For example, you can convert hours to seconds (Time), but if you attempt to convert kilograms (Mass) to miles (Distance), Excel will output an error because the conversion is mathematically impossible.