Converting Measurement Units: Using the Convert Function in Microsoft Excel
Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
The Convert Function
The convert function in Excel converts a number from one measurement type to that of another.
Created by Joshua Crowder
Why Use Excel to Convert Measurement Units
Microsoft Excel can play hard ball when it comes to creating conversion tables. The Convert function can be utilized when one has a lot of data to convert to an alternate unit of measurement. The convert function coverts weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, speed, prefixes and binary prefixes. With all of these types of conversions it’s safe to say that this is indeed a powerful function of Excel.
Convert Function Example: Fahrenheit to Celsius Coversion
A common conversion is from Fahrenheit to Celsius when measuring temperature. A blank worksheet and completed worksheet for the following example data can be downloaded here. Data is displayed below in Fahrenheit and needs to be converted to Celsius. This can be easily accomplished by using the convert function.
Created by Joshua Crowder
To start using this function type “=convert(“ in the cell where you would like the converted data to display. There are only three pieces of data that the convert function needs to create a conversion. The first of the data is the original unit measurement (in this case Fahrenheit). The original measure can be a value or a cell that contains that value. The next two values need to represent values that tell Excel from what measurement and to what measurement.
Created by Joshua Crowder
For this function to work properly the syntax must be used properly. In the illustration below the cell B4 is being converted from “F” to “C” or from Fahrenheit to Celsius. There should be a comma with no spaces between each set of data in this function. To convert Atlanta's January high to Celsius you would have to type =convert(B4,"F","C")
Created by Joshua Crowder
Once the formula is entered formula can be copied down with the fill handle to convert the rest of the Fahrenheit temperatures in column B.
Created by Joshua Crowder
Correct Syntax Use
The remainder of this text consists of tables for different types of measurements. Take notice to the next table down that displays available syntax for temperature measurements. With a simple change in the function Fahrenheit in the example can also be changed to Degrees Rankine, Degrees Réaumur or Kelvin. Any unit contained in an individual table can be converted to another unit within that table only.
Temperature Unit Syntax
| Temperature | From_unit or to_unit |
|---|---|
Degree Celsius | "C" (or "cel") |
Degree Fahrenheit | "F" (or "fah") |
Degrees Rankine | "Rank" |
Degrees Réaumur | "Reau" |
Kelvin | "K" (or "kel") |
Mass & Weight Unit Syntax
| Weight & Mass | From_unit or to_unit |
|---|---|
Grain | "grain" |
Gram | "g" |
Imperial hundredweight | "uk_cwt" or "lcwt" ("hweight") |
Imperial ton | "uk_ton" or "LTON" ("brton") |
Ounce mass (avoirdupois) | "ozm" |
Pound mass (avoirdupois) | "lbm" |
Slug | "sg" |
Stone | "stone" |
Ton | "ton" |
U (atomic mass unit) | "u" |
U.S. (short) hundredweight | "cwt" or "shweight" |
Distance Unit Syntax
| Distance | From_unit or to_unit |
|---|---|
Angstrom | "ang" |
Ell | "ell" |
Foot | "ft" |
Inch | "in" |
Light-year | "ly" |
Meter | "m" |
Nautical mile | "Nmi" |
Parsec | "parsec" or "pc" |
Pica (1/6 inch) | "pica" |
Pica (1/72 inch) | "Picapt" or "Pica" |
Statute mile | "mi" |
U.S survey mile (statute mile) | "survey_mi" |
Yard | "yd" |
Time Unit Syntax
| Time | From_unit or to_unit |
|---|---|
Day | "day" or "d" |
Hour | "hr" |
Minute | "mn" or "min" |
Second | "sec" or "s" |
Pressure Unit Syntax
| Pressure | From_unit or to_unit |
|---|---|
Atmosphere | "atm" (or "at") |
mm of Mercury | "mmHg" |
Pascal | "Pa" (or "p") |
PSI | "psi" |
Torr | "Torr" |
Force Unit Syntax
| Force | From_unit or to_unit |
|---|---|
Dyne | "dyn" (or "dy") |
Newton | "N" |
Pond | "pond" |
Energy Unit Syntax
| Energy | From_unit or to_unit |
|---|---|
BTU | "BTU" (or "btu") |
Electron volt | "eV" (or "ev") |
Erg | "e" |
Foot-pound | "flb" |
Horsepower-hour | "HPh" (or "hh") |
IT calorie | "cal" |
Joule | "J" |
Thermodynamic calorie | "c" |
Watt-hour | "Wh" (or "wh") |
Power Unit Syntax
| Power | From_unit or to_unit |
|---|---|
Horsepower | "HP" (or "h") |
Pferdestärke | "PS" |
Watt | "W" (or "w") |
Magnetism Unit Syntax
| Magnetism | From_unit or to_unit |
|---|---|
Gauss | "ga" |
Tesla | "T" |
Volume Unit Syntax
| Volume | From_unit or to_unit |
|---|---|
Cubic angstrom | "ang3" or "ang^3" |
Cubic feet | "ft3" or "ft^3" |
Cubic inch | "in3" or "in^3" |
Cubic light-year | "ly3" or "ly^3" |
Cubic meter | "m3" or "m^3" |
Cubic Mile | "mi3" or "mi^3" |
Cubic nautical mile | "Nmi3" or "Nmi^3" |
Cubic Pica | "Picapt3", "Picapt^3", "Pica3" or "Pica^3" |
Cubic yard | "yd3" or "yd^3" |
Cup | "cup" |
Fluid ounce | "oz" |
Gallon | "gal" |
Gross Registered Ton | "GRT" ("regton") |
Imperial gallon (U.K.) | "uk_gal" |
Imperial quart (U.K.) | "uk_qt" |
Liter | "l" or "L" ("lt") |
Measurement ton (freight ton) | "MTON" |
Modern teaspoon | "tspm" |
Quart | "qt" |
Tablespoon | "tbs" |
Teaspoon | "tsp" |
U.K. pint | "uk_pt" |
U.S. bushel | "bushel" |
U.S. oil barrel | "barrel" |
U.S. pint | "pt" (or "us_pt") |
Area Unit Syntax
| Area | From_unit or to_unit |
|---|---|
International acre | "uk_acre" |
U.S. survey/statute acre | "us_acre" |
Square angstrom | "ang2" or “ang^2" |
Are | "ar" |
Square feet | "ft2" or "ft^2" |
Hectare | "ha" |
Square inches | "in2" or "in^2" |
Square light-year | "ly2" or "ly^2" |
Square meters | "m2" or "m^2" |
Morgen | "Morgen" |
Square miles | "mi2" or "mi^2" |
Square nautical miles | "Nmi2" or "Nmi^2" |
Square Pica | "Picapt2", "Pica2", "Pica^2" or "Picapt^2" |
Information Unit Syntax
| Information | From_unit or to_unit |
|---|---|
Bit | "bit" |
Byte | "byte" |
Speed Unit Syntax
| Speed | From_unit or to_unit |
|---|---|
Admiralty knot | "admkn" |
Knot | "kn" |
Meters per hour | "m/h" or "m/hr" |
Meters per second | "m/s" or "m/sec" |
Miles per hour | "mph" |
Prefix Unit Syntax
| Prefix | Multiplier | From_unit or to_unit |
|---|---|---|
yotta | 1E+24 | "Y" |
zetta | 1E+21 | "Z" |
exa | 1E+18 | "E" |
peta | 1E+15 | "P" |
tera | 1E+12 | "T" |
giga | 1E+09 | "G" |
mega | 1E+06 | "M" |
kilo | 1E+03 | "k" |
hecto | 1E+02 | "h" |
dekao | 1E+01 | "da" or "e" |
deci | 1E-01 | "d" |
centi | 1E-02 | "c" |
milli | 1E-03 | "m" |
micro | 1E-06 | "u" |
nano | 1E-09 | "n" |
pico | 1E-12 | "p" |
femto | 1E-15 | "f" |
atto | 1E-18 | "a" |
zepto | 1E-21 | "z" |
Binary Prefix Unit Syntax
| Binary Prefix | Prefix Value | From_unit or to_unit | Derived from |
|---|---|---|---|
yobi | 2^80 = 1 208 925 819 614 629 174 706 176 | "Yi" | yotta |
zebi | 2^70 = 1 180 591 620 717 411 303 424 | "Zi" | zetta |
exbi | 2^60 = 1 152 921 504 606 846 976 | "Pi" | peta |
pebi | 2^50 = 1 125 899 906 842 624 | "Pi" | peta |
tebi | 2^40 = 1 099 511 627 776 | "Ti" | tera |
gibi | 2^30 = 1 073 741 824 | "Gi" | giga |
mebi | 2^20 = 1 048 576 | "Mi" | mega |
kibi | 2^10 = 1024 | "ki" | kilo |
To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
References:
CONVERT function. (n.d.). Retrieved July 01, 2018, from https://support.office.com/en-us/article/convert-function-d785bef1-808e-4aac-bdcd-666c810f9af2
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2018 Joshua Crowder
