What it does – Convert one unit of measurement to another.
value: It represents the numerical value that is supposed to be converted to a different unit.
start_unit: The initial unit of the numerical value enclosed within inverted commas.
end_unit: The unit of measurement you want to convert the value to enclosed within inverted commas.
=CONVERT(1000000, "sec", "day")//Converts 1 million seconds to days.
=CONVERT(A2, B2, C2) [A2=25, B2=C, C2=F]
//Converts 25 degrees in Celcius to Fahrenheit.
Note: The unit of conversion must be one recognized by Google Sheets. Otherwise, an #N/A error will be returned.
Get the sample spreadsheet here.
Suppose you want to find the average time your colleagues take to reach the office from their homes. What do you do? You talk to several people, sum their answers and divide it by the total number of people you surveyed. But what if you get different answers? What if person A takes 15 mins, while person B takes 2 hours? Now, what do you do? You first convert the values into homogenous units and then find the average.
Instead of manually doing so, one can use the CONVERT function in Google Sheets and promptly revamp their data into homogenous units.
The convert function in Google Sheets facilitates the conversion of units in weight, distance, time, pressure, force, energy, power, magnetism, temperature, liquid, volume, area, information, and speed.
- Rules of the CONVERT function
- Conversion between units of weight
- Conversion between units of time
- Conversion between units of temperature
- Conversion between units of information
- Conversion between different units manually
Rules for using the Convert Function in Google Sheets
- The units and prefixes used in the Convert Function must belong to the same category and are case-sensitive.
- The convert function will return the #NA error when the units and prefixes are not recognized and incompatible.
- The convert function will return the #VALUE error when the provided numerical value is not valid.
Case 1: The error message appears because although the user wants to convert the value from kilograms to grams, he/she has inputted a text value which Google Sheets reads as an invalid input.
Case 2: Since it’s impossible to convert two units that are not related in any way, the “#NA” error is displayed when we use the Convert function in Google Sheets to convert minutes into grams.
Case 3: The units to convert to and from must be in small-case. Since the formula is case sensitive and the units between which conversion is to be taking place are in upper case, the software displays an error message.
To know more about errors you can click here.
Let’s take a few examples to understand the different ways we can use the convert function in Google Sheets
Example #1: Conversion between units of weight
Different countries use different units as a measure of weight. For example, while in India people state their weight in kilograms, in Europe it is common practice for the public to state their weight in pounds.
A summarized table comprising the use of the convert function in Google Sheets to convert different units of weight is shown below:
While the above image shows the conversion between 6 different units, we can also use the Convert function in Google Sheets to convert between other units such as Atomic Mass Unit, Stone, Slug, and so on.
Example #2: Conversion between units of time
It might just so happen that when you ask someone how long they took to complete a task, some might answer in minutes while some might answer in hours. To facilitate comparison, we can use the convert function in Google Sheets to standardize their units and draw conclusions.
A summarized table comprising the use of the convert function in Google Sheets to convert different units of time is shown below:
Example #3: Conversion between units of temperature
Celsius is conventionally used as a unit to record the temperature of the environment around you; Fahrenheit is generally used to note the temperature of your body and determine if you are running a fever; Kelvin is used while performing scientific calculations. Thus, different units of temperature have different uses, yet they indicate the same thing – the temperature
The table below summarizes the different ways in which we can use the convert formula in Google Sheets to convert the different units of temperature from one to another:
You can also use the convert function in Google Sheets to convert from other units such as Rankine (Rank) and Reaumur (Reau).
Example #4: Conversion between units of information
Did you know that the terms MBs or GBs that we use while talking about our data plan are in reality units of storage that are required to store data in your computers? In Google Sheets, you can use the convert function to convert the size of your information from bits into bytes as shown below:
The examples above are not exhaustive, i.e., Google Sheets allows you to convert your values among different units as well. You can find an exhaustive list of units along with their notations here.
Example #5: Conversion between different units manually
While the above examples all used cell referencing to convert values, you can also manually input the unit you want to convert your value from and the final unit of the value by enclosing them within double inverted commas as shown below.
For example, if you wanted to manually convert 8 bits to bytes, you would have written the following formula:
The convert function can be very useful when you collect data from various sources that follow different conventions to record data. In those times, you can clean your data and ensure uniformity by using the convert function in Google Sheets.
For more such articles, check out our blog.
How to convert formulas to values in Google Sheets
How to use subtotal function in Google Sheets