How to include the measurement unit in the number format in Excel

Excel has a few built-in number formats such as $100.00 or ¥100,00. But what if you want to add another measurement unit to the number format, such as lbs, stone, kg, km/h, ft, acre or oz?

If you type “169 lbs” in the cell, Excel will interpret it as text, and you won’t be able to do any calculations. We need to find another way.

One way is to divide it into two cells:

But that doesn’t look good. This looks a lot better:

The trick is to use custom number formatting. Select the cells that you want to apply the formatting on and open the Format Cells dialog box (Ctrl+1). Choose Custom, type a 0 followed by the measurement unit in double quotes, for example:

0″ lbs”

Remember the space after the first quote!

That’s it, and you can even increase/decrease decimals without losing your custom text.

This is a very useful feature if you want to show a data table like the one on the picture below. First you set up and calculate the table, then you simply apply custom formatting on each column:

Bonus trick:
If you want to show a thousand separator, you should use this format instead:
#,##0″ ft” (common in English-speaking countries)
or
# ##0″ ft” (common in Europe)