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
- How to Use the CONVERT Function in Excel?
- 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
Can the CONVERT function in Excel take negative values?
Yes, the CONVERT function can take negative values. Depending on the measurement quantity, the output of the result varies.
How do I convert values within the same measurement type?
You can convert values within the same measurement type by using measurement prefixes. Adding prefixes before the from_unit and to_unit alters the values and provides results accordingly. Note that, the prefixes are different for units of information (bit and byte) and other measurement quantities.
What is the easiest way to use the CONVERT function?
Using the formula =CONVERT(number,from_unit,to_unit) is the easiest way to convert values. Enter the function in the destination cell and you can easily select the conversion units from the drop-down. Press Enter to get the resultant converted values.
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!
