How to Create a Conversion Table in Excel - dummies

How to Create a Conversion Table in Excel

You may work at a company where you need to know how many cubic yards can be covered by a gallon of material, or how many cups are needed to fill an Imperial Gallon.

You can use Excel’s CONVERT function to produce a conversion table containing every possible type of conversion that you need for a set of measures. The figure illustrates a conversion table created using nothing but Excel’s CONVERT function.

image0.jpg

With this table, you can get a quick view of the conversions from one unit of measure to another. You can see that it takes 48 teaspoons to make a cup, 2.4 cups to make an English pint, and so on.

The CONVERT function requires three arguments: a number value, the unit you’re converting from, and the unit you’re converting to. For instance, to convert 100 miles into kilometers, you can enter this formula to get the answer 160.93:

=CONVERT(100,"mi", "km")

You can use the following formula to convert 100 gallons into liters. This gives you the result 378.54:

=CONVERT(100,"gal", "l")

Notice the conversion codes for each unit of measure. These codes are very specific and must be entered in exactly the way Excel expects to see them. Entering a CONVERT formula using “gallon” or “GAL” instead of the expected “gal” returns an error.

Luckily, Excel provides a tooltip as you start entering your CONVERT function, letting you pick the correct unit codes from a list.

You can refer to Excel’s Help files on the CONVERT function to get a list of valid unit-of-measure conversion codes.

When you have the codes you are interested in, you can enter them in a matrix-style table like the one you see in Figure 12-14. In the top-leftmost cell in your matrix, enter a formula that points to the appropriate conversion code for the matrix row and matrix column.

Be sure to include the absolute references necessary to lock the references to the conversion codes. For the codes located in the matrix row, lock the column reference. For the codes located in the matrix column, lock the row reference.

=CONVERT(1,$E4,F$3)

At this point, you can simply copy your formula across the entire matrix.