Excel CONVERT Function Made Easy

Do you ever use the Excel CONVERT function? Do you avoid it, because you can't remember all the measurement unit codes?

For example, the formula =CONVERT(10,"klt","gal") will convert 10 kilolitres to 2,641.7205 gallons – if you get those codes right.

You might be able to remember lt and gal, but probably not many of the other codes.

Drop Down Lists of Units

To make the CONVERT function easy to use, I created an Excel file, with lists of the unit categories, codes and the prefixes.

First, you'll select a conversion type, from a drop down list of unit categories.


Then, enter a quantity.


Next, you can select a prefix, if applicable, from a drop down list.


And select the unit type, from the next drop down list. This list only shows the items for the Conversion Type that you selected. The instructions for this technique can be found on the Dependent Data Validation From a Sorted List page.


Select the Conversion Units

After you have selected the starting unit type, select the prefix and units for the converted quantity.

In this example, we're converting kilolitres to gallons, and you can see the CONVERT function unit codes at the right of the unit names.


See the Converted Quantity

In the final cell, a formula checks for entries in the From and To unit cells, then converts the quantity. It uses the quantity, prefix codes, and unit codes.

=IF(COUNTA(D10,D14)<2,"", CONVERT(D6,E9&E10,E13&E14))


Excel VBA Code Clears Cells

You can use this workbook without macros, but if you enable macros, the data entry cells will be cleared, when you select a Conversion Type.

To see the event code that clears the cells, right-click on the sheet tab, and click View Code.


Download the Excel CONVERT Function Workbook

To test the CONVERT function, and see how the data validation drop down lists work, you can download the sample file from the Contextures website. Go to the Excel Sample Files page, and in the Functions section, look for: FN0022 CONVERT Function Made Easy.

The file is in Excel 2007/2010 format, and zipped. It contains a macro, so enable macros if you want to automatically clear the data entry cells.

NOTE: The Conversion sheet is protected, with no password.


You may also like...

10 Responses

  1. Hi Debra,

    Nice post!
    I’m sure it is jus a typo, but the “dekao” prefix looks rather silly :-)

  2. Hi Jan Karel,
    Thanks, and I’m not sure why it’s spelled like that, but dekao is the prefix that Microsoft decided to use.

  3. Ken Puls says:

    Huh… and all that time I went to an online converter page on the web.

  4. Lara says:

    I love this, I can put it in to something I use everyday and not use this other tool I have. Thanks.

  5. Joseph says:

    Is it possible to add units that are not already part of the excel library?

  1. March 13, 2012

    […] Also enter the amount shown in the Food List measurement, and the worksheet calculates a multiplier. This feature uses the Excel CONVERT function. […]

  2. September 29, 2012

    […] […]

  3. September 13, 2014

    […] Dalgleish of Contextures has made it even easier and put together a workbook you can download that allows you to select the units from a drop down […]

  4. August 17, 2017

    […] make it easier to create a CONVERT formula in Excel 2007, I build a worksheet with drop down lists for the CONVERT function’s category, units and […]

Leave a Reply

Your email address will not be published. Required fields are marked *