Every engineer knows that for the results of engineering calculations to be correct, the units of the input values must be correct. In the case of a calculation like F=ma, the units must be consistent with each other (e.g. N, kg, m/s^2) and in the case of a unit-specific calculation like P=Tn/5252, the equation requires values in particular units (e.g. hp, lbf-ft, RPM).
A tool like Mathcad is “unit aware” because it allows you to enter units along with a value and take these units into account in subsequent calculations.
But you may be wondering, is there a way to make Excel “unit aware”?
What are “Unit Aware” Calculations?
Unit aware means that if you tell Excel the units on your input values, it will perform the calculations and provide the correct result by performing the appropriate unit conversions.
It turns out that there is a relatively straightforward way to do this in Excel by using a combination of two built-in capabilities:
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
- The “CONVERT” function
- Data Validation
Excel CONVERT Function
In my opinion, the CONVERT function is one of the best kept Excel secrets for engineers.
The syntax for this handy function is
CONVERT(number,from_unit,to_unit)
where
number is the value to be converted
from_unit is a text string containing the unit to be converted FROM (or the unit of number)
to_unit is a text string containing the unit to be converted TO
How the CONVERT Function Works
The convert function simply multiplies number by a conversion factor that it looks up from an internal table. Therefore, one caveat to using the CONVERT function is that both from_unit and to_unit must be recognized by Excel. Otherwise, Excel returns an #N/A error.
CONVERT recognizes many units of weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, and speed. For metric (SI) units, it also recognizes every prefix from yotta to yocto (I didn’t even know those were things :)) and everything in between.
Microsoft seems to add more units to CONVERT with each Excel release and they keep an excellent list of all the supported units on their site.
CONVERT Function Examples
There are two common ways to use CONVERT. The first way is to type from_unit and to_unit directly into the function. Keep in mind that these are text strings, so they must be enclosed in double quotes (“) or else you will receive a #VALUE! error.
=CONVERT(10,"m","in") >> returns 393.7
=CONVERT(2 "gal","l") >> returns 7.57
The second way that CONVERT can be used is by entering the from_unit and to_unit in cells on the worksheet. (With this method, no double quotes are required because Excel automatically recognizes text in cells as a string.)
This is really all you need to do to make Excel calculations “unit aware”
However, we can take it one step further by combining the ability to determine from_unit and to_unit from cells and use Data Validation to control what units are selected.
Data Validation
Data Validation is a tool that allows a spreadsheet author to control the values that are entered in a cell. You’ve probably encountered it before if you have ever seen a drop-down menu in a cell.
With the cell containing the from_unit selected, go to Data > Data Validation (in the Data Tools section)
There are several different validation criteria to choose from, but we will choose to allow only values from a pre-specified list.
The “Source” field contains the allowable values. These can be selected from a range in the workbook or simply entered as a list of comma-separated values as shown below:
If only recognized units are included, the conversion is sure to work without error.
Going back to the spreadsheet, we can select a new from_unit in cell C2, and the result in B3 will reflect the change in units.
Unit Aware Calculations in Excel: Examples
Of course, you can convert many other units as well, like area. In the following example, the spreadsheet has been set up to accept area in many different units, and convert to square meters for subsequent calculations:
It’s also possible to convert complex units by multiplying or dividing simple unit conversions:
