• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Excel Course
  • VBA Course
  • Free Training
  • Login

Tips for Using the CONVERT Function in Excel

One of the tips in my eBook that gets a lot of response is to use the CONVERT function to convert units in Excel. It’s one of those hidden gems in Excel that I wish I had found much sooner.

Given that we spend so much time working with data in mixed unit systems, this could be the most important Excel function for engineers.

It’s a pretty straightforward function to use, but here are a few tips that will make it even easier to implement in your spreadsheets.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Excel CONVERT Function Syntax

The syntax is really simple. Just provide the value, the current units, and the desired units. The syntax looks like this:

CONVERT(number, from_unit, to_unit)

Where:

number is the value to be converted

from_units is the current unit system

to_units is the desired unit system

Working with “from_units” and “to_units”

If you type the “from” and “to” units directly into the function, be sure to enclose the units in quotation marks so that Excel recognizes them as a text string. For instance, below I’ve converted 15.6 liters to gallons by typing the units directly into the function using the text strings recognized by Excel: “l” for liters and “gal” for gallons.

excel convert units liters to gallons

However, the CONVERT function is even easier to use if you’ve included your units in a cell next to the value, because you can reference those cells in the function as the “from” and “to” units. See below:

Obviously, for this function to work you need to know the text strings that Excel recognizes as valid units. You can find the official list from Microsoft here.

One thing to keep in mind if you are using Excel 2010 or earlier, though, is that you will not have access to all of the units that are available in Excel 2013 and 2016. Microsoft added to the recognized units in Excel 2013. This site does a good job of showing which units work with 2007 and 2010 vs. those that are only available in later versions.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Dealing with Compound Units

The CONVERT function can deal with many different unit types, but there are some common engineering units that are not built into Excel. Most of them are “compound units” such as those for flow rate (e.g. gallons/minute or liters/minute), torque (e.g. lbf-ft or N-m), density (e.g. lbm/ft3 or kg/m3), etc.

To deal with these units, we have to break them apart into their individual components and multiply or divide the individual conversion factors.

For example, if we want to convert a torque value of 500 lbf-ft to N-m, the syntax would appear as follows:

excel convert units lbf-ft to N-m

Unfortunately, with this method, we can’t choose the cells containing the units as the “from_unit” and “to_unit” arguments to the function. Well, we COULD if we wanted to do some really tricky stuff with the FIND and LEN functions to extract the individual text strings. But that just seems like too much work. J

Combining CONVERT with Data Validation

You can also use data validation to create some powerful automation in a spreadsheet that allows a user to choose the most convenient unit system for them to enter their data. As long as the user selects the correct units for their value, the CONVERT function can change to the appropriate units for further calculations.

To do this, first create a list of recognized units somewhere in the spreadsheet. Then with the unit input cell selected, choose Data>Data Validation>List and choose the range containing the units. Now, you will have a drop-down list of recognized units, as shown below.

excel convert units data validation

In another cell, enter the “to_unit”. Finally, enter the CONVERT function and reference the appropriate cells.

Now, no matter what unit system the spreadsheet user enters their data in, the spreadsheet can deal with it and provide correct results.

Wrap-Up

Hopefully, you’ve gotten an idea of the kinds of things you can do with the CONVERT function in Excel.

Do you use this function a lot? Do you have any other tips to share? Let me know in the comments below.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

MMULT Excel Function for Matrix Multiplication

Solving Systems of Simultaneous Nonlinear Equations in Excel

Array Formulas in Excel for Engineering Calculations

Increase Efficiency with Keyboard Shortcuts

Create a Vector Plot in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in