How to Use CONVERT Function in Excel? A Step-by-Step Guide

(Note: This guide on how to use CONVERT function in Excel is suitable for all Excel versions including Office 365)

In an Excel sheet that consists of different measurements, there might be values that are not of the same unit. You would then need to convert them to a particular unit to perform certain operations and interpret the value. In such cases, the CONVERT function comes to your aid.

In this article, you will learn how to use the CONVERT function in Excel along with the unit conversions and examples.

You’ll Learn:

• What Is the CONVERT Function in Excel and Where Is It Used?
• How Does the CONVERT Function Work in Excel?
• Prerequisites to Use the CONVERT Function
  • Syntax
  • Measurements and Their Notations
• How to Use the CONVERT Function in Excel?
  • By Entering the Formula
  • By Using the Formulas Main Menu
• Points to Remember

To download sample Excel workbook with example and measurement notations for reference, click below.

Related Reads:

How to Use the Excel TREND Function? A Step-by-Step Guide

How to Use SUMPRODUCT Function in Excel? 5 Easy Examples

How to Use the ROUND Function in Excel?

What Is the CONVERT Function in Excel and Where Is It Used?

People from different parts of the world use different units of measurement to denote the measured value. However, denoting values using different units has certain limitations as people who use one unit of measurement cannot interpret another unit of measurement easily. Also for some scientific purposes and operations, there is a need to convert the values from one unit to another. 

For example, the temperature value can be denoted using three types of units namely – Celsius, Fahrenheit, and Kelvin. Each unit of measurement has a different meaning and the temperatures 100℃, 100℉, and 100K are not similar. Likewise, Americans use “miles” as their common unit to measure distance, whereas other regions of the world use “meters”.

The CONVERT function in Excel is used to convert a value from one unit of measurement to another. Additionally, you can also convert the values from one metric form to another within the same unit of measurement.

How Does the CONVERT Function Work in Excel?

The units of measurement differ from each other by a constant difference. For example, when you want to convert miles to meters, you will multiply the existing miles values by 1609.44 to get the value in meters. And, to convert meters to miles, you divide the value of the miles by 1609.44.

They look good on paper, but when the data is large, calculating and converting the values can be very arduous and time-consuming. 

The Excel built-in function automatically houses the values and differences for a variety of units. You just have to pass the specific parameters and viola, Excel returns the converted values in an instant.

Prerequisites to Use the CONVERT Function

Using the CONVERT function is very straightforward, just like using any other formula. First, let us learn about the syntax of the CONVERT function.

Syntax

=CONVERT(number,from_unit,to_unit)

Where, 

number is the measurement value that needs to be converted from one unit to another. The number parameter can take constants or variables such as cell numbers that point to a value.

from_unit is the current measurement unit in which the value exists in.

to_unit is the measurement unit to which you want to convert the current value.

The from_unit and to_unit are used to convert one form of the unit to another within the same measurement type, but they only take certain keywords.

Note: If you want to convert a value that is in “inches” to “feet”, you cannot enter inches and feet in the from_unit and to_unit parameters respectively. Here, you will have to use the shorthand notations that represent these units. Therefore, you should use “in” for inches and “ft” for feet. 

Measurements and Their Notations

Given below are the measurement types and shorthand notations that represent the units that are accepted and can be converted in Excel.

Units and Notations of Mass, Distance, Time, and Pressure

Units of Temperature, Speed, Information, and Energy

Units of Volume and Area

Units of Power, Magnetism, and Force 

Prefix Units

Also Read:

How to Use the Excel IFS Function? – 2 Easy Examples

How to Use the TEXTJOIN Excel Function? 3 Easy Examples

How to Use the Excel NOW Function? 3 Examples

How to Use the CONVERT Function in Excel?

You can use the CONVERT function in Excel to convert values between units in 2 easy ways.

By Entering the Formula

This is a very common and easy method to arrive at the converted values.

Consider an example, where you have a list of temperatures in Celsius (℃) and you want to convert them to Fahrenheit (℉).

• First, select a destination cell. 

• Enter the formula =CONVERT(

• Then, enter the value you want to convert in the place of the number parameter. 

• You can also add a variable in the place of the number parameter instead of directly passing the constant. This will be helpful when you already have values occupying the cells and you need to convert the values onto a new cell.

• Once you enter a comma after the number parameter, you can see a dropdown with a list of units. Double-click on the current unit or navigate using arrows and press the Tab key to select the unit. In this case, the temperature value is 100℃. 

• Now for the to_unit parameter, from the drop-down select the unit to which you want to convert the current value. 

• Press Enter.

• This gives you the value converted to the required units in the selected destination cell.

• You can use the drag handle to apply the formula to the other cells 

Note: When you pass the to_unit parameter, you can see that Excel only shows the units relating to the particular measurement system. This means that you can only convert the value among the same measurement types. For example, you cannot convert a value from a unit of temperature to a unit of distance.

By Using the Formulas Main Menu

Another way to use the CONVERT function is by selecting it from the Formulas main menu.

Consider an example, where you have to convert the values given in “meters per hour” to “miles per hour”.

• First, select the destination cell.

• Go to Formulas in the main menu. Click on the dropdown from More Functions. In the Engineering section, select CONVERT.

• This opens up a dialog box to pass the parameters.

• In the number textbox, enter the value or the cell name that houses the value.

• In the from_unit textbox, enter the unit in which the value is existing.

• In the to_unit textbox, enter the unit to which you want to convert the value to.

• Click OK. This gives you the converted values.

Note: One disadvantage of this method is that you cannot choose the units from the dropdown as they appear when you enter the formula. You will have to refer to the table and enter the unit notations manually.

Points to Remember

• You cannot convert the values from one measurement type to another. 
• You can add the prefixes when you want to convert one unit within the same measurement type.
• The CONVERT function throws an #N/A error if the units are of different measurement types or the units don’t exist.
• If the input data or the input data types are incorrect, the CONVERT function throws #VALUE! error.
• The from_unit and to_unit values are case-sensitive. 

Suggested Reads:

The Excel SMALL Function – 3 Best Examples

The Excel CHOOSE Function – 4 Best Uses

The FORMULATEXT Excel Function – 2 Best Examples

Frequently Asked Questions

Closing Thoughts

From academic to scientific purposes, the CONVERT function offers a variety of uses and functionalities.

In this article, we saw what the CONVERT function is in Excel along with the syntax and shorthand notations to use in the formula. We also learned how to use the CONVERT function in two easy ways.

Please visit our free resources center for more high-quality Excel guides. 

Ready to take the next step and hone your skills in Excel?

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!