Want to build a custom unit converter in Google Sheets? Whether you need to convert weight, distance, temperature, volume, or other measurement units, the built-in CONVERT function makes it easy.

Instead of editing the CONVERT formula for each conversion, you can combine it with dependent drop-down lists to create a dynamic unit converter.

In this tutorial, you’ll learn how to build one from scratch and can also download the free template to get started quickly.

The following Google Sheets Unit Converter template includes dynamic drop-down lists, bidirectional conversion, and unit descriptions, so you can start using it immediately or customize it for your own projects.

Click the button below to preview the template and create your own copy.

Preview and Download the Template

How to Use the Unit Converter Template

Using the automated unit converter is simple and intuitive. Follow these quick steps to perform any conversion instantly:

1. Select a Category: Choose one of the 13 available measurement categories from the drop-down menu in cell E1 (such as Weight, Distance, Temperature, or Volume).
2. Choose Your Units: Select your starting unit code in cell E2 and your target unit code in cell E3.
3. Enter Your Value: Type the number you want to convert into cell F2 (or F3). The template will automatically calculate the result and display the converted value in cell G2.

View Unit Descriptions: If you are unsure what a specific short code means (like “lbm” or “Pa”), you will find a lookup table built into the template directly below the converter to show you the full unit descriptions instantly.

Now let’s see how the CONVERT function works and how the template uses it to perform the conversions.

How to Use the CONVERT Function in Google Sheets

The converter is built around the Google Sheets CONVERT function.

Syntax:

=CONVERT(value, start_unit, end_unit)

• value – The number (or cell reference) to convert.
• start_unit – The current measurement unit of your starting value. This must be wrapped in quotation marks if entered directly into the formula (e.g., “kg”).
• end_unit – The target measurement unit you want to convert to (e.g., “ton”).

Example:

=CONVERT(1000, "kg", "ton")

Important: You can convert only between units in the same measurement category. For example, you can’t convert kilograms to meters. Doing so returns a #N/A error.

Categories and Units for the CONVERT Function

The following tables list the unit abbreviations supported by the Google Sheets CONVERT function.

Weight

Distance

Time

Pressure

Force

Energy

Power

Magnetism

Temperature

Volume

Area

Information

Speed

Steps to Make a Unit Converter in Google Sheets

Follow these step-by-step instructions to build the fully automated unit converter from scratch.

Step 1: Setting Up the Database

First, create a reference table in columns A, B, and C. Label cells A1:C1 as “Category”, “Unit”, and “Description”. You can use the unit tables provided above to populate rows 2 and downward.

Step 2: Building the Dependent Drop-Down Menus

Next, follow these steps to create your dependent drop-down lists:

1. Select cell E1 (this will be your Category selector).
2. Click Insert > Drop-down to open the Data Validation sidebar.
3. In the Criteria menu, select Drop-down (from a range).
4. Enter A2:A in the Range field and click Done.
5. In cell D1, enter the following formula to return the unit codes for the selected category:

=VSTACK("Helper", FILTER(B2:B, A2:A=E1))

6. Select cell E2 (your start_unit selector).
7. Click Insert > Drop-down, choose Drop-down (from a range), enter D2:D as the range, and click Done.
8. Copy cell E2 and paste it into E3 to create the end_unit selector.

Step 3: Adding the Multi-Directional Conversion Formula

The following formula checks whether F2 or F3 contains the input value and performs the conversion in the appropriate direction.

1. Navigate to cell G2.
2. Enter the following formula:

=IFERROR(
 CONVERT(
   SORTN(F2:F3), 
   FILTER(E2:E3, F2:F3), 
   FILTER(E2:E3, F2:F3="")
 )
)

Now, simply enter the number you want to convert into either cell F2 or F3, and cell G2 will instantly output the converted value.

Formula Breakdown: How the Array Logic Works

The formula works by checking which of the two input cells (F2 or F3) contains a value. It uses the unit beside that value as the source unit and the other selected unit as the destination unit, then passes all three arguments to the CONVERT function. This allows the converter to work in either direction without changing the formula.

• SORTN(F2:F3) returns the entered value.
• FILTER(E2:E3, F2:F3) returns the source unit.
• FILTER(E2:E3, F2:F3="") returns the destination unit.

Wrapping the formula in IFERROR ensures that your spreadsheet displays a clean blank cell or custom text instead of throwing a generic error when the input cells are empty.

Step 4: Adding Dynamic Unit Description Lookups (Optional)

To make the converter more user-friendly, we can display the full name of the selected unit abbreviations (for example, changing “lbm” to “Pound (Mass)”).

1. Navigate to cell F7 and enter the following formula to display the full start_unit description.

=IFNA(E2&" ("&XLOOKUP(E2, B2:B, C2:C)&")")

2. Navigate to cell F8 and enter the following formula to display the full end description.

=IFNA(E3&" ("&XLOOKUP(E3, B2:B, C2:C)&")")

Unit Descriptions: XLOOKUP and IFNA

The lookup formulas in F7 and F8 combine the & operator with XLOOKUP to scan your master database in columns B and C. It matches the short code from your drop-down (E2 or E3) and pairs it with its full description.

Wrapping the formula in IFNA prevents #N/A errors when no unit is selected.

Related Google Sheets Tutorials

• Free Google Sheets Templates (Premium & Fully Automated)
• Convert Numbers to Words in Google Sheets (US & Indian Formats)
• Military Alphabet (NATO Code) Converter Template for Google Sheets