Today you can learn how to create Unit Converter in Google Sheets. For this purpose, I am using the Convert Function. Other than CONVERT you only need to use the Data Validation menu option. So first learn how to use CONVERT Function in Google Sheets.

With this unit converter, in your Google Sheets, you can convert values from one unit to another. I think this function is best for engineers.

Below are the available conversion unit categories. Under these categories, there are different subcategories. Please refer this Google Doc Editor’s help page for more info.

Weight
Distance
Time
Pressure
Force
Energy
Power
Magnetism
Temperature
Volume
Area
Information
Speed

First, learn the usage of the convert function then I will take you to how to create Unit Converter in Google Sheets using this function.

How to Use Convert Function in Google Sheets

The syntax of Google Sheets Convert Function.

Syntax:

CONVERT(value, start_unit, end_unit)

Here value is the number that you want to convert from one unit to another. Start unit and end units are the units that are coming under the above categories. For example, if you check the above list, the first category is Weight. Under this, there are different units of conversion which I’ve not shared above.

Example

=CONVERT(1, “kg”, “g”)

This Convert formula converts the value 1 from kilogram to gram (kg to g). This way you can use any available units in covert. Needless to say, you can convert unit under one category to another unit under the same category. In my example sheet, which is shared at the end of this spreadsheet tutorial, you can see all the available categories and units. It’s a sample Unit Converter in Google Sheets. Now let me first explain you, how to create Unit Converter in Google Sheets.

You May Also Like: Amount or Numbers to Words Converter Using Google Sheets

Steps to Create Unit Converter in Google Sheets

Step 1:

Go to this Google Sheet and Copy the content in the “Sheet 1” to your sheet.

It should be copied in the range A1: M26. If you don’t have time now to do the experiment, no need to have a wild guess on what is in that sheet. See this screenshot.

Step 2:

In a new tab, I am going to create the Unit Converter. In my above-shared sheet, from “Sheet2” copy the content in the column range A2: A14 and pasted it in your sheet in the same range in a new tab. It’s only the categories listed above. Then enter the column labels in the range C1: F1. Refer the screenshot below.

Step 3:

Now I am going to create a simple drop-down menu for each category. I will explain here how to do it for the category “Weight” in cell A2. You can follow the same steps for other categories in the rows below.

Go to the cell D2 and set the data validation as below. This setting is available under the menu Data.

Here take care of two things. Against the “Criteria” you should select “List from a range” in the drop-down and then in the field adjoining to it, select or enter the range Sheet1!A2: A13. To select the range click the square box on the corner of the field.

This’s the range that contains the different units for the category “Weight”. Make sure that everything is as per the above validation settings and click “Save”. You have created one drop down menu for the category “Weight”.

Now copy cell D2 to E2. It’ll automatically copy the data validation settings to E2.

Step 4:

Follow the same steps in Step 3 for all the categories. For example, set the data validation in D3 for the category “Distance” and copy the settings to E3. But you should change the data validation criteria reference from “Sheet1!A2: A13” to corresponding column references. For example, for the category “Distance” the reference will be “Sheet1!B2: B13”

Step 5:

You are almost learned how to create Unit Converter in Google Sheets. Now the last step is applying the conversion formula. In Cell F2 apply the below formula.

=ArrayFormula(iferror(convert(C2:C14,D2:D14,E2:E14),""))

It’s an Array Formula. So no need to apply this formula in each and every rows. The use of Iferror function in this formula is to return a custom value (here blank) if there are error values in any cells in the result. That means if there is no value in any of the cells in C2: C4 or no units selected for conversion, the formula will return #N/A error in those cells. The Iferror function removes such errors and returns blank.

Finally, if you want you can select the range C2: C14 and set a validation rule as below. With this settings, you can restrict other users from entering values other than the number in the range. To do this you can use the below ISNUMBER formula in Data Validation.

=ISNUMBER(C2)

Conclusion:

Hope you could understand how to create Unit Converter in Google Sheets. If you want you can create a custom unit converter by using functions like SUMIF or Vlookup. For that, you can refer my below blog post (it’s an old post though)

Tutorial: Create Unit Calculator Using Google Doc Spreadsheet

If you want to create a currency converter in Google Sheets, here is my tutorial that you can refer.

Tips: How to Convert Currency in Google Sheets Using GoogleFinance Function

That’s all about unit converter in Google Sheets, enjoy!