How To Create an Engineering Unit Conversion Calculator in Excel – Free Excel Sheet Download

Unit converters are required to standardize measurements, guarantee accuracy, and boost productivity. They are employed in many different industries, including science, engineering, and finance. Unit converters are practical and straightforward to use for everyday purposes.

In this article, we will learn how to create a unit converter in Excel step by step. We hope this article is going to be very important, especially for Engineers 

How to Use the CONVERT Function in Excel?

Before jumping to the steps we suggest you keep following ideas on the CONVERT function in Excel

Syntax of CONVERT Function 

=CONVERT(value, from_unit, to_unit)

Argument of CONVERT Function

value: You would like to convert this value.

from_unit: The value you want to convert is represented by this unit of measurement. Both a text string and a reference to a cell containing the unit of measurement may be used to insert it.

to_unit: The unit of measurement you want to change the value’s value to is indicated here. Both a text string and a reference to a cell containing the unit of measurement may be used to insert it.

Example: How to Convert Kg to Ton in Excel?

Suppose, you want to convert 10 Kg to Ton. Just apply the following formula:

=CONVERT(10,"kg","ton")   //returns 0.011023

Similarly, you can convert all units as per your necessity. But we want to create an automated unit converter to get rid of this hard-coded formula.

If you want to know more, please spend 1 minute on the hyperlinked page about the CONVERT function in Excel.

Steps to Create an Engineering Unit Conversion calculator in Excel

We will divide the total steps for making the  unit converter into 4 section 

A. Preparing Unit list for the converter 

B. Designing the converter template

C. Creating a dropdown list of units.

D. Applying formulas to convert units automatically

Section A. Preparing prefix & Unit list for the converter 

You already know that to use the CONVERT function, we need the units and prefixes as given in the official documentation of Microsoft Excel.

Suppose you need to convert millimeters to meters. But Microsoft Excel provides units for meter(m) and prefixes for mili (m). So you have to combine prefixes and units to make them usable in the CONVERT function. But if you apply irrelevant prefixes with units that do not exist or are not supported in Excel, the CONVERT function will return an #N/A error.

You can skip this laborious work; we made a unit list combining all necessary units and prefixes.

Section B. Designing the unit converter Template in Excel

Step 01: Create a blank worksheet named Unit Converter

Step 02: label a cell as Length/Distance

Step 03: Select the horizontal cells in 5 columns and navigate to the Home tab.

Step 04:  Click on the Marge cells.

Step 05: From the Home tab, click on the cell styles and choose any style you prefer.

Step 06: Increase the font size from the Home tab’s Font section.

Step 07: Under the row labeled  Length/Distance, rename four cells as value, Select unit, value, and Select unit.

Step 08: In the third column (from left), input the equal sign (=), align center, and adjust the font size as instructed above.

Step 09: In the third column (from the left), select two vertical cells. Marge, as instructed above.

Step 10: Select the range B3:F5. From the Home tab’s Font section, select All borders.

The result is

Step 11: Create a new worksheet, named Unit

Step 12: Enlist the units in two columns as shown in the picture.

Section C. Create a drop-down list of units

Step 01: In the worksheet named “unit converter,” select the cell (Cell C5) below the cell named “select unit.”

Step 02: Go to the Data tab. From the Data Tools section, click on Data Validation.

Step 03: From the Data validation window, select the list in the Allow option 

Step 04: Click on the Source input field

Step 05: Go to the worksheet named Unit

Step 06: Select the first column of the unit list. And click on OK

 Drop-down list is created 

Step 07: Repeat steps 01-06 in Section C for cell F5.

Section D. Applying formulas to Convert Units Automatically

In the output cell E5, input the following formula and press Enter.

=ROUND(CONVERT(B5,INDEX(Units!B3:C22,MATCH(C5,Units!B3:B22,0),2),INDEX(Units!B3:C22,MATCH(F5,Units!B3:B22,0),2)),3)

In this formula, we used the nested INDEX ,MATCH function to replace the from_unit and to_unit in the syntax of the CONVERT function.

Explanation:

• The function MATCH(C5,Units!B3:B22,0) looks for the value of cell C5 in cells B3 through B22 of the “Units” sheet. An exact match is necessary, as indicated by the 0 at the end.
• The value in the second column (column C) of the row where the value in C5 was matched in the “Units” sheet is returned by the expression INDEX(Units!B3:C22,MATCH(C5,Units!B3:B22,0). And injected into the syntax of the CONVERT function as from_unit
• Similarly, the second index and Match finds the matched to_unit and injected it into the syntax of the CONVERT function.

• The value in cell B5 is converted by the formula. The returned result is rounded to three decimal places by the ROUND Formula.

We created the unit converter in Excel for a unit type (length or distance). Similarly, you can create all unit types according to your preferences.