Excel CONVERT Function With Drop Down Lists

Excel CONVERT Function With Drop Down Lists

With the Excel CONVERT function, you can change quantities from one measurement type to another. For example, a hot day in Celsius looks even worse in Fahrenheit! Get my updated workbook for the CONVERT function, with drop down lists for category, units and prefixes.

CONVERT Function First Version

If you used CONVERT in the old days, things were tough. When CONVERT was first added in Excel 2007, you had to remember all the measurement codes, or go into the Help files, to look them up (and nobody wanted to go there).

convertunits03

Drop Down Lists

To make it easier to create a CONVERT formula in Excel 2007, I built a worksheet with drop down lists for the CONVERT function’s category, units and prefixes.

After you selected a Conversion Type, only the codes from that category were shown in the Units drop downs.

You could also select a prefix for your units, to use as multipliers. For example, combine “k” and “g”, to make a kilogram unit.

convertfunction02

CONVERT Improvements

In Excel 2010, the CONVERT function’s data entry options were improved. Now, when you start to enter the “from_unit” argument, a full list of the measurement codes appears.

Then, when you start to enter the “to_unit” argument, Excel shows a filtered list of codes, from the measurement category that match your “from_units” selection.

The prefix options aren’t shown though – you have to add them on your own, if you want a prefix.

convertunits01

CONVERT Function Bug

If you’re still using Excel 2010, there are codes where the filtering doesn’t work. (This was fixed in Excel 2013).

For example, if you select “K” (Kelvin) or “m” (Meter), the next list isn’t filtered. Instead of a list of temperature measurements, you could pick Gram, or Foot. And, if the measurement types don’t match, the CONVERT formula will return an error.

convertunits02

More Codes in Excel 2013

In Excel 2013, more categories, codes and prefixes were added for the CONVERT function. The new categories are Area, Information, and Speed, and there are too many new codes and prefixes to list here.

However, Excel still doesn’t help with the prefixes such as “k” (kilo), “M” (mega) and “m” (milli). If you need a prefix, you’ll have to type it in the formula yourself.

In the screen shot below, the formula coverts 10 kilograms to 0.01 Megagrams.

convertfunctionprefix01

Get the CONVERT Function Workbook

If you’d like an easy way to build CONVERT formulas, with all the new categories, codes, and prefixes, download the updated version of my CONVERT workbook.

Go to the Excel Sample Files page on my Contextures website, and in the Functions section, look for: FN0022 CONVERT Function Made Easy.

The zipped file is in xlsm format, and it contains a macro, so enable macros if you want to automatically clear the data entry cells, when you select a different Conversion Type.

convertfunction03

Make a Drop Down List

To see how to make a drop down list in a cell, watch the steps in this short video, and the written instructions are on the Excel Drop Down List page on my Contextures site.

_____________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.