Custom number formatting in Excel is easy!

Easy-Excel_3_1Excel 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 won’t be able to 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 easy way to do it: 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 have to remember: Put the text in double quotes after the format code, like this:

###0″ stone”

Remember the space after the first quote!

Easy-Excel_3_2



Now it looks a lot better, and you can even increase/decrease decimals without losing your custom text.

Easy-Excel_3_3

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:

Easy-Excel_3_4


Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

Leave a Reply

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


× 7 = twenty eight

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>