With the help of the CONVERT function, you can easily create a unit converter in Google Sheets. This function allows you to convert a numerical value from one unit of measure to another within the same category.
For example, you can use the following formula to convert 1 ton to kilograms, assuming the value 1 is in cell C2:
=CONVERT(C2, "ton", "kg") // returns 907.18474
Here, “ton” and “kg” are units in the “Weight” category.
Before we dive into the categories and units of measurement, let’s review the syntax of the function:
Syntax:
CONVERT(value, start_unit, end_unit)
value – The numeric value to convert.
start_unit – The current unit of the value.
end_unit – The unit to which you want to convert the value.
Refer to the tables below for the available categories and their units. After that, we’ll create a simple unit converter using the CONVERT function, three drop-down lists, and a FILTER formula for lookup.
Categories and Units for the CONVERT Function
Here are the available conversion units for the CONVERT function. Please remember to use both start and end units from the same category. You cannot select one unit from one category and another unit from a different category for conversion.
Category: Weight
unit
description
u
Atomic Mass Unit
grain
Grain
g
Gram
ozm
Ounce Mass
lbm
Pound (Mass)
stone
Stone
sg
Slug
cwt
Hundredweight (US)
uk_cwt
UK Hundredweight
ton
Ton (Metric)
uk_ton
UK Ton (Imperial)
kg
Kilogram
Category: Distance
unit
description
ang
Angstrom
Picapt
Picapoint
pica
Pica (1/6 inch)
in
Inch
ft
Foot
yd
Yard
m
Meter
ell
Ell
mi
Mile
survey_mi
U.S. Survey Mile
Nmi
Nautical Mile
ly
Light Year
parsec
Parsec
Category: Time
unit
description
sec
Second
min
Minute
hr
Hour
day
Day
yr
Year
Category: Pressure
unit
description
Pa
Pascal
mmHg
Millimeter of Mercury
Torr
Torr
psi
Pounds per Square Inch
atm
Atmosphere
Category: Force
unit
description
dyn
Dyne
pond
Pond
N
Newton
lbf
Pound-force
Category: Energy
unit
description
eV
Electron Volt
e
Erg
J
Joule
flb
Foot-Pound (or Foot-Pound Force)
c
Thermodynamic Calorie
cal
IT Calorie
BTU
British Thermal Unit
Wh
Watt-Hour
HPh
Horsepower-Hour
Category: Power
unit
description
W
Watt
PS
Pferdestärke (Metric Horsepower)
HP
Horsepower
Category: Magnetism
unit
description
ga
Gauss
T
Tesla
Category: Temperature
unit
description
C
Celsius
F
Fahrenheit
K
Kelvin
Rank
Rankine
Reau
Réaumur
Category: Volume
unit
description
ang^3
Cubic Angstrom
Picapt^3
Cubic Picapoint
tsp
Teaspoon
tspm
Teaspoon Metric
tbs
Tablespoon
in^3
Cubic Inch
oz
Ounce (Fluid)
cup
Cup
pt
Pint
uk_pt
UK Pint
qt
Quart
l
Liter
uk_qt
UK Quart
gal
Gallon
uk_gal
UK Gallon
ft^3
Cubic Foot
bushel
Bushel
barrel
Barrel
yd^3
Cubic Yard
m^3
Cubic Meter
MTON
Measurement Ton
GRT
Gross Register Ton
mi^3
Cubic Mile
Nmi^3
Cubic Nautical Mile
ly^3
Cubic Light-Year
Category: Area
unit
description
ang^2
Square Angstrom
Picapt^2
Square Picapoint
in^2
Square Inch
ft^2
Square Foot
yd^2
Square Yard
m^2
Square Meter
ar
Are
Morgen
Morgen
uk_acre
UK Acre
us_acre
US Acre
ha
Hectare
mi^2
Square Mile
Nmi^2
Square Nautical Mile
ly^2
Square Light Year
Category: Information
unit
description
bit
Binary Digit
byte
8 Bits
Category: Speed
unit
description
m/hr
Meters per Hour
mph
Miles per Hour
kn
Knots
admkn
Admiralty Knots
m/s
Meters per Second
Steps to Create a Unit Converter with the CONVERT Function
Enter categories in column A, units in column B, and unit descriptions in column C, where A1:C1 contains the field labels “Category,” “Unit,” and “Description.”
You can refer to the above tables to fill these columns, and it should look as follows:
Then follow these steps:
Navigate to cell E1.
Click Insert > Drop-down.
In the sidebar panel, select “Drop-down (from a range)” under “Criteria.”
Enter A2:A in the field below.
Click Done. This will create a drop-down menu in cell E1 to select the categories.
Enter the following formula in cell D1 to match the category selected in the drop-down and filter the units: =VSTACK("Helper", FILTER(B2:B, A2:A=E1))
Navigate to cell E2.
Click Insert > Drop-down.
In the sidebar panel, select “Drop-down (from a range)” under “Criteria.”
Enter D2:D in the field below.
Click Done.
Copy the drop-down in E2 to E3.
Enter the following formula in cell G2: =IFERROR(CONVERT(SORTN(F2:F3), FILTER(E2:E3, F2:F3), FILTER(E2:E3, F2:F3="")))
Enter the value to convert from one unit to another in either F2 or F3.
The above dynamic CONVERT formula will return the converted unit.
Formula Breakdown
In the above CONVERT formula, the arguments are as follows:
value: SORTN(F2:F3) // returns the value to convert from the range F2:F3.
start_unit: FILTER(E2:E3, F2:F3) // returns the start unit by filtering E2:E3 for non-empty cells in F2:F3.
end_unit: FILTER(E2:E3, F2:F3="") // returns the end unit by filtering E2:E3 for empty cells in F2:F3.
Your Trusted Google Sheets and Excel Guide
Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
