Custom number formatting in Excel is easy!

Excel has a lot of built-in number formats such as $100.00 or ¥100,00. But what if you want something that is not in the standard menu, like 100 km/h or 14 stone?

If you type “14 stone” in the cell, Excel will interpret it as text, and you can’t do any calculations. We need to find another way.

One way is to divide it into two cells. This is not a pretty solution, and I think it should be avoided.

 

 

 

 

 

 
So, let me show you the way to do it if you want to impress your colleagues: Custom number formatting! It’s easy, and it only takes a few seconds.

Select the cells that you want to apply the formatting on and open the Format Cells dialog box (Ctrl+1). Choose Custom, click on ###0 and type your text in double quotes as shown on the picture below.

(###0 is a standard number format where the hash (pound sign) represents a significant number and the zero represents an insignificant number. You don’t need to worry about that for now, though!)

Here’s all you need to remember: Put the text in double quotes after the format code:

###0” stone”

Remember the space after the first quote.

 

Now it looks good, 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 this:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s